Is find compatible with :join? The example below returns three rows, with id:s 1, 1, and 1. I expected 1, 2 and 3. One User has many Reports. @reports = Report.find(:all, :conditions => "user_id=1", :joins => "AS r INNER JOIN Users AS u ON r.user_id = u.id <http://u.id>") SELECT * FROM reports AS r INNER JOIN Users AS u ON r.user_id u.id<http://u.id>WHERE (user_id=1) When I use find_by_sql with SELECT r.* FROM reports AS r INNER JOIN Users AS u ON r.user_id u.id<http://u.id>WHERE (user_id=1) I receive 1, 2 and 3 as expected. It seems find uses u.id <http://u.id> instead of r.id <http://r.id> Christer _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Julian ''Julik'' Tarkhanov
2005-Nov-14 21:34 UTC
Re: ActiveRecord problem with find and :join ?
On 14-nov-2005, at 21:40, NILSSON Christer wrote:> Is find compatible with :join? The example below returns three > rows, with id:s 1, 1, and 1. > I expected 1, 2 and 3. One User has many Reports. > > @reports = Report.find(:all, :conditions => "user_id=1", :joins => > "AS r INNER JOIN Users AS u ON r.user_id = u.id") > > SELECT * FROM reports AS r INNER JOIN Users AS u ON r.user_id = > u.id WHERE (user_id=1) > > When I use find_by_sql with > > SELECT r.* FROM reports AS r INNER JOIN Users AS u ON r.user_id = > u.id WHERE (user_id=1) > > I receive 1, 2 and 3 as expected. > It seems find uses u.id instead of r.idThis is a complicated matter. Shortly speaking, it was decided NOT to allow AR to substitute table.* automatically because no one known how the table is going to be aliased at the moment the query has to be written. There are quite some tickets about the issue. -- Julian "Julik" Tarkhanov
On 11/14/05, Julian ''Julik'' Tarkhanov <listbox-RY+snkucC20@public.gmane.org> wrote:> > On 14-nov-2005, at 21:40, NILSSON Christer wrote: > > > Is find compatible with :join? The example below returns three > > rows, with id:s 1, 1, and 1. > > I expected 1, 2 and 3. One User has many Reports. > > > > @reports = Report.find(:all, :conditions => "user_id=1", :joins => > > "AS r INNER JOIN Users AS u ON r.user_id = u.id") > > > > SELECT * FROM reports AS r INNER JOIN Users AS u ON r.user_id > > u.id WHERE (user_id=1) > > > > When I use find_by_sql with > > > > SELECT r.* FROM reports AS r INNER JOIN Users AS u ON r.user_id > > u.id WHERE (user_id=1) > > > > I receive 1, 2 and 3 as expected. > > It seems find uses u.id instead of r.id > > This is a complicated matter. Shortly speaking, it was decided NOT to > allow AR to substitute table.* automatically because no one known how > the table is going to be aliased at the moment the query has to be > written. There are quite some tickets about the issue. > > -- > Julian "Julik" Tarkhanov > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >users.id is clobbering r.id. When it maps the columns to attributes, it ignores any table alias. Try this: @reports = Report.find(:all, \ :select => ''r.*'', :conditions => "user_id=1", :joins => "AS r INNER JOIN Users AS u ON r.user_id = u.id") -- rick http://techno-weenie.net
Thank you Rick, that made it tick! Christer 2005/11/14, Rick Olson <technoweenie-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>:> > On 11/14/05, Julian ''Julik'' Tarkhanov <listbox-RY+snkucC20@public.gmane.org> wrote: > > > > On 14-nov-2005, at 21:40, NILSSON Christer wrote: > > > > > Is find compatible with :join? The example below returns three > > > rows, with id:s 1, 1, and 1. > > > I expected 1, 2 and 3. One User has many Reports. > > > > > > @reports = Report.find(:all, :conditions => "user_id=1", :joins => > > > "AS r INNER JOIN Users AS u ON r.user_id = u.id <http://u.id>") > > > > > > SELECT * FROM reports AS r INNER JOIN Users AS u ON r.user_id > > > u.id <http://u.id> WHERE (user_id=1) > > > > > > When I use find_by_sql with > > > > > > SELECT r.* FROM reports AS r INNER JOIN Users AS u ON r.user_id > > > u.id <http://u.id> WHERE (user_id=1) > > > > > > I receive 1, 2 and 3 as expected. > > > It seems find uses u.id <http://u.id> instead of r.id <http://r.id> > > > > This is a complicated matter. Shortly speaking, it was decided NOT to > > allow AR to substitute table.* automatically because no one known how > > the table is going to be aliased at the moment the query has to be > > written. There are quite some tickets about the issue. > > > > -- > > Julian "Julik" Tarkhanov > > > > _______________________________________________ > > Rails mailing list > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > users.id <http://users.id> is clobbering r.id <http://r.id>. When it maps > the columns to attributes, > it ignores any table alias. Try this: > > @reports = Report.find(:all, \ > :select => ''r.*'', > :conditions => "user_id=1", > :joins => "AS r INNER JOIN Users AS u ON r.user_id = u.id <http://u.id>") > > -- > rick > http://techno-weenie.net > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >_______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
technoweenie wrote:>Try this: > > @reports = Report.find(:all, \ > :select => ''r.*'', > :conditions => "user_id=1", > :joins => "AS r INNER JOIN Users AS u ON r.user_id = u.id") > > -- > rick > http://techno-weenie.netMany thanks, Rick. This solved my issue as well. -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---