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

**URL:** <https://www.ruby-forum.com/t/how-do-i-get-all-records-whose-count-of-associations-is-above-a-certain-number/183441>\
**Category:** Rails\
**Created:** [February 14, 2010, 7:56am UTC](https://www.ruby-forum.com/t/how-do-i-get-all-records-whose-count-of-associations-is-above-a-certain-number/183441 "2010-02-14T07:56:18Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![frank\_programmer](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/958977/32.png) [@frank\_programmer](https://www.ruby-forum.com/u/frank_programmer)\
**Post date:** [February 14, 2010, 7:56am UTC](https://www.ruby-forum.com/t/how-do-i-get-all-records-whose-count-of-associations-is-above-a-certain-number/183441/1 "2010-02-14T07:56:18Z")

</div>

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.

> **[betweengo.com](http://ww38.betweengo.com/)**
>
> This domain may be for sale!

---

<div class="post-metadata">

**Author:** ![frank\_programmer](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/958977/32.png) [@frank\_programmer](https://www.ruby-forum.com/u/frank_programmer)\
**Post date:** [February 14, 2010, 8:12am UTC](https://www.ruby-forum.com/t/how-do-i-get-all-records-whose-count-of-associations-is-above-a-certain-number/183441/2 "2010-02-14T08:12:07Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![frank\_programmer](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/958977/32.png) [@frank\_programmer](https://www.ruby-forum.com/u/frank_programmer)\
**Post date:** [February 14, 2010, 8:39am UTC](https://www.ruby-forum.com/t/how-do-i-get-all-records-whose-count-of-associations-is-above-a-certain-number/183441/3 "2010-02-14T08:39:36Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![frank\_programmer](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/958977/32.png) [@frank\_programmer](https://www.ruby-forum.com/u/frank_programmer)\
**Post date:** [February 14, 2010, 1:33pm UTC](https://www.ruby-forum.com/t/how-do-i-get-all-records-whose-count-of-associations-is-above-a-certain-number/183441/4 "2010-02-14T13:33:23Z")

</div>

On Feb 14, 6:54 am, Frank K. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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

---

<div class="post-metadata">

**Author:** ![frank\_programmer](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/958977/32.png) [@frank\_programmer](https://www.ruby-forum.com/u/frank_programmer)\
**Post date:** [February 15, 2010, 8:00am UTC](https://www.ruby-forum.com/t/how-do-i-get-all-records-whose-count-of-associations-is-above-a-certain-number/183441/5 "2010-02-15T08:00:42Z")

</div>

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. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![frank\_programmer](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/958977/32.png) [@frank\_programmer](https://www.ruby-forum.com/u/frank_programmer)\
**Post date:** [February 15, 2010, 9:54am UTC](https://www.ruby-forum.com/t/how-do-i-get-all-records-whose-count-of-associations-is-above-a-certain-number/183441/6 "2010-02-15T09:54:49Z")

</div>

On Feb 15, 6:59 am, Peter [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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

---

<div class="post-metadata">

**Author:** ![frank\_programmer](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/958977/32.png) [@frank\_programmer](https://www.ruby-forum.com/u/frank_programmer)\
**Post date:** [February 14, 2010, 3:55pm UTC](https://www.ruby-forum.com/t/how-do-i-get-all-records-whose-count-of-associations-is-above-a-certain-number/183441/7 "2010-02-14T15:55:03Z")

</div>

Thanks everyone, that was really helpful!

On Sun, Feb 14, 2010 at 4:31 AM, Frederick C.  
[removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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 removed\_email\_address@domain.invalid.  
> To unsubscribe from this group, send email to removed\_email\_address@domain.invalid.  
> For more options, visit this group at [http://groups.google.com/group/rubyonrails-talk?hl=en](http://groups.google.com/group/rubyonrails-talk?hl=en).

–  
Frank K.

> **[betweengo.com](http://ww38.betweengo.com/)**
>
> This domain may be for sale!

---

<div class="post-metadata">

**Author:** ![frank\_programmer](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/958977/32.png) [@frank\_programmer](https://www.ruby-forum.com/u/frank_programmer)\
**Post date:** [February 15, 2010, 12:37pm UTC](https://www.ruby-forum.com/t/how-do-i-get-all-records-whose-count-of-associations-is-above-a-certain-number/183441/8 "2010-02-15T12:37:37Z")

</div>

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/](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.](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)