Ruby on Rails .find_by_sql quotes issue

Question

I am having an issue using the date variable in the following MySQL statement. The date variable contains a string which represents a date.

personal = Event.find_by_sql ["SELECT * 
                                 FROM events 
                                WHERE DATE(start) = ?
                                  AND HOUR(start) = ? 
                                  AND user_id = ?;", date, hour, user]

The following is the resulting query which is run on the database.

SELECT * 
  FROM events 
 WHERE DATE(start) = ''2015-02-27'' 
   AND HOUR(start) = 9 
   AND user_id = 123456789;

An extra set of quotes is added around the date string, which causes an error. Is there any way to get rid of the extra pair of quotes?

Note, the semi-colon is only required when entering queries in an interactive environment and they should be omitted when using a query interface like that. What value does `date` contain? — tadman, Feb 27 '15 at 15:38
date does have quotes, because without them it is treated as a mathematical expression and results in 1986 being used in the SQL query. The start column is of type DateTime. — Will M, Feb 27 '15 at 15:50

messanjah · Accepted Answer · 2015-02-27T16:48:19.810

0

Have you tried using where?

personal = Event.where("DATE(start) = ?", date.to_date).
  where("HOUR(start) = ?", hour).
  where(user_id: user)

edited Feb 27 '15 at 16:48

answered Feb 27 '15 at 15:59

messanjah

8,977
4
27
40

Yes, unfortunately it returns the exact same results. – Will M Feb 27 '15 at 16:03
Even if you parse `date` to a Date first, using `to_date`? – messanjah Feb 27 '15 at 16:05
Yes sorry that does fix the problem of having an extra pair of quotes, however, I only want to compare the date part of the datetime column, this was the reason for using WHERE DATE(start) = ?. – Will M Feb 27 '15 at 16:43
Of course, that was a silly oversight. Does the edited version work for you? – messanjah Feb 27 '15 at 16:48

Ruby on Rails .find_by_sql quotes issue

1 Answers1