Find By SQL + multiple tables

Rails
1

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 :slight_smile:

Jeff

2

Jeff J. 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.