Rails query [3.2]

Rails
1

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

2

[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

3

Hi Erwin,

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

On Sat, Apr 21, 2012 at 8:12 AM, Erwin [email protected] wrote:

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

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