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

rubyonrails-talk
1

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:

2

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:

3

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

4

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

5

Thanks everyone, that was really helpful!

6

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

7

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

Fred

8

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 (GitHub - binarylogic/searchlogic: Searchlogic provides object based searching, common named scopes, and other useful tools.).

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)