find_by_sql

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.

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)

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

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.