rails query [3.2]

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

[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

Hi Erwin,

I'm sorry I didn't see your first post.

<snip>

I would like to have a single line to get both, posted and commented counts

<snip>

where am I wrong ? thanks for feedback

Why 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