rails sorting blog posts by title

Question

I realize that this could be a rare occurrence (that two or more users would have the same blog post title) but this is something my client wants so, I have to figure it out.

I have a query @blog_posts (which is a query on Posts that changes based on location, etc). I need a way to to list out all the posts titles and how many times that title occurs within the query @blog_posts

Like this:

How to clean a car (2)
I love baseball (1)

Is there a standard practice for grouping and sorting?

In summary, I need to count the occurrences in the query @blog_posts = Post.where(...) (for example) -- not all posts in existence.

Answer 1

Yes, you can do it with a named scope inside your model (rails 3+ syntax):

scope :title_count, select('id, title, count(*) AS tcount').
    where('created_at >= ?', 10.days.ago).
    group('id, title').
    order('tcount desc')

In your controller:

@posts = Post.title_count

Answer 2

Something like this?

@blog_post_counts = Post.find(
  :all, 
  :select => "COUNT(*) AS count, title", 
  :group  => "title", 
  :order  => "count DESC"
)

Of course you need to modify the query so that your conditions etc. are included.

Answer 3

Sounds like you basically want to count occurrences: this answer could be adapted to your purposes.

If you're on 1.8.7 or newer, consider group_by:

ruby-1.9.2-p180 :002 > posts = ["How to clean a car", "How to clean a car", "I love baseball"]
 => ["How to clean a car", "How to clean a car", "I love baseball"]
ruby-1.9.2-p180 :007 > posts.group_by {|e| e}.map {|k, v| {k => v.length}}
 => [{"How to clean a car"=>2}, {"I love baseball"=>1}]