# find\_by\_sql + Join + Rails 2

**URL:** <https://discuss.rubyonrails.org/t/find-by-sql-join-rails-2/31116>\
**Category:** rubyonrails-talk\
**Created:** [June 26, 2008, 9:08pm UTC](https://discuss.rubyonrails.org/t/find-by-sql-join-rails-2/31116 "2008-06-26T21:08:48Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![11175](https://avatars.discourse-cdn.com/v4/letter/1/8edcca/32.png) [@11175](https://discuss.rubyonrails.org/u/11175)\
**Post date:** [June 26, 2008, 9:08pm UTC](https://discuss.rubyonrails.org/t/find-by-sql-join-rails-2/31116/1 "2008-06-26T21:08:48Z")

</div>

Hello

I have write a code to select data from two tables (T1,T2) but the problem is

@tables= T1.find\_by\_sql("select T1.\*,T2.\* from T1inner join T2on T1.followup\_id = T2.id where followups.userid = '41' ")

THe problem all values that is returned from running only T1 variables,why? Where are T2 variables??

---

<div class="post-metadata">

**Author:** ![Frederick\_Cheung](https://sea2.discourse-cdn.com/rubyonrails/user_avatar/discuss.rubyonrails.org/frederick_cheung/32/3901_2.png) [@Frederick\_Cheung](https://discuss.rubyonrails.org/u/Frederick_Cheung)\
**Post date:** [June 27, 2008, 7:55am UTC](https://discuss.rubyonrails.org/t/find-by-sql-join-rails-2/31116/2 "2008-06-27T07:55:50Z")

</div>

That's what find\_by\_sql does: you'll always get and instance of T1 (with some extra attributes though, can't remember if the console will show them by default. Also be careful doing what you;ve done because you could easily have overwritten the id attribute with the one from T2, which will cause havoc when you try and save the objects you've loaded. Lastly with the write associations you can probably do without the call to find\_by\_sql.

Fred

---

<div class="post-metadata">

**Author:** ![11175](https://avatars.discourse-cdn.com/v4/letter/1/8edcca/32.png) [@11175](https://discuss.rubyonrails.org/u/11175)\
**Post date:** [June 27, 2008, 8:40am UTC](https://discuss.rubyonrails.org/t/find-by-sql-join-rails-2/31116/3 "2008-06-27T08:40:01Z")

</div>

Frederick Cheung wrote:

---

<div class="post-metadata">

**Author:** ![Frederick\_Cheung](https://sea2.discourse-cdn.com/rubyonrails/user_avatar/discuss.rubyonrails.org/frederick_cheung/32/3901_2.png) [@Frederick\_Cheung](https://discuss.rubyonrails.org/u/Frederick_Cheung)\
**Post date:** [June 27, 2008, 9:25am UTC](https://discuss.rubyonrails.org/t/find-by-sql-join-rails-2/31116/4 "2008-06-27T09:25:06Z")

</div>

> Frederick Cheung wrote:
> 
> > > Where are T2 variables??
> > 
> > That's what find\_by\_sql does: you'll always get and instance of T1 (with some extra attributes though, can't remember if the console  
> > will show them by default. Also be careful doing what you;ve done because you could easily have overwritten the id attribute with the one from T2, which will cause havoc when you try and save the objects you've loaded. Lastly with the write associations you can probably do  
> > without the call to find\_by\_sql.
> > 
> > Fred
> 
> true, but I believe I'm correct in saying that if you did
> 
> "select T1.\*, T2.name as t2\_name ..." it would correctly load the "t2\_name" as an attribute of the T1  
> object.

Yup that should work. You just won't see t2\_name if you're messing  
around in the console (except of course if you call attributes or  
t2\_name): the default output format in rails 2 just shows attributes  
from the main table.

Fred

---

<div class="post-metadata">

**Author:** ![11175](https://avatars.discourse-cdn.com/v4/letter/1/8edcca/32.png) [@11175](https://discuss.rubyonrails.org/u/11175)\
**Post date:** [June 27, 2008, 9:39am UTC](https://discuss.rubyonrails.org/t/find-by-sql-join-rails-2/31116/5 "2008-06-27T09:39:34Z")

</div>

Frederick Cheung wrote:

---

<div class="post-metadata">

**Author:** ![Frederick\_Cheung](https://sea2.discourse-cdn.com/rubyonrails/user_avatar/discuss.rubyonrails.org/frederick_cheung/32/3901_2.png) [@Frederick\_Cheung](https://discuss.rubyonrails.org/u/Frederick_Cheung)\
**Post date:** [June 27, 2008, 9:43am UTC](https://discuss.rubyonrails.org/t/find-by-sql-join-rails-2/31116/6 "2008-06-27T09:43:49Z")

</div>

> Frederick Cheung wrote:
> 
> > > > you could easily have overwritten the id attribute with the one  
> > > > from
> > > 
> > > "select T1.\*, T2.name as t2\_name ..." it would correctly load the "t2\_name" as an attribute of the T1 object.
> > 
> > Yup that should work. You just won't see t2\_name if you're messing around in the console (except of course if you call attributes or t2\_name): the default output format in rails 2 just shows attributes from the main table.
> > 
> > Fred
> 
> yes your are right and i am tottaly accept but when i did that it  
> throws

did what?

Fred

---

<div class="post-metadata">

**Author:** ![11175](https://avatars.discourse-cdn.com/v4/letter/1/8edcca/32.png) [@11175](https://discuss.rubyonrails.org/u/11175)\
**Post date:** [June 27, 2008, 9:58am UTC](https://discuss.rubyonrails.org/t/find-by-sql-join-rails-2/31116/7 "2008-06-27T09:58:14Z")

</div>

Frederick Cheung wrote:

---

<div class="post-metadata">

**Author:** ![Frederick\_Cheung](https://sea2.discourse-cdn.com/rubyonrails/user_avatar/discuss.rubyonrails.org/frederick_cheung/32/3901_2.png) [@Frederick\_Cheung](https://discuss.rubyonrails.org/u/Frederick_Cheung)\
**Post date:** [June 27, 2008, 10:01am UTC](https://discuss.rubyonrails.org/t/find-by-sql-join-rails-2/31116/8 "2008-06-27T10:01:27Z")

</div>

> Frederick Cheung wrote:
> 
> > > > Yup that should work. You just won't see t2\_name if you're messing around in the console (except of course if you call attributes or t2\_name): the default output format in rails 2 just shows  
> > > > attributes from the main table.
> > > > 
> > > > Fred
> > > 
> > > yes your are right and i am tottaly accept but when i did that it throws
> > 
> > did what?
> > 
> > Fred
> 
> @tables= T1.find\_by\_sql("select T1.\*,T2.\* from T1 inner join T2on T1.followup\_id = T2.id where followups.userid = '41' ") puts @tables[:t2\_name]

@tables is an array. You'd have to do something like @tables.first[:foo]

Fred

---

<div class="post-metadata">

**Author:** ![11175](https://avatars.discourse-cdn.com/v4/letter/1/8edcca/32.png) [@11175](https://discuss.rubyonrails.org/u/11175)\
**Post date:** [June 27, 2008, 10:29am UTC](https://discuss.rubyonrails.org/t/find-by-sql-join-rails-2/31116/9 "2008-06-27T10:29:22Z")

</div>

Mohamed Saeed wrote:

> @tables= T1.find\_by\_sql("select T1.\*,T2.\* from T1 inner join T2on T1.followup\_id = T2.id where followups.userid = '41' ") puts @tables[:t2\_name]

and you missed out the "T2.name as t2\_name";

---

<div class="post-metadata">

**Author:** ![11175](https://avatars.discourse-cdn.com/v4/letter/1/8edcca/32.png) [@11175](https://discuss.rubyonrails.org/u/11175)\
**Post date:** [June 27, 2008, 11:02am UTC](https://discuss.rubyonrails.org/t/find-by-sql-join-rails-2/31116/10 "2008-06-27T11:02:21Z")

</div>

Matthew Rudy Jacobs wrote:

> Mohamed Saeed wrote:
> 
> > @tables= T1.find\_by\_sql("select T1.\*,T2.\* from T1 inner join T2on T1.followup\_id = T2.id where followups.userid = '41' ") puts @tables[:t2\_name]
> 
> and you missed out the "T2.name as t2\_name";
> 
> > > @tables= T1.find\_by\_sql("select T1.\*,T2.name AS t2\_name from T1 inner join T2 on T1.followup\_id = T2.id where followups.userid = '41' ") puts @tables.first[:t2\_name]

Thanks Fred Now it's working the problem was in @tables.first[:t2\_name], also you are right console display only the attributes of the table that i used in finding "T1"

Thanks Mohamed