Rails - Feb 2010 - 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. :-)

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

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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

On Feb 13, 10:54 pm, Frank Kim
<railso...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
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 Kimhttp://betweengo.com/
-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On Feb 14, 6:54 am, Frank Kim
<railso...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
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

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Thanks everyone, that was really helpful!

On Sun, Feb 14, 2010 at 4:31 AM, Frederick Cheung
<frederick.cheung-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
>
>
> On Feb 14, 6:54 am, Frank Kim
<railso...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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
>
> --
> You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
> To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
> For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
>
>


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

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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 Cheung
<frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> On Feb 14, 6:54 am, Frank Kim
<railso...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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
-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On Feb 15, 6:59 am, Peter
<ppgeng...-vA8bmGSXo1KakBO8gow8eQ@public.gmane.org>
wrote:
> 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.
Depends on the database - mysql doesn''t care what you add to the
select.

Fred

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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. :-)
> 
> --
> 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)

-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.