In Rails 3.1 Model.count() ignores :include - no outer join in generated SQL

[Dmitry Suzdalev]

Hello!

Just upgraded to Rails 3.1, ran my test and found this issue:

class Trade < ActiveRecord::Base
has_many :transaction_trades
.....

def Trade.do_something
stale_trades = Trade.count('transaction_trades.id',
:include => :transaction_trades,
:group => 'trades.id')
:having => "count_transaction_trades_id =
0")
end
end

Earlier this used to work perfectly, now I get this:
Mysql2::Error: Unknown column 'transaction_trades.id' in 'field list':
SELECT COUNT(transaction_trades.id) AS count_transaction_trades_id,
trades.id AS trades_id FROM `trades` GROUP BY trades.id

What puzzles me is that no LEFT OUTER JOIN on transaction_trades is
present in generated SQL - that's why things broke.
Any hints what's wrong here? Why is :include => :transaction_trades
seems to be ignored?

[Dmitry Suzdalev]

So guys, no ideas about this one?

Why Model.count is completely broken in 3.1 when using :include option?

(see my original mail for details)

I consider two possibilities:

1) there might be some new option which I need to enable (though strange that this was not announced)

2) this is some regression specific to Rails 3.1 and I should report this as a bug upstream

If this is 2) I guess I should forward this to rails-core mailing list?...

[Frederick Cheung]

On Sep 6, 3:51 pm, Dmitry Suzdalev <dim...@gmail.com> wrote:
> So guys, no ideas about this one?
> Why Model.count is completely broken in 3.1 when using :include option?
> (see my original mail for details)
>

From which version did you upgrade?
Part of the problem with include is that there has (for some years
now) been two different code paths that rails can do down when there
is an :include option, one that uses joins and one that doesn't.
Rails tries to pick the right one if it things you are references the
joined columns elsewhere in your code, but that detection has never
been 100%

If my memory is correct, the eager_load option forces the use of the
join variant.

Fred

[Dmitry Suzdalev]

On Tuesday, September 6, 2011 7:12:05 PM UTC+4, Frederick Cheung wrote:

From which version did you upgrade?

Upgraded from 3.0.10. 

Before upgrading I ran all of my tests to ensure they pass.

After upgrading got a couple of test failures related to this issue...

 

Part of the problem with include is that there has (for some years  

now) been two different code paths that rails can do down when there
is an :include option, one  that uses joins and one that doesn't.
Rails tries to pick the right one if it things you are references the
joined columns elsewhere in your code, but that detection has never
been 100% 

I see...

 

If my memory is correct, the eager_load option forces the use of the
join variant.

eager_load is some configuration option? 

[Frederick Cheung]

It's an option for individual finds that works just like includes, but
forces rails to use a left join, so instead of doing
SomeModel.includes(:some_association) you can do
SomeModel.eager_load(:some_association). I assume that you can also
pass it as an option to find.

Fred

[Dmitry Suzdalev]

Ah, I see.

Unfortunately I need exactly to call a count() method with left outer join of another table....

[Frederick Cheung]

If you want a join, why not use the :joins option ? (I think there's
an option these days for that to do left joins, if not then you can
always spell out the join clause explicitly)

Fred

[Dmitry Suzdalev]

Well, I need the outer join to find if some fields of right table are null wrt left one...

Good point about spelling joins clause, thank you! Will try that! :)

But other than that - do you think this might be a bug I should report? :-)

I'd be willing to do so...

[Dmitry Suzdalev]

Just for the record (in case anyone would search for the same thing):

See this thread for more info:

http://goo.gl/30nQF