Find_by_sql with :include?

Rails
1

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

2

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 ,
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.

3

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

4

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.