How to join a table and count records in Rails 3?

Question

I have a Collection class which has many coins. I am trying to select collections which have more than two coins. Currently, I have no problem doing that through straight Ruby, but that's extremely inefficient.

My current code:

collections = Collection.all.select { |c| c.coins.count > 2 }

How do I achieve that through a joins call with Arel?

Thanks!

You should not use Collection as class name... There are several Ruby built in structures which require collections (e.g. rails model collections) and it may happen that you override some things which results in unexpected behaviour.. — BvuRVKyUVlViVIc7, Aug 10 '11 at 00:31

score 23 · Accepted Answer · edited May 23 '17 at 11:48

23

To answer my own question:

Collection.joins(:coins).group("coins.collection_id").having("count(coins.id) > 2")

Hat tip to KJF who asked this similar question and to krakover for answering it.

edited May 23 '17 at 11:48

Community

1
1

answered Aug 13 '11 at 23:23

Yuval Karmi

26,277
39
124
175

score 1 · Answer 2 · answered Aug 10 '11 at 00:31

1

Add counter_cache columns and query them.

http://railscasts.com/episodes/23-counter-cache-column

answered Aug 10 '11 at 00:31

BvuRVKyUVlViVIc7

11,641
9
59
111

1

Thanks. A counter cache is meant to save counting records on the fly. Given that my collections only include 2-5 coins each, this isn't an issue and I'd rather not store a separate column in the database. Any ideas how to write the `joins` query using Rails 3? – Yuval Karmi Aug 10 '11 at 04:11

How to join a table and count records in Rails 3?

2 Answers2

Linked