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.
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)
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.