ActiveRecord problem with find and :join?


#1

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.idhttp://u.idWHERE (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.idhttp://u.idWHERE (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


#2

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


#3

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


#4

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.


#5

Thank you Rick, that made it tick!
Christer
2005/11/14, Rick O. removed_email_address@domain.invalid: