Forum: Ruby on Rails ActiveRecord problem with find and :join ?

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
christer.nilsson (Guest)
on 2005-11-14 22:41
(Received via mailing list)
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
listbox (Guest)
on 2005-11-14 23:35
(Received via mailing list)
On 14-nov-2005, at 21:40, NILSSON Christer wrote:

> 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
technoweenie (Guest)
on 2005-11-14 23:44
(Received via mailing list)
On 11/14/05, Julian 'Julik' Tarkhanov <removed_email_address@domain.invalid> 
wrote:
> > SELECT * FROM reports AS r INNER JOIN Users AS u ON r.user_id =
> This is a complicated matter. Shortly speaking, it was decided NOT to
> 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
christer.nilsson (Guest)
on 2005-11-15 00:14
(Received via mailing list)
Thank you Rick, that made it tick!
 Christer
 2005/11/14, Rick O. <removed_email_address@domain.invalid>:
James Werwath (Guest)
on 2007-02-10 01:24
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.net

Many thanks, Rick.  This solved my issue as well.
This topic is locked and can not be replied to.