Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

find all that are nil in the association

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
 => [] 
like image 547
Matt Elhotiby Avatar asked Feb 09 '11 16:02

Matt Elhotiby


4 Answers

Post.where("id not in (select post_id from users)")
like image 70
Dylan Markow Avatar answered Nov 07 '22 04:11

Dylan Markow


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))
like image 21
Josh Kovach Avatar answered Nov 07 '22 03:11

Josh Kovach


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) 
like image 2
Alexander Paramonov Avatar answered Nov 07 '22 03:11

Alexander Paramonov


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))
like image 2
Nathan Lilienthal Avatar answered Nov 07 '22 03:11

Nathan Lilienthal