[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 <[email protected]> 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 [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
