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

Rails
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:

–
Frank K.

2

On Sat, 2010-02-13 at 22:54 -0800, Frank K. 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:

I can’t think of a single query that would pull that off but perhaps
someone can.

But chained… (and very untested)

@players = Array.new
@counts = Player.find(:all)
@counts.each do |player|
if Trophy.count(‘id’, :conditions => [“player_id = ?”, player]) >= 5
then
@players << player
end
end

Craig

–
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

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 U.
Robot Mode LLC

4

On Feb 14, 6:54 am, Frank K. [email protected] 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:

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

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

On Feb 14, 6:31 am, Frederick C. [email protected]

6

On Feb 15, 6:59 am, Peter [email protected] wrote:

it’d be nice if rails would auto do that for the table I’m doing the
find on by default.

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

Fred

7

Thanks everyone, that was really helpful!

On Sun, Feb 14, 2010 at 4:31 AM, Frederick C.
[email protected] wrote:

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

Fred

–
You received this message because you are subscribed to the Google G. “Ruby on Rails: Talk” group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to [email protected].
For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.

–
Frank K.

8

Frank K. 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 K.
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)