Rails User.joins.not (...) in active record?

Question

Im looking for a query of all users without comments in a single SQL query?

Models:

class User < ActiveRecord::Base has_many :comments end class Comment < ActiveRecord::Base belongs_to :user end 

So, I want to do the opposite:

 User.joins.(:comments).group('users.id') 

But not like that: (because it generates two requests)

 User.where.not(id: Comment.pluck(:user_id)) 

Maybe something like this?

 User.joins.not.(:comments).group('users.id') 

Thanks for any input!

Answer 1

You can do this with:

 User.includes(:comments).where.not(comments: { id: nil }) 

This will result in raw SQL, which will look something like this:

 SELECT COUNT(DISTINCT 'users'.'id') FROM 'users' LEFT OUTER JOIN 'comments' ON 'comments'.'user_id' = 'users'.'id' WHERE 'comments'.'id' IS NULL 

To accomplish this through a subquery, see the answer below.

Old answer :

You can do something like

 User.where.not(id: Comment.select(:user_id)) 

If you wanted one (albeit nested) request.

Otherwise, check out http://guides.rubyonrails.org/active_record_querying.html#joining-tables to use an external join.

Answer 2

If you use postgresql , you can do something like this

 User.joins("LEFT join comments c on users.id = c.comment_id"). select("users.id"). group("users.id"). having("count(users.id) = 1") 

This will generate this request

 select u.id from users u LEFT join comments c on c.comment_id = u.id group by u.id having count(u.id) = 1 

This query does not generate two SQL (nor nested sql), as indicated above.

Answer 3

Another simple solution is User.where("id NOT IN (?)", Comment.pluck(:user_id))