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. 
–
Frank K.
http://betweengo.com/
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. 
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.
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
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. 
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
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]
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
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. 
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/
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. 
–
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)