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

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?


-- 
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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?...

On Monday, September 5, 2011 10:11:24 PM UTC+4, Dmitry Suzdalev
wrote:
>
> 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? 
>
>
>
-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/rubyonrails-talk/-/NtD1qkAGVgoJ.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On Sep 6, 3:51 pm, Dmitry Suzdalev
<dim...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
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

> 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?...
>
>
>
>
>
>
>
> On Monday, September 5, 2011 10:11:24 PM UTC+4, Dmitry Suzdalev wrote:
>
> > 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?
-- 
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
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.

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? 

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/rubyonrails-talk/-/eRGg1eb6utAJ.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On Sep 6, 4:23 pm, Dmitry Suzdalev
<dim...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> 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?
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

-- 
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
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.

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

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/rubyonrails-talk/-/y33viRcbKnEJ.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On Sep 6, 6:10 pm, Dmitry Suzdalev
<dim...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> Ah, I see.
> Unfortunately I need exactly to call a count() method with left outer join
> of another table....
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

-- 
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
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.

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...

On Tuesday, September 6, 2011 10:57:15 PM UTC+4, Frederick Cheung
wrote:
>
>
>
> On Sep 6, 6:10 pm, Dmitry Suzdalev
<dim...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > Ah, I see. 
> > Unfortunately I need exactly to call a count() method with left outer 
> join 
> > of another table.... 
>
> 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
-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/rubyonrails-talk/-/320G_zLSWbMJ.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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

See this thread for more info:
http://goo.gl/30nQF

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/rubyonrails-talk/-/iJaxrEppoHAJ.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.