Rails User.joins.not(...) in Active Record?

Question

Im looking to query 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 the opposite of this:

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

But not like this: (because it generates two queries)

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 accomplish this with:

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

This will result in raw SQL that looks something like:

SELECT DISTINCT `users`.`*` FROM `users` LEFT OUTER JOIN `comments` ON `comments`.`user_id` = `users`.`id` WHERE `comments`.`id` IS NULL

For accomplishing this via a subquery, see the below answer.

Old Answer:

You can do something like

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

If you wanted a single (though nested) query.

Otherwise, check out http://guides.rubyonrails.org/active_record_querying.html#joining-tables for using an outer join.