Rails 3 Order By Count on has_many :through

Question

I have an application where I can list Items and add tags to each Item. The models Items and Tags are associated like this:

class Item < ActiveRecord::Base
  has_many :taggings
  has_many :tags, :through => :taggings
end

class Tagging < ActiveRecord::Base
  belongs_to :item
  belongs_to :tag
end

class Tag < ActiveRecord::Base
  has_many :taggings
  has_many :items, :through => :taggings
end

So, this many-to-many relationship allows me to set n tags for each Item, and the same tag can be used several times.

I'd like to list all tags ordered by the number of items associated with this tag. More used tags, shows first. Less used, last.

How can I do that?

Regards.

Amol Pujari · Accepted Answer · 2012-06-09T10:37:22.043

9

Tag.joins(:taggings).select('tags.*, count(tag_id) as "tag_count"').group(:tag_id).order(' tag_count desc')

try it desc, asc and see the difference.

We need select, to have tag_count column, and we need tag_count column to apply order to it, rest all straight forward join and grouping.

Btw, why dont you try this out https://github.com/mbleigh/acts-as-taggable-on

edited Jun 09 '12 at 10:37

answered Jun 09 '12 at 04:14

Amol Pujari

2,280
20
42

+1 Amol. Do you have any idea on how to accomplish this same query result but with another layer of has_many through:? My question [ http://stackoverflow.com/questions/19537378/ordering-nested-has-many-through-by-count-of-associations ] would be analogous to goo's Items being members of Groups, then trying to list the Tags of an individual Group of Items in order of their frequency of use within the Group, rather than among all Items. Thanks! – djoll Oct 23 '13 at 10:35

score 3 · Answer 2 · answered Jun 09 '12 at 03:58

You'd hope there's a better way, but this worked for me (without the whitespace). Assume name is the name attribute of the Tag model.

foo = Tagging.select("name, count(item_id) as item_count")
      .joins("inner join tags on taggings.tag_id = tags.id")
      .group("name")
      .order("item_count DESC")

foo.each { |r| puts "number of items tagged #{r.name}: #{r.item_count}"}

-->"number of items tagged Bieber: 100"
-->"number of items tagged GofT: 99"

Rails 3 Order By Count on has_many :through

2 Answers2

Linked