Rails - Mar 2006 - Find By SQL + multiple tables

Hello all. I am trying to get the following SQL to work in Ruby on rails 
and Oracle.

SELECT bugsheet.*, bugcomplete.bc_time FROM bugsheet, bugcomplete WHERE 
bugsheet.bs_id = bugcomplete.bc_id AND bugsheet.bs_patchrel like 
''5.1.12-B4%'' ORDER BY bs_cdate DESC, bc_time DESC

If I type that into an SQL program it works fine, if I try and use the 
following in RoR:

Bugsheet.find_by_sql["SELECT bugsheet.*, bugcomplete.bc_time FROM 
bugsheet, bugcomplete WHERE bugsheet.bs_id = bugcomplete.bc_id AND 
bugsheet.bs_patchrel
like ? ORDER BY ?", @criteria, sort_clause]

It returns an empty array. I have checked that @criteria and sort_clause 
are populated using the breakpointer.

Is it because I am selecting from two tables using a single models 
.find_by_sql?

I thought about using left join:

SELECT bugsheet.bs_cdate, bugcomplete.bc_time FROM bugsheet left join 
bugcomplete ON bugsheet.bs_id = bugcomplete.bc_id ;

But that gives me an error in the SQL program, let alone RoR

Any help from RoR / database gurus would be much apprciated and rewarded 
with a metaphorical cookie :)

Jeff

-- 
Posted via http://www.ruby-forum.com/.

Jeff Jones wrote:
> Bugsheet.find_by_sql["SELECT bugsheet.*, bugcomplete.bc_time FROM 
> bugsheet, bugcomplete WHERE bugsheet.bs_id = bugcomplete.bc_id AND 
> bugsheet.bs_patchrel
> like ? ORDER BY ?", @criteria, sort_clause]
> 
> It returns an empty array. I have checked that @criteria and sort_clause 
> are populated using the breakpointer.
Confirm by looking at the DB queries in your Rails log, but I think
your problem is that your ORDER BY clause is getting quoted.  Write
instead "...ORDER BY #{sort_clause}".

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