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!

@MrYoshiji This is bang on and more correct than the accepted answer. Thanks. — Joshua Pinter, Sep 23 '20 at 15:00
NOTE: In Rails 6.1, you can just use `User.where.missing( :comments )`. See https://edgeapi.rubyonrails.org/classes/ActiveRecord/QueryMethods/WhereChain.html#method-i-missing and this SO answer https://stackoverflow.com/a/5570221/293280. — Joshua Pinter, Sep 23 '20 at 15:04
While the solution proposed by @MrYoshiji might work (if includes is handled internally using eager_load), it's not guaranteed to work. In case AR executes a preload, there will be no join happening and the query will fail. If you need a join to occur, use joins (or on a modern rails left_outer_joins). — NobodysNightmare, Jan 26 '21 at 14:31

Darren Cheng · Accepted Answer · 2019-02-11T00:24:55.027

26

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.

edited Feb 11 '19 at 00:24

answered Apr 16 '14 at 02:25

Darren Cheng

1

Will it use subquery or 2 different queries? – thethanghn Apr 16 '14 at 03:57
2

@thethanghn in this case it will result in a sub query. Any time you pass an `ActiveRecord::Relation` (which is what the active record query methods like `select` and `where` return) to a query method, a sub query will result. – Paul Richter Apr 16 '14 at 14:38
Why are you using the `not` in `User.includes(:comments).where.not(comments: { id: nil })`? This seems wrong to me. You want the condition to be where `id IS NULL`, not the inverse of that. – Joshua Pinter Sep 23 '20 at 14:50

score 3 · Answer 2 · answered Apr 16 '14 at 14:33

3

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.

answered Apr 16 '14 at 14:33

Paritosh Piplewar

What does `c` and `u ` in that query stand for ? – thiebo Feb 11 '17 at 14:32
1

@thiebo "c" is an alias for the comments table & "u" is an alias for the users table. You can find more about SQL aliases [here](https://en.wikipedia.org/wiki/Alias_(SQL)) – Yomna Fahmy Feb 11 '17 at 17:11
I used this successfully, except I changed the having clause to test for the absence of the child record. Something like `.having("count(comment.id) = 0")` – David Hempy Aug 27 '18 at 17:57

Sachin srinivasan · Answer 3 · 2017-08-17T09:21:20.220

2

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

edited Aug 17 '17 at 09:21

answered Jun 08 '17 at 04:35

Sachin srinivasan

2

Good for quick ad hoc queries in rails console, especially when there is no model relationship between tables. BUT...this is not a good route for production code. – David Hempy Feb 23 '19 at 22:41
Just to add to David Hempy, the reason why it is not good for production code is because it requires **two** queries to run: one to get the `user_id`s of all the `Comment` records and then two to get all the `User` records not in those `user_id`s. It's possible and better to do this in a single query. – Joshua Pinter Sep 23 '20 at 14:48

score 1 · Answer 4 · answered Jul 31 '23 at 07:07

1

Starting in Rails 6.1, you can use

User.where.missing(:comments)

answered Jul 31 '23 at 07:07

Bruno Degomme

4 Answers4