Rails SQL 查询生成器...或 ActiveRecord 查询生成器 [英] Rails SQL query builder... Or ActiveRecord query builder

问题描述

我需要像

sql = 'SELECT * FROM users WHERE id != ' + self.id.to_s + ' AND id NOT IN (SELECT artner_id FROM encounters WHERE user_id = ' + self.id.to_s + ')'
sql += ' AND id NOT IN (SELECT user_id FROM encounters WHERE partner_id = ' + self.id.to_s + ' AND predisposition = ' + Encounter::Negative.to_s + ')'
sql += ' AND cfg_sex = ' + self.sex.to_s + ' AND cfg_country = ' + self.country.to_s + ' AND cfg_city = ' + self.city.to_s
sql += ' ORDER BY rand() LIMIT 1'

可以通过 AR.find_by_sql 执行，但是之前的代码可读性差.是否有任何查询构建器可以构建该查询?

It can be executed by AR.find_by_sql, but the code before is bad readable. Are there any query builder, which can build that query?

例如，Kohana(它是 PHP 框架，我是 php 开发人员，但我想将那种儿童语言更改为 ruby​​/rails)有一个查询构建器，其工作方式如下:

For example, Kohana (it is PHP framework, I am php developer, but I want to change that kid-language to ruby/rails) have a query builder, which works like this:

$sql = DB::select('*')->from('users');
$sql->where('id', 'NOT_IN', DB::expr('SELECT partner_id FROM encounters WHERE user_id = '.$user->id));
$sql->where('id', 'NOT_IN', DB::expr('SELECT user_id FROM encounters WHERE partner_id = '.$user->id.' AND predisposition = '.Encounter::Negative));
....
etc
...

使用 Kohana 查询构建器等查询构建器构建的查询更具可读性和可理解性.

Query which was builded with query builder like a Kohana query builder is more readable and understandable.

有什么 gem 可以解决这个问题吗?

Are there any gem to solve this problem?

推荐答案

你需要 squeel gem.它用块扩展 AR，并轻松进行非常复杂的查询.

You need the squeel gem. It extends AR with blocks and makes very complicated queries with ease.

只有几个功能:

# not_in == cool! )
Product.where{id.not_in LineItem.select{product_id}}
# SELECT "products".* FROM "products" WHERE "products"."id" NOT IN 
# (SELECT "line_items"."product_id" FROM "line_items" )

# outer joins on pure Ruby:
LineItem.joins{product.outer}
# LineItem Load (0.0ms)  SELECT "line_items".* FROM "line_items" 
# LEFT OUTER JOIN "products" ON "products"."id" = "line_items"."product_id"

# calcs, aliasing:
Product.select{[avg(price).as(middle)]}
# SELECT avg("products"."price") AS middle FROM "products"

# comparison
Product.where{id != 100500}
Product.where{price<10}

# logical OR
Product.where{(price<10) | (title.like '%rails%')}
# SELECT "products".* FROM "products" WHERE (("products"."price" < 10 OR
# "products"."title" LIKE '%rails%'))

# xxx_any feature (also available xxx_all)
Product.where{title.like_any %w[%ruby% %rails%]}
# SELECT "products".* FROM "products" WHERE (("products"."title" LIKE '%ruby%' OR 
# "products"."title" LIKE '%rails%'))    

注意 using 块:这里的 {...} 不是哈希.还要注意没有符号.

Note the using blocks: {...} here aren't hashes. Also note the absence of symbols.

如果您决定选择它，请阅读以这具有重要意义"开头的部分

这篇关于Rails SQL 查询生成器...或 ActiveRecord 查询生成器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！