Digithoughts

Consider the following scenario.  You have a blog application, which has users, who have many posts.  Posts, in turn, have many comments.  Comments must be approved by the post author, so therefore can be in one of the following states, 'pending', 'approved', or 'denied'.  In the admin interface, you would like to display a paginated list of users, sorted by the number of 'pending' comments are associated with their posts.  How can you ask Rails for this information?

Typically, when wanting to operate on roll-up counts of related objects, such as how many users are in a group, or how many comments belong to a post, Rails would suggest that you use the counter_cache option in the association:

class Comment < ActiveRecord::Base
  belongs_to :post, :counter_cache => true
end


This requires a 'comments_count' field on your posts table, which will happily keep track of the number of comments that are associated with a particular post, allowing you to use this for basic sorting:

>> User.find(:all, :include => :posts, :order => 'posts.comments_count desc')


However, we want to be able to further qualify this to the number of approved, pending, and denied comments associated with a post (and don't want to create lots of extra database fields to track all these various comment counts).  One way to do this is to use the :select, :joins, and :group options in ActiveRecord. These give us more control over the query by allowing us to specify different parts of the SQL clause.  However, unlike find_by_sql, they allow you to maintain any scope chaining you may have been doing.  For example:

# returns all posts
>> my_user.posts.find_by_sql("select * from posts")

# returns only my_user's posts
>> my_user.posts.find(:all, :select => "posts.*")


So, on to the request for users, sorted by the number of pending comments that are associated with their posts:

users = User.find(
  :all,
  :select => "users.*, count(*) as pending_comments_count",
  :joins => "left outer join posts on posts.user_id = users.id
    left outer join comments on comments.post_id = posts.id and
    comments.status = 'pending'",
  :group  => "users.id",
  :order  => "pending_comments_count desc"
)


There you go!  A list of users sorted by the number of pending comments are associated with them (through posts).  Also, when using :select, ActiveRecord will include (only) the fields you specify in the returned objects.  So:

>> users.first.pending_comments_count
==> "11"


works, even though there isn't a pending_comments_count normally on the User model.  Quick note - If you inspect the objects returned from the find call, you won't see pending_comments_count in the list of attributes, since this attribute list is based on the columns in the database table.  In some older versions of Rails, I've also found you need to use the attributes hash to get to the calculated fields:

>> users.first.attributes['pending_comments_count']
==> "11"


Hopefully this helps to show how in some cases using your own SQL is good, even in the Rails world.

Dylan Vaughn Engineering Manager

Read more from the Engineering category. If you would like to leave a comment, click here: Comment or stay up to date with this post via RSS, or you can Trackback from your site.