Rails advanced sql query

Question

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?

Dan Reedy · Accepted Answer · 2013-05-01 16:33:21Z

1

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.

answered May 1, 2013 at 16:33

Dan Reedy

1,85710 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

PinnyM · Accepted Answer · 2013-05-01 17:11:16Z

1

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")

answered May 1, 2013 at 17:11

PinnyM

35.6k4 gold badges77 silver badges84 bronze badges

3 Comments

Dan Reedy Over a year ago

If there is a large data set you will often see better performance with NOT EXISTS over a LEFT JOIN. Source: explainextended.com/2009/09/15/… EDIT: Clarifying that you often see better performance, taking into account PinnyM's comment below.

PinnyM Over a year ago

@DanReedy, that will depend on the DBMS and version - only the query optimizer can determine how performant it will be given a specific query and analytics. The article you linked was from 3.5 years ago and is SQL Server specific. I'm not sure if a current version of MySQL (or SQL Server) is any better in this respect, but that would need to be determined.

PinnyM Over a year ago

@DanReedy, I do prefer your approach BTW, however ActiveRecord makes this messy to write out by hand. Too bad there isn't a gem to add this as native behavior, since this is a fairly common scenario. Maybe someday...

Collectives™ on Stack Overflow

Rails advanced sql query

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related