Want to sort by association records count in Datamapper

Question

Lets say I have the following DataMapper resources:

class Post
  include DataMapper::Resource 

  has n, :comments
  ...

end  

class Comment
  include DataMapper::Resource 

  belongs_to :post
  ...

end

To get the ordered list of posts, I know you can do:

@posts = Posts.all(:order => :date.desc)

But lets say I want to display all the Posts ordered descending by how many comments they have. How would I do that?

Answer 1

I believe that the SQL you want to generate is:

SELECT posts.*, COUNT(comments.id) AS comments_count FROM posts
JOIN comments ON posts.id = comments.post_id
GROUP BY posts.id ORDER BY comments_count DESC

As far as I know, this isn't something you can do programmatically with the Query class. Just drop into SQL for this.

Answer 2

A good way to do this for performance reasons is to cache the comment_count value for the post, which would then give you an attribute to use for the :order, maybe like :order => :comment_count_cache.desc . This is easily setup by adding an after creation hook for the Comment model.