Forum: Ruby on Rails counting rows via associations

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Bruce B. (Guest)
on 2006-01-29 05:11
(Received via mailing list)
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
Pat M. (Guest)
on 2006-01-29 05:17
(Received via mailing list)
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
John T. (Guest)
on 2006-01-29 06:09
bruce balmer wrote:

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


Try:

clients.lists.length

jt
Bruce B. (Guest)
on 2006-01-29 06:41
(Received via mailing list)
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
Bruce B. (Guest)
on 2006-01-29 06:44
(Received via mailing list)
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
Pat M. (Guest)
on 2006-01-29 06:47
(Received via mailing list)
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 :(

Pat
Pat M. (Guest)
on 2006-01-29 06:47
(Received via mailing list)
gah, forgot to mention that you should change 'elements' to whatever
the assocation name is.
Bruce B. (Guest)
on 2006-01-29 07:23
(Received via mailing list)
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
Pat M. (Guest)
on 2006-01-29 07:48
(Received via mailing list)
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
Pat M. (Guest)
on 2006-01-29 07:48
(Received via mailing list)
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
Kevin O. (Guest)
on 2006-01-29 09:34
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
Pat M. (Guest)
on 2006-01-29 10:36
(Received via mailing list)
On 1/29/06, Kevin O. <removed_email_address@domain.invalid> 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
> removed_email_address@domain.invalid
> http://lists.rubyonrails.org/mailman/listinfo/rails

Any idea what would be faster?  Looping with counter caching or one
monster SQL statement?
Kevin O. (Guest)
on 2006-01-29 15:55
Pat M. wrote:
> On 1/29/06, Kevin O. <removed_email_address@domain.invalid> 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
>> removed_email_address@domain.invalid
>> 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
Ezra Z. (Guest)
on 2006-01-29 22:29
(Received via mailing list)
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
> removed_email_address@domain.invalid
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

-Ezra Z.
WebMaster
Yakima Herald-Republic Newspaper
removed_email_address@domain.invalid
509-577-7732
Bruce B. (Guest)
on 2006-01-30 01:54
(Received via mailing list)
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)
Ezra Z. (Guest)
on 2006-01-30 03:46
(Received via mailing list)
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. <removed_email_address@domain.invalid> 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
> removed_email_address@domain.invalid
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

-Ezra Z.
WebMaster
Yakima Herald-Republic Newspaper
removed_email_address@domain.invalid
509-577-7732
Bruce B. (Guest)
on 2006-01-30 06:38
(Received via mailing list)
Thanks :-)

bruce
This topic is locked and can not be replied to.