Rails - Find all entries that don't have any children with a specific value

Question

We're trying to create an ActiveRecord query that could be simplified to a blog example for the sake of understanding. How would we find all blog posts that don't have any comments left by a specific user?

Our current query returns posts as long as some of the comments aren't by the specific user, but we need to exclude the blog post if any of the comments are written by that user. Any ideas?

Answer 1

The most suitable sql syntax would be a "not exists" clause. Outer joins are also popular, but this tends to be for historical reasons, as RDBMS query optimisers did not used to be very good at optimising them for queries involving many records.

Nowadays a decent optimiser can impliment a query with a not exists using something appropriate like a hash antijoin for bulk data.

The query would be...

select *
from   posts
where  not exists (
         select null
         from   comments
         where  comments.post_id = posts.id and
                comments.user_id = 38)

In rails-speak ...

Post.where("not exists (select null from comments where comments.post_id = posts.id and comments.user_id = #{user.id}")

UPDATE:

A better syntax in rails:

Post.where.not(Comment.where("comments.post_id = posts.id").where(:user_id => user.id)).exists)

Answer 2

Post.joins("LEFT JOIN comments ON posts.id = comments.post_id AND user_id = #{user_id}").where("comments.id IS NULL")