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?

Answers


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.


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

Need Your Help

How to pass a model(data) from one view to another in Backbone and edit/delete it?

javascript backbone.js backbone-views backbone-collections backbone-model

I have a web application using BackboneJS. In this application, I have a LayoutView.js file in which there is a Backbone View (called LayoutView). LayoutView has other functions (methods) that ...

A Javascript function

javascript iife

Please explain the following way of writing a function in javascript functions :

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.