开发者

Rails more statements with ; doesnt work... :s

开发者 https://www.devze.com 2022-12-25 07:13 出处:网络
I have this code, but i cant make it work: 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

I have this code, but i cant make it work:

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

I got this error:

Mysql::Error: You have an error in your SQL syntax; check the manual that corresponds to your My开发者_Python百科SQL 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;

but if i use a sql app, like this, it works:

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;

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


You will be better off using something like execute [1] if you are writing the whole sql yourself (which is not really the 'Rails way', but that's a whole other story all together)

[1] http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/DatabaseStatements.html#M001934

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号