Find_by_sql


#1

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.


#2

On Apr 26, 4:48 pm, Darren S. removed_email_address@domain.invalid
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)


#3

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 S. removed_email_address@domain.invalid


#4

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.