so here is the deal. i''ve got three tables: lists, items, comments,
and this is how they are laid out:
lists table
id
title
items table
id
list_id
item
comments table
id
list_id
comment
to give you some background. in order to get a list of all the lists i am
making this call:
SELECT lists.id, title, count(items.list_id) AS total_list_items
FROM lists
INNER JOIN items ON list_id = lists.id
GROUP BY list_id
ORDER BY created_on DESC
LIMIT 10
i need to make that kind of a call because when i print out the list of lists
they look like this:
10 favorite movies
15 favorite cheese
8 places to eat sushi
etc etc. but i''m getting the number at the beginning from the
"total_list_items" in the call from above. now comes the complicated
part. i am trying to get a list of the most commented lists, and in that list i
need all the information from above, but i also need to count
"comments.list_id" and set that as "total_comments" or
something. this is what i''ve come up with:
SELECT lists.id, title, count(comments.list_id) AS total_comments,
count(items.list_id) AS total_items
FROM lists
INNER JOIN comments ON comments.list_id = lists.id
INNER JOIN items ON items.list_id = lists.id
GROUP BY comments.list_id
ORDER BY total_comments DESC
LIMIT 10
but that doesn''t work. 2 of the colums that it returns are
"total_comments" and "total_items" but both columns have
crazy values that are nothing like what they are by themselves. it''s
almost like the values of each were multiplied together or something. so, what
are your suggestions?
Try:
SELECT lists.id, title, count(distinct comments.id) AS total_comments,
count(distinct items.id) AS total_items
in your SQL. Currently, if you have 2 lists, each with 3 items, and 4
comments per list, your non-grouped by table will have 2 * 3 * 4 = 24 items,
with comments.list_id and items.list_id repeated as many times as there are
rows related to it. Count the values of your comments.id and your
lists.idinstead.
Also, you may want to change one or both of your ''INNER'' joins
to ''LEFT''
joins if it''s valid that there are no items in a list, or no comments
for a
list. You''re SQL as it stands will skip lists with items but no
comments.
Daniel Butler
http://www.yup.com
On 5/13/06, elisfanclub <elisfanclub@gmail.com>
wrote:>
>
> so here is the deal. i''ve got three tables: lists, items,
comments, and
> this is how they are laid out:
>
> lists table
> id
> title
>
> items table
> id
> list_id
> item
>
> comments table
> id
> list_id
> comment
>
> to give you some background. in order to get a list of all the lists i am
> making this call:
>
> SELECT lists.id, title, count(items.list_id) AS total_list_items
> FROM lists
> INNER JOIN items ON list_id = lists.id
> GROUP BY list_id
> ORDER BY created_on DESC
> LIMIT 10
>
> i need to make that kind of a call because when i print out the list of
> lists they look like this:
>
> 10 favorite movies
> 15 favorite cheese
> 8 places to eat sushi
>
> etc etc. but i''m getting the number at the beginning from the
> "total_list_items" in the call from above. now comes the
complicated
> part. i am trying to get a list of the most commented lists, and in that
> list i need all the information from above, but i also need to count "
> comments.list_id" and set that as "total_comments" or
something. this is
> what i''ve come up with:
>
> SELECT lists.id, title, count(comments.list_id) AS total_comments,
> count(items.list_id) AS total_items
> FROM lists
> INNER JOIN comments ON comments.list_id = lists.id
> INNER JOIN items ON items.list_id = lists.id
> GROUP BY comments.list_id
> ORDER BY total_comments DESC
> LIMIT 10
>
> but that doesn''t work. 2 of the colums that it returns are
> "total_comments" and "total_items" but both columns
have crazy values that
> are nothing like what they are by themselves. it''s almost like
the values
> of each were multiplied together or something. so, what are your
> suggestions?
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060513/0590e507/attachment.html
thanks for the help. i''m definitly going to use the LEFT JOIN, that''s a good idea. but i tried counting the "comments.id" and the "items.id" (instead of list_id) and i''m getting the same problem. it still seems to be multiplying the values somewhere in there. thanks.
Can you give the number of comments and items vs the number given by COUNT? It seems possible that mysql isn''t counting *unique* comments and items? -----Original Message----- From: rails-bounces@lists.rubyonrails.org [mailto:rails-bounces@lists.rubyonrails.org] On Behalf Of elisfanclub Sent: Saturday, May 13, 2006 9:30 AM To: rails@lists.rubyonrails.org Subject: [Rails] same thing happens thanks for the help. i''m definitly going to use the LEFT JOIN, that''s a good idea. but i tried counting the "comments.id" and the "items.id" (instead of list_id) and i''m getting the same problem. it still seems to be multiplying the values somewhere in there. thanks. _______________________________________________ Rails mailing list Rails@lists.rubyonrails.org http://lists.rubyonrails.org/mailman/listinfo/rails