CODEWITHSUNDEEP
mysqlrubyruby-on-rails-3activerecord
BC00
BC00

Reputation: 1639

Rails advanced sql query

I have a model User thats has_many user_entitlements. I want to get all of the users that have a preview attribute and no user entitlements.

My code is currently:

User.where("preview is not null").keep_if {|user| user.user_entitlements.empty?}

This is iterating through all of the users and seeing if they have any user entitlements.

Is there a way I could do this in SQL to make it more efficient and avoid having to go through each user?

Upvotes: 0

Views: 120

Answers (2)

PinnyM
PinnyM

Reputation: 35533

Even simpler, you can use includes to force a LEFT JOIN, then check for NULL:

User.includes(:user_entitlements).
     where("users.preview IS NOT NULL AND user_entitlements.id IS NULL")

Upvotes: 1

Dan Reedy
Dan Reedy

Reputation: 1857

You can use Arel to build a query leveraging NOT EXISTS. 

user_arel = User.arel_table
ue_arel = UserEntitlement.arel_table
User.where("preview is not null").where(
  UserEntitlement.where(ue_arel[:user_id].eq(user_arel[:id])).exists.not
)

I am making an assumption on your schema that UserEntitlement contains a user_id column.

Upvotes: 1

Related Questions