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.