how do I get all records whose count of associations is above a certain number

I have a model, let's call it Player.

It has many Trophies.

How do I do a simple query in Rails in my controller that will return let's say all Players that have more than 5 tropies?

Sorry for the dumb question but I can't figure it out. :slight_smile:

I have a model, let's call it Player.

It has many Trophies.

How do I do a simple query in Rails in my controller that will return let's say all Players that have more than 5 tropies?

Sorry for the dumb question but I can't figure it out. :slight_smile:

The best way to do it is to set a counter_cache for the trophies. Example:

class Trophy < ActiveRecord::Base   belongs_to :player, :counter_cache => true

You'll also have to create an integer column called trophies_count in the players table. If you already have data in there, you'll need to update the counters after creating the column:

def self.up     add_column :players, :trophies_count, :integer, :default => 0     Player.reset_column_information

    Player.all.each do |p|       Player.update_counters p.id, :trophies_count => p.trophies.length     end   end

Then you can just do:

Player.all(:conditions => ["trophies_count > ?", 5])

Jarin Udom Robot Mode LLC

If for some reason you don't want to use a counter cache then you can write it as

SELECT players.*, count(*) as trophy_count from players inner join trophies on trophies.player_id = players.id group by players.id having trophy_count > 5

But using a counter_cache and an index on that column would be way faster.

Fred

Thanks everyone, that was really helpful!

And you can actually represent this in AR friendly syntax; no console in front of me but its something like

Player.find(:select => "players.id", :joins => :trophies, :group => 'players.id', :having => 'count(players.id) > 5')

Downside of this is that the returned objects in this case would only have ID attribute; you can add more to the select, but you need to add them to the :group as well. Would be nice if there was a better way to do this; SQL requires them in both the select and group_by, but it'd be nice if rails would auto do that for the table I'm doing the find on by default.

\Peter

Depends on the database - mysql doesn't care what you add to the select.

Fred

Frank Kim wrote:

I have a model, let's call it Player.

It has many Trophies.

How do I do a simple query in Rails in my controller that will return let's say all Players that have more than 5 tropies?

Sorry for the dumb question but I can't figure it out. :slight_smile:

-- Frank Kim http://betweengo.com/

I would recommend checking out the searchlogic gem (http://github.com/binarylogic/searchlogic).

You can then do something like (I haven't checked the exact format of the method but it will be something along these lines):

Player.trophies_count_greater_than(5)

or used your own named scopes. I'd also think about maybe putting this as a method in your model i.e.

def self.has_more_trophies_than(trophy_count)   Player.trophies_count_greater_than(trophy_count) end

Then you can call it from the controller like so:

Player.has_more_trophies_than(5)