Rails problem find by sql

Question

I have this query and I have an 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: http://stackoverflow.com/questions/2566620/mysql-i-need-to-get-the-offset-of-a-item-in-a-query i need another help here to port it to rails.

Answer 1

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;")

Answer 2

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.

Answer 3

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