13

So I have a Post and a User.
Post has_many users and a user belongs_to a post.
I need a find that will find all the Posts that dont have any users like the following:

Post.first.users
 => []
Nakilon
  • 34,866
  • 14
  • 107
  • 142
Matt Elhotiby
  • 43,028
  • 85
  • 218
  • 321

7 Answers7

30
Post.where("id not in (select post_id from users)")
Deepak Mahakale
  • 22,834
  • 10
  • 68
  • 88
Dylan Markow
  • 123,080
  • 26
  • 284
  • 201
  • If the subquery contains a nil, nothing will be returned and you might want to use `NOT EXISTS` instead, like I needed to in my particular case. [Further reading](http://stackoverflow.com/questions/173041/not-in-vs-not-exists). – manafire Jul 05 '13 at 15:20
  • Simple and elegant... – Fatima May 10 '22 at 10:45
22

Learned this one just today:

Post.eager_load(:users).merge(User.where(id: nil))

Works with Rails 4+ at least.

Update:

In Rails 5+, you can use left_joins instead:

Post.left_joins(:users).merge(User.where(id: nil))
Josh Kovach
  • 7,679
  • 3
  • 45
  • 62
  • Cool solution. It actually works if the relation is not in the default scope (in our case "soft-deleted"). – ilvez Jun 15 '22 at 15:17
2

I know this is tagged as Rails 3, but if you are using Rails 4, I've been doing it like this.

Post.where.not(user_id: User.pluck(:id))
Nathan Lilienthal
  • 864
  • 1
  • 10
  • 16
2

If you need something that is fast, employ a SQL statement like:

SELECT * 
FROM posts p 
LEFT OUTER JOIN users u ON p.id = u.post_id 
WHERE u.id IS null
Deepak Mahakale
  • 22,834
  • 10
  • 68
  • 88
reto
  • 16,189
  • 7
  • 53
  • 67
2

something like that:

p = Post.arel_table
u = User.arel_table

posts = Post.find_by_sql(p.join(u).on(p[:user_id].eq(u[:p_id])).where(u[:id].eq(nil)).to_sql)
Alexander Paramonov
  • 1,441
  • 14
  • 24
1

Post.first.users.empty? should be sufficient if users returns an array.

If you want to check for each post you could do

Post.each do |p|
  if p.users.empty?
    do whatever
  end
end
Deepak Mahakale
  • 22,834
  • 10
  • 68
  • 88
acconrad
  • 3,201
  • 1
  • 22
  • 31
1

i guess a sql with in can cause performance problems if database table has many rows. careful with that

Marcus Sá
  • 608
  • 3
  • 8
  • 2
    Right. Start with the most readable code, secure it with tests, and then you may start to optimize the performance. Without tests, you risk that you "optimize away" some records by accident. And always measure the real performance: optimized version is not always the fastest. – Arsen7 Feb 09 '11 at 17:07