find_by_sql with :include?

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

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.

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

Mark Reginald James wrote:

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.