Forum: Ruby on Rails Find By SQL + multiple tables

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Jeff J. (Guest)
on 2006-03-07 14:28
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
Mark Reginald J. (Guest)
on 2006-03-07 15:00
(Received via mailing list)
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.
This topic is locked and can not be replied to.