CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-4activerecord
zeeMonkeez
zeeMonkeez

Reputation: 5157

Count, empty? fails for ActiveRecord with outer joins

I have two models, Monkey and Session, where Monkey has_many Session. I have a scope for Monkey:

scope :with_session_counts, -> {
    joins("LEFT OUTER JOIN `sessions` ON `sessions`.`monkey_id` = `monkeys`.`id`")
    .group(:id)
    .select("`monkeys`.*, COUNT(DISTINCT `sessions`.`id`) as session_count")
}

in order to grab the number of associated Sessions (even when 0).

Querying @monkeys = Monkey.with_session_counts works as expected. However, when I test in my view:

<% unless @monkeys.empty?%>

I get this error: 

Mysql2::Error: Column 'id' in field list is ambiguous:
SELECT  COUNT(*) AS count_all, id AS id FROM `monkeys`
LEFT OUTER JOIN `sessions` ON `sessions`.`monkey_id` = `monkeys`.`id`
GROUP BY `monkeys`.`id`

How would I convince Rails to prefix id with the table name in presence of the JOIN? Or is there a better alternative for the OUTER JOIN?

This applies equally to calling @monkeys.count(:all). I'm using RoR 4.2.1.

Update:

I have a partial fix for my issue (specify group("monkeys.id") explicitly) I wonder whether this is a bug in the code that generates the SELECT clause for count(:all). Note that in both cases (group("monkeys.id") and group(:id)) the GROUP BY part is generated correctly (i.e. with monkeys.id), but in the latter case the SELECT only contains id AS id. The reason I say 'partial' is because it works in that it does not break a call to empty?, but a call to count(:all) returns a Hash {monkey_id => number_of_sessions} instead of the number of records.

Update 2: I guess my real question is: How can I get the number of associated sessions for each monkey, so that for all intents and purposes I can work with the query result as with Monkey.all? I know about counter cache but would prefer not to use it.

Upvotes: 0

Views: 218

Answers (1)

RPinel
RPinel

Reputation: 896

I believe it is not a bug. Like you added on your update, you have to specify the table that the id column belongs to. In this case group('monkeys.id') would do it.

How would the code responsible for generating the statement know the table to use? Without the count worked fine because it adds points.* to the projection and that is the one used by group by. However, if you actually wanted to group by Sessions id, you would have to specify it anyway.

Upvotes: 1

Related Questions