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.
2dd904ec5981c31e7bb7a5743a53caf8?d=identicon&s=25 Bruce Balmer (brucebalmer)
on 2006-01-29 04: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
42172acdf3c6046f84d644cb0b94642c?d=identicon&s=25 Pat Maddox (pergesu)
on 2006-01-29 04: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
32b703cdb0ea03c3939b97a93205414f?d=identicon&s=25 John T. (johnt519)
on 2006-01-29 05:09
bruce balmer wrote:

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


Try:

clients.lists.length

jt
2dd904ec5981c31e7bb7a5743a53caf8?d=identicon&s=25 Bruce Balmer (brucebalmer)
on 2006-01-29 05: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
2dd904ec5981c31e7bb7a5743a53caf8?d=identicon&s=25 Bruce Balmer (brucebalmer)
on 2006-01-29 05: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
42172acdf3c6046f84d644cb0b94642c?d=identicon&s=25 Pat Maddox (pergesu)
on 2006-01-29 05: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
42172acdf3c6046f84d644cb0b94642c?d=identicon&s=25 Pat Maddox (pergesu)
on 2006-01-29 05:47
(Received via mailing list)
gah, forgot to mention that you should change 'elements' to whatever
the assocation name is.
2dd904ec5981c31e7bb7a5743a53caf8?d=identicon&s=25 Bruce Balmer (brucebalmer)
on 2006-01-29 06: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
42172acdf3c6046f84d644cb0b94642c?d=identicon&s=25 Pat Maddox (pergesu)
on 2006-01-29 06: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
42172acdf3c6046f84d644cb0b94642c?d=identicon&s=25 Pat Maddox (pergesu)
on 2006-01-29 06: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
C8a634a01a2c4508360874bff7fb1a7f?d=identicon&s=25 Kevin Olbrich (olbrich)
on 2006-01-29 08:34
Pat Maddox 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
42172acdf3c6046f84d644cb0b94642c?d=identicon&s=25 Pat Maddox (pergesu)
on 2006-01-29 09:36
(Received via mailing list)
On 1/29/06, Kevin Olbrich <kevin.olbrich@duke.edu> 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
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails

Any idea what would be faster?  Looping with counter caching or one
monster SQL statement?
C8a634a01a2c4508360874bff7fb1a7f?d=identicon&s=25 Kevin Olbrich (olbrich)
on 2006-01-29 14:55
Pat Maddox wrote:
> On 1/29/06, Kevin Olbrich <kevin.olbrich@duke.edu> 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
>> Rails@lists.rubyonrails.org
>> 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
8e44c65ac5b896da534ef2440121c953?d=identicon&s=25 Ezra Zygmuntowicz (Guest)
on 2006-01-29 21:29
(Received via mailing list)
On Jan 28, 2006, at 8:44 PM, Pat Maddox 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
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

-Ezra Zygmuntowicz
WebMaster
Yakima Herald-Republic Newspaper
ezra@yakima-herald.com
509-577-7732
2dd904ec5981c31e7bb7a5743a53caf8?d=identicon&s=25 Bruce Balmer (brucebalmer)
on 2006-01-30 00: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)
8e44c65ac5b896da534ef2440121c953?d=identicon&s=25 Ezra Zygmuntowicz (Guest)
on 2006-01-30 02: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 Balmer wrote:

>
>>> in the list in named, but if you want to try associations, do
>>>
>>> On 1/28/06, Bruce Balmer <brucebalmer@mac.com> 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
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

-Ezra Zygmuntowicz
WebMaster
Yakima Herald-Republic Newspaper
ezra@yakima-herald.com
509-577-7732
2dd904ec5981c31e7bb7a5743a53caf8?d=identicon&s=25 Bruce Balmer (brucebalmer)
on 2006-01-30 05:38
(Received via mailing list)
Thanks :-)

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