Rails problem find by sql
I have this query and I have a开发者_如何学Gon error:
images = Image.find_by_sql('PREPARE stmt FROM \'
SELECT *
FROM images AS i
WHERE i.on_id = 1 AND i.on_type = "profile"
ORDER BY i.updated_at
LIMIT ?, 6\
'; SET @lower_limit := ((5 DIV 6) * 6); EXECUTE stmt USING @lower_limit;')
Mysql::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET @lower_limit := ((5 DIV 6) * 6); EXECUTE stmt USING @lower_limit' at line 1: PREPARE stmt FROM ' SELECT * FROM images AS i WHERE i.on_id = 1 AND i.on_type = "profile" ORDER BY i.updated_at LIMIT ?, 6'; SET @lower_limit := ((5 DIV 6) * 6); EXECUTE stmt USING @lower_limit;
EDIT: after finding the answer to: MySQL: I need to get the offset of a item in a query i need another help here to port it to rails.
I suspect that you might not be allowed to use semicolon-delimited query sets in find_by_sql
. Try finding a way to just use the happy Rails find
method (see @Sleepycat's answer, for example).
Is it just choking on the quotes? I am assuming that statement runs ok on mysql already. Try this:
Image.find_by_sql("PREPARE stmt FROM \'
SELECT *
FROM images AS i
WHERE i.on_id = 1 AND i.on_type = 'profile'
ORDER BY i.updated_at
LIMIT ?, 6\
'; SET @lower_limit := ((5 DIV 6) * 6); EXECUTE stmt USING @lower_limit;")
I figured I would make a new answer because I ran out of space in the comment. I think you might be making life complicated for yourself with that statement. I am pretty sure you can achieve the effect you are looking for with less pain with a regular Rails find statement. It will be more portable (across databases) as well. I don't really understand whats going on in there but something like this:
Image.find :all, :conditions => ["on_id = :on_id and on_type = :on_type", {:on_id => 1, :on_type => "profile"}], :order => "updated_at" :limit => (whatever calculation you are using to get your limit)
The syntax might be a little off but it should get you in the ball park. In general you should be trying to keep logic out of the database and in your models.
精彩评论