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.
SOLVED: This generates 2 queries and is worse, but now i can get the offset of the image. The other way around was with just one query, but i would not got any offset of the image, and anyway I couldn't make it work.
def self.get_image_offset(id)
image_offset = Image.find_by_sql("SELECT COUNT(id) as pos FROM images WHERE updated_at <= (SELECT updated_at FROM images WHERE id = #{id})")[0].pos.to_i
end
def self.get_group_offset(id, per_block, image_offset = nil)
image_offset ||= Image.get_image_offset(id)
group_offset = (image_offset / per_block).floor * per_block
{:image_offset => image_offset, :group_offset => group_offset, :group_number => ( group_offset + per_block ) / per_block}
end