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@googlegroups.com.
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.

Search Discussions

  • Erwin at Apr 21, 2012 at 1:12 pm
    [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 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@googlegroups.com.
    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.
  • Bill Walton at Apr 21, 2012 at 1:53 pm
    Hi Erwin,

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

    On Sat, Apr 21, 2012 at 8:12 AM, Erwin 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

    --
    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@googlegroups.com.
    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.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouprubyonrails-talk @
categoriesrubyonrails
postedApr 21, '12 at 10:42a
activeApr 21, '12 at 1:53p
posts3
users2
websiterubyonrails.org
irc#RubyOnRails

2 users in discussion

Erwin: 2 posts Bill Walton: 1 post

People

Translate

site design / logo © 2021 Grokbase