Counting rows via associations

Rails
1

Hi:

I have a table called clients and each client has many lists (a
has_many and belongs_to has been created in the class.

What I need to do is get a count of the rows in each list for each
client. I was hoping I could do something like

client.lists.count but it does not seem to work. Any suggestions?

bruce

2

client.lists.size ?

Does each list object have more than one element? That’d make sense
given the name, but I’m not entirely sure. If that’s the case, you
could do it with a join.

Pat

3

bruce balmer wrote:

client.lists.count but it does not seem to work. Any suggestions?

bruce

Try:

clients.lists.length

jt

4

Actually, it was not the number of lists per client that I was
seeking but the number of orgs per list. Hence why I was hoping to do
something like

client.lists.orgs.count() but it seems that the associations don’t
go that deep. I could well be wrong, this is my first day tackling this.

Bruce

5

Hey Bruce,

I thought that was what you might want. Not sure what the association
in the list in named, but if you want to try associations, do
something like:

count = 0
client.lists.each { |list| count += list.elements.size }

The problem with that is that I’m sure it will do a bunch of queries
that you don’t need…I’m pretty sure it’s possible to do what you want
in a single SQL statement with a join, but unfortunately I don’t know
enough SQL to do it :frowning:

Pat

6

Pat:

Thanks. That’s great.

However, (if only life were that simple) I also want to get a subset
of all the rows, so I don’t think “.size” will work. I know how to
do it with a join, I was just trying to do it by associations because
it seemed neater.

Anyhow, I appreciate your help so far and will apply it immediately.

bruce

7

gah, forgot to mention that you should change ‘elements’ to whatever
the assocation name is.

8

Pat:

Worked beautifully. Thanks again. I think I get the concept.
Associations only go two deep. From client to list for example and
from list to orgs. But you can step through them with the code you
wrote. Got it! Thanks.

bruce

9

Hey Bruce,

Try doing something like this

num_orgs = Organization.find_by_sql([“SELECT lists.id,
lists.cliend_id, organizations.list_id FROM clients, lists,
organizations WHERE lists.client_id = ? AND organizations.list_id =
lists.id”, client.id])

I don’t know if that will work or not, it’s just an idea. However if
you’re going to be going through a lot of records you will only want
to do one query so it’s a lot faster. I’m sure someone here will be
able to come up with the exact SQL, and if not then just hop on #mysql
for some help. The point is you want to do this in one statement,
because it is possible to do.

Pat

10

Pat M. wrote:

I need to stop sending so quickly.

num_orgs = Organization.find_by_sql([“SELECT lists.id,
lists.cliend_id, organizations.list_id FROM clients, lists,
organizations WHERE lists.client_id = ? AND organizations.list_id =
lists.id”, client.id]).size

Don’t forget about counter caching. Some associations will let you add
a column that gets incremented and decremented when association items
are added or deleted. It’s probably much faster, but not as flexible as
the queries.

_Kevin

11

On 1/29/06, Kevin O. [email protected] wrote:

are added or deleted. It’s probably much faster, but not as flexible as
the queries.

_Kevin

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

Rails mailing list
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails

Any idea what would be faster? Looping with counter caching or one
monster SQL statement?

12

I need to stop sending so quickly.

num_orgs = Organization.find_by_sql([“SELECT lists.id,
lists.cliend_id, organizations.list_id FROM clients, lists,
organizations WHERE lists.client_id = ? AND organizations.list_id =
lists.id”, client.id]).size

13

Pat M. wrote:

On 1/29/06, Kevin O. [email protected] wrote:

are added or deleted. It’s probably much faster, but not as flexible as
the queries.

_Kevin

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

Rails mailing list
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails

Any idea what would be faster? Looping with counter caching or one
monster SQL statement?

I’m sure it would depend on the monster SQL query, but I’m guessing that
retrieving and summing a few integers would be faster than finding and
counting a bunch of records.

In fact, I’m fairly certain that you could write a monster SQL query to
sum the relevant counter cache columns for you and get the best
performance from that.

_Kevin

14

Ezra:

Thanks.

bruce

PS. If I should not be clogging things up with “thank you’s” I will
stop. That said, as long as threads are on, it does not seem too
bad. Your advice will be acted on (although, I do think it helps
maintain a positive tone)

15

On Jan 28, 2006, at 8:44 PM, Pat M. wrote:

Hey Bruce,

I thought that was what you might want. Not sure what the association
in the list in named, but if you want to try associations, do
something like:

count = 0
client.lists.each { |list| count += list.elements.size }

clients.lists.inject(0){|count, list| count += list.elements.size }

-Ezra

Pat:
bruce

Pat

client.lists.count but it does not seem to work. Any suggestions?
Rails mailing list
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails

-Ezra Z.
WebMaster
Yakima Herald-Republic Newspaper
[email protected]
509-577-7732

16

Thanks :slight_smile:

bruce

17

Bruce-

I think its fine to say thanks. No worries here ;-)

-Ezra

On Jan 29, 2006, at 3:54 PM, Bruce B. wrote:

in the list in named, but if you want to try associations, do

On 1/28/06, Bruce B. [email protected] wrote:

Does each list object have more than one element? That’d make

I have a table called clients and each client has many lists (a

Rails mailing list
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails

-Ezra Z.
WebMaster
Yakima Herald-Republic Newspaper
[email protected]
509-577-7732