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.
http://betweengo.com/

#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.
http://betweengo.com/

#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
(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)