Rails Tagging: Find Tags by number of occurances

I have a fairly simple tagging model in my app.

Photo has_many :taggings
Photo has_many :tags, :through => :taggings

Tag has_many :taggings
Tag has_many :photos, :through => :taggings

Tagging belongs_to :photo
Tagging belongs_to :tag

What I'd like to do now is retrieve all the tags, order them by the number of photos tagged with that particular tag, and show that number along side the tag.

How 开发者_运维问答do you write a query like that? And, how do you prevent an n+1 query when you show tag.photos.count for each tag?

Thanks!

SQL is your friend - assuming the Tag model has a name attribute:

tags = Tag.joins(:photos).
           group("tags.id").
           select("tags.id, tags.name, count(*) as num_photos").
           order("num_photos")

tags.all.each do |tag|
  puts [tag.id, tag.name, tag.num_photos].join(",")
end

You could throw that in a scope (e.g., with_counts) and do things like:

 Tag.where(["tags.name = ?",sassy]).with_counts.first.num_photos

This is a hack, but assuming that all the Taggings are valid links between a tag and a photo you could use

Tagging.group("tag_id").order("count(*) desc").count

to get an ordered hash of all the Tag ids and the number of taggings/photos associated with that tag.