sql - Ordering records by the number of associated records -

users can submit resources , post comments.

i want show users 'active' selecting users have submitted resources , comments , order results user has submitted resources , comments combined least.

**resource** has_many :users, :through => :kits has_many :kits belongs_to :submitter, class_name: "user" **user** has_many :resources, :through => :kits has_many :kits has_many :submitted_resources, class_name: "resource", foreign_key: "submitter_id" **kits** belongs_to :resource belongs_to :user **comments** belongs_to :user 

i new kind of sql in rails. how can record set?

first, need add comments association user model:

has_many :comments 

with this, simplest solution this:

user.all.sort |a,b| (a.submitted_resources.count + a.comments.count) <=> (b.submitted_resources.count + b.comments.count) end 

this slow, if want better want add counter caches. in migration:

def add_column :users, :submitted_resources_count, :integer add_column :users, :comments_count, :integer user.reset_column_information user.find_each |u| u.update_attributes! \ :submitted_resources_count => u.submitted_resources.count, :comments_count => u.comments.count end end def down add_column :users, :submitted_resources_count add_column :users, :comments_count end 

once run migration, can change original query to:

user.select('*, (submitted_resources_count + comments_count) activity_level').order('activity_level desc') 

this efficiently return users in proper order, , bonus each user have read-only attribute called activity_level give exact submitted resources + comments count.