Rails ActiveRecord: Select Posts WITHOUT comments

Question

I have a a Comments table and a Posts table, where a Post has many comments and a comment belongs to a post (i.e. it has a post_id in the table).

How can I efficiently select the last ten Posts that DO NOT have comments. I can't seem to accomplish this without first selecting all the posts and checking each for a 0 comment count.

Thanks in advance for any help.

-Nathan

Answer 1

You would have to add a condition somehow like this:

:conditions => '(SELECT count(*) FROM comments WHERE post_id = #{id}) = 0'

I'm not an expert in SQL so maybe there's something like an EMPTY keyword or so.

Answer 2

You can do

Post.find(:all, :include => :comments, :conditions => "comments.id is null", :limit => 10, :order => "created_at desc")

Answer 3

Add a comments_count column to your posts table and let the migration update the counters. From now on Rails will automagically update your counter if you supply :counter_cache => true on the association. It is very easy to select the last 10 posts now:

Post.find_all_by_comments_count 0, :order => 'created_at DESC', :limit => 10

Documentation link: http://rails.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html#M001318

Answer 4

What you need is a left outer join:

Post.all :joins => "left outer join comments on posts.id = comments.post_id", :conditions => "comments.id is null", :limit => 10, :order => 'created at desc'

This link should help you understand the different types of SQL joins: http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html