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

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

If you are using 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 query

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 is not generating two SQL (neither nested sql), the above answer does.

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.

Related