Forum: Ruby on Rails rails query [3.2]

Ee0293412faca21e4ebf3326ed9d5f8c?d=identicon&s=25 Kad Kerforn (kadoudal)
on 2012-04-21 12:43
(Received via mailing list)
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
Ee0293412faca21e4ebf3326ed9d5f8c?d=identicon&s=25 Kad Kerforn (kadoudal)
on 2012-04-21 15:13
(Received via mailing list)
[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
91a72b0ad3e43e3ef3a31816667ec694?d=identicon&s=25 Bill Walton (Guest)
on 2012-04-21 15:54
(Received via mailing list)
Hi Erwin,

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

On Sat, Apr 21, 2012 at 8:12 AM, Erwin <yves_dufour@mac.com> wrote:
<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
Please log in before posting. Registration is free and takes only a minute.
Existing account

NEW: Do you have a Google/GoogleMail, Yahoo or Facebook account? No registration required!
Log in with Google account | Log in with Yahoo account | Log in with Facebook account
No account? Register here.