with 3 models : user has_many posts / user has_many comments
Using the new Active Record Query Interface , I am trying to write a
query involving 2 counts in the select method :
writing
@search = User.joins(:posts).select("*, users.id as user_id,
COUNT(posts.id) as posted").uniq.group(''users.id'')
generates the SQL :
SELECT DISTINCT users.id as user_id, COUNT(posts.id) as posted FROM
`users` INNER JOIN `posts` ON `posts`.`user_id` = `users`.`id` GROUP
BY users.id;
which is fine. I get
user_id posted
1 9
2 1
3 3
4 14
5 17
I can also write a similar line to query the users.comments
( replacing posts by comments ...
@search = User.joins(:comments).select("*, users.id as user_id,
COUNT(comments.id) as commented").uniq.group(''users.id'')
which generates the SQL:
SELECT DISTINCT users.id as user_id, COUNT(comments.id) as commented
FROM `users` INNER JOIN `comments` ON `comments`.`user_id` `users`.`id` GROUP BY
users.id;
also correct, and I get
user_id commented
1 42
2 40
3 40
4 32
5 30
I would like to have a single line to get both, posted and commented
counts, but if I write :
@search = User.joins(:posts, :comments).select("*, users.id as
user_id, COUNT(posts.id) as posted, COUNT(comments.id) as
commented").uniq.group(''users.id'') , this generates the
SQL:
SELECT DISTINCT users.id as user_id, COUNT(IF(comments.user_id users.id, 1,
NULL)) as commented FROM `users` INNER JOIN `comments` ON
`comments`.`user_id` = `users`.`id` GROUP BY users.id;
user_id posted commented
and I get with :
user_id posted commented
1 378 378
2 40 40
3 120 120
4 448 448
5 510 510
which is the combined number of records : posted * commented ....
and not
user_id posted commented
1 9 42
2 1 40
3 3 40
4 14 32
5 17 30
where am I wrong ? thanks for feedback
--
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
[SOLVED] after many sql tests in console .. I got : SELECT DISTINCT users.id as user_id, COUNT(DISTINCT posts.id) as posted, COUNT(DISTINCT comments.id) as commented FROM `users` INNER JOIN `posts` ON `posts`.`user_id` = `users`.`id` INNER JOIN `comments` ON `comments`.`user_id` = `users`.`id` GROUP BY users.id ORDER BY `users`.`id` ASC; now need to write it as a Rails query.... I guess a scope will be appropriate On Apr 21, 12:41 pm, Erwin <yves_duf...-ee4meeAH724@public.gmane.org> wrote:> with 3 models : user has_many posts / user has_many comments > Using the new Active Record Query Interface , I am trying to write a > query involving 2 counts in the select method : > writing > > @search = User.joins(:posts).select("*, users.id as user_id, > COUNT(posts.id) as posted").uniq.group(''users.id'') > generates the SQL : > SELECT DISTINCT users.id as user_id, COUNT(posts.id) as posted FROM > `users` INNER JOIN `posts` ON `posts`.`user_id` = `users`.`id` GROUP > BY users.id; > which is fine. I get > user_id posted > 1 9 > 2 1 > 3 3 > 4 14 > 5 17 > > I can also write a similar line to query the users.comments > ( replacing posts by comments ... > @search = User.joins(:comments).select("*, users.id as user_id, > COUNT(comments.id) as commented").uniq.group(''users.id'') > which generates the SQL: > SELECT DISTINCT users.id as user_id, COUNT(comments.id) as commented > FROM `users` INNER JOIN `comments` ON `comments`.`user_id` > `users`.`id` GROUP BY users.id; > also correct, and I get > user_id commented > 1 42 > 2 40 > 3 40 > 4 32 > 5 30 > > I would like to have a single line to get both, posted and commented > counts, but if I write : > @search = User.joins(:posts, :comments).select("*, users.id as > user_id, COUNT(posts.id) as posted, COUNT(comments.id) as > commented").uniq.group(''users.id'') , this generates the SQL: > SELECT DISTINCT users.id as user_id, COUNT(IF(comments.user_id > users.id, 1, NULL)) as commented FROM `users` INNER JOIN `comments` ON > `comments`.`user_id` = `users`.`id` GROUP BY users.id; > user_id posted commented > and I get with : > user_id posted commented > 1 378 378 > 2 40 40 > 3 120 120 > 4 448 448 > 5 510 510 > > which is the combined number of records : posted * commented .... > and not > user_id posted commented > 1 9 42 > 2 1 40 > 3 3 40 > 4 14 32 > 5 17 30 > > where am I wrong ? thanks for feedback-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Hi Erwin, I''m sorry I didn''t see your first post. On Sat, Apr 21, 2012 at 8:12 AM, Erwin <yves_dufour-ee4meeAH724@public.gmane.org> wrote: <snip>>> I would like to have a single line to get both, posted and commented >> counts<snip>>> where am I wrong ? thanks for feedbackWhy do you want to count 2 separate / independent resources in one SQL statement? I''d bet a nickel that it''s going to be less efficient from a processing perspective, and it''s certainly less readable than: users = User.includes(:posts, :comments) users.each do |u| puts u.id.to_s + u.posts.size.to_s + u.comments.size.to_s end Best regards, Bill -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.