# ActiveRecord problem with find and :join?

**URL:** <https://www.ruby-forum.com/t/activerecord-problem-with-find-and-join/48547>\
**Category:** Rails\
**Created:** [November 14, 2005, 9:41pm UTC](https://www.ruby-forum.com/t/activerecord-problem-with-find-and-join/48547 "2005-11-14T21:41:10Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![christer.nilsson](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/f6c823/32.png) [@christer.nilsson](https://www.ruby-forum.com/u/christer.nilsson)\
**Post date:** [November 14, 2005, 9:41pm UTC](https://www.ruby-forum.com/t/activerecord-problem-with-find-and-join/48547/1 "2005-11-14T21:41:10Z")

</div>

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](http://u.id)”)  
SELECT \* FROM reports AS r INNER JOIN Users AS u ON r.user\_id =  
u.id[http://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](http://u.id)WHERE (user\_id=1)  
I receive 1, 2 and 3 as expected.  
It seems find uses u.id [http://u.id](http://u.id) instead of r.id [http://r.id](http://r.id)  
Christer

---

<div class="post-metadata">

**Author:** ![christer.nilsson](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/f6c823/32.png) [@christer.nilsson](https://www.ruby-forum.com/u/christer.nilsson)\
**Post date:** [November 14, 2005, 10:35pm UTC](https://www.ruby-forum.com/t/activerecord-problem-with-find-and-join/48547/2 "2005-11-14T22:35:25Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![christer.nilsson](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/f6c823/32.png) [@christer.nilsson](https://www.ruby-forum.com/u/christer.nilsson)\
**Post date:** [November 14, 2005, 10:44pm UTC](https://www.ruby-forum.com/t/activerecord-problem-with-find-and-join/48547/3 "2005-11-14T22:44:26Z")

</div>

On 11/14/05, Julian ‘Julik’ Tarkhanov [removed\_email\_address@domain.invalid](mailto: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](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](http://techno-weenie.net)

---

<div class="post-metadata">

**Author:** ![christer.nilsson](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/f6c823/32.png) [@christer.nilsson](https://www.ruby-forum.com/u/christer.nilsson)\
**Post date:** [February 10, 2007, 12:24am UTC](https://www.ruby-forum.com/t/activerecord-problem-with-find-and-join/48547/4 "2007-02-10T00:24:01Z")

</div>

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](http://techno-weenie.net)

Many thanks, Rick. This solved my issue as well.

---

<div class="post-metadata">

**Author:** ![christer.nilsson](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/f6c823/32.png) [@christer.nilsson](https://www.ruby-forum.com/u/christer.nilsson)\
**Post date:** [November 14, 2005, 11:14pm UTC](https://www.ruby-forum.com/t/activerecord-problem-with-find-and-join/48547/5 "2005-11-14T23:14:32Z")

</div>

Thank you Rick, that made it tick!  
Christer  
2005/11/14, Rick O. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid):