# Find\_by\_sql with :include?

**URL:** <https://www.ruby-forum.com/t/find-by-sql-with-include/67242>\
**Category:** Rails\
**Created:** [September 1, 2006, 4:24pm UTC](https://www.ruby-forum.com/t/find-by-sql-with-include/67242 "2006-09-01T16:24:30Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![jeff](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/e19b73/32.png) [@jeff](https://www.ruby-forum.com/u/jeff)\
**Post date:** [September 1, 2006, 4:24pm UTC](https://www.ruby-forum.com/t/find-by-sql-with-include/67242/1 "2006-09-01T16:24:30Z")

</div>

When you use find, you can include related objects with include so that  
both objects get instantiated in the results, e.g. Foo.find(:all,  
:include =\> :bar).

But when you use find\_by\_sql, is there a way to do this? You could  
definitely write the find\_by\_sql SQL to join Foo and Bar tables:  
Foo.find\_by\_sql(“SELECT foo._, bar._ from foo, bar where foo.bar\_id =  
bar.id”)  
But is there a way to instantiate not just the Foo object but also the  
Bar object in the results that come back?

thanks,  
jeff

---

<div class="post-metadata">

**Author:** ![jeff](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/e19b73/32.png) [@jeff](https://www.ruby-forum.com/u/jeff)\
**Post date:** [September 1, 2006, 8:09pm UTC](https://www.ruby-forum.com/t/find-by-sql-with-include/67242/2 "2006-09-01T20:09:15Z")

</div>

Jeff wrote:

> When you use find, you can include related objects with include so that  
> both objects get instantiated in the results, e.g. Foo.find(:all,  
> :include =\> :bar).
> 
> But when you use find\_by\_sql, is there a way to do this? You could  
> definitely write the find\_by\_sql SQL to join Foo and Bar tables:  
> Foo.find\_by\_sql(“SELECT foo._, bar._ from foo, bar where foo.bar\_id =  
> bar.id”)  
> But is there a way to instantiate not just the Foo object but also the  
> Bar object in the results that come back?

Yes: Install the AR mod at [http://mrj.bpa.nu/eager\_custom\_sql.rb](http://mrj.bpa.nu/eager_custom_sql.rb) ,  
add the include option, then write your SQL using the tn\_rm field  
alias pattern used by Rails’ eager loading.

–  
We develop, watch us RoR, in numbers too big to ignore.

---

<div class="post-metadata">

**Author:** ![jeff](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/e19b73/32.png) [@jeff](https://www.ruby-forum.com/u/jeff)\
**Post date:** [September 1, 2006, 9:13pm UTC](https://www.ruby-forum.com/t/find-by-sql-with-include/67242/3 "2006-09-01T21:13:23Z")

</div>

Thank you for that, Mark. Regarding the field aliasing, can I simply  
write my select as  
"SELECT t0._, t1._ FROM t0, t1 WHERE … "

or do I explicitly have to select each column, like  
“SELECT t0.id as id, t0.name as name, etc…”

thanks,  
jeff

---

<div class="post-metadata">

**Author:** ![jeff](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/e19b73/32.png) [@jeff](https://www.ruby-forum.com/u/jeff)\
**Post date:** [September 2, 2006, 5:43am UTC](https://www.ruby-forum.com/t/find-by-sql-with-include/67242/4 "2006-09-02T05:43:50Z")

</div>

Jeff wrote:

> Thank you for that, Mark. Regarding the field aliasing, can I simply  
> write my select as  
> "SELECT t0._, t1._ FROM t0, t1 WHERE … "
> 
> or do I explicitly have to select each column, like  
> “SELECT t0.id as id, t0.name as name, etc…”

As things stand you have to explicitly alias every field:

select foo.id as t0\_r0, foo.f1 as t0\_r1, bar.id as t1\_r0, bar.f1 as  
t1\_r1

Actually, you can skimp and select only the fields you’re going to use.

–  
We develop, watch us RoR, in numbers too big to ignore.