Forum: Ruby on Rails find_by_sql

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.
8fbbbaa4d15d97ad290271dcd59b2892?d=identicon&s=25 Darren Strom (dmarc)
on 2009-04-26 17:48
I'm having some difficulty getting find_by_sql to work.

This code below works perfect:

@available = Elevator.find_by_sql("select name from elevator_timeslots
WHERE NOT EXISTS(select elevator_timeslot_id from elevators where
elevators.elevator_timeslot_id = elevator_timeslots.id)")

Now I'm trying to add one more condition which is causing me grief:

@available = Elevator.find_by_sql("select name from elevator_timeslots
WHERE NOT EXISTS(select elevator_timeslot_id from elevators where
elevators.elevator_timeslot_id = elevator_timeslots.id
and elevators.booked_date = ?", params[:booked_date]))   ** Added line
not working

Basically using

and elevators.booked_date = ?, params[:booked_date] is causing only
errors.

Any advise, I truely appreciate it.

Thanks in advance.
81b61875e41eaa58887543635d556fca?d=identicon&s=25 Frederick Cheung (Guest)
on 2009-04-26 22:20
(Received via mailing list)
On Apr 26, 4:48 pm, Darren Strom <rails-mailing-l...@andreas-s.net>
wrote:

> Basically using
>
> and elevators.booked_date = ?, params[:booked_date] is causing only
> errors.
>
> Any advise, I truely appreciate it.
>
much like the :conditions  option requires either a string, or an
array containing a string and the parameter values (or a hash, but
that's irrelevant here), you need to either pass a single string to
find_by_sql or an array, ie Foo.find_by_sql(["some sql", param1,
param2])

Fred

(tip for next time: describe what errors you get rather than just
saying it isn't working)
10f7e00c1c5c60e2173995eb16f4ffc7?d=identicon&s=25 pepe (Guest)
on 2009-04-27 02:36
(Received via mailing list)
In case any more help is needed, what Fred is saying is that the
question mark is not going to be replaced by anything because you are
using a string and not an array for your find_by_sql. When the code
runs the question mark will stay and the SQL engine will probably
either find nothing because 'elevators.booked_date' will never equals
'?' or will blow up.

For your code to work you would need something like this (check the
[ and ] ):

...find_by_sql(["select name from elevator_timeslots WHERE NOT EXISTS
(select elevator_timeslot_id from elevators where
elevators.elevator_timeslot_id = elevator_timeslots.id and
elevators.booked_date = ?)", params[:booked_date]])

Now you have an array (between the square brackets) and the value of
the second element (params[:booked_date]) will replace your question
mark.

Pepe

On Apr 26, 11:48 am, Darren Strom <rails-mailing-l...@andreas-s.net>
8fbbbaa4d15d97ad290271dcd59b2892?d=identicon&s=25 Darren Strom (dmarc)
on 2009-04-27 05:58
Guys thanks so much for all your help.  The sql is working great.

I really do appreciate your prompt replies.

Thanks, so much once again.
This topic is locked and can not be replied to.