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

问题描述

我需要运行像

sql查询

  SQL =SELECT * FROM用户WHERE ID！='+ self.id.to_s +'和ID NOT IN（SELECT artner_id从交锋WHERE user_ID的='+ self.id. to_s +'）'
SQL + ='和ID NOT IN（SELECT user_ID的FROM遭遇WHERE partner_id ='+ self.id.to_s +'和predisposition ='+遭遇:: Negative.to_s +'）'
SQL + ='AND cfg_sex ='+ self.sex.to_s +和cfg_country ='+ self.country.to_s +和cfg_city ='+ self.city.to_s
SQL + ='ORDER BY兰特（）限制1'
 

它可以通过AR.find_by_sql执行，但code之前是坏的可读性。 是否有任何查询生成器，它可以构建查询？

例如，Kohana的（这是PHP框架，我是PHP开发人员，但我想改变孩子语言红宝石/导轨）有一个查询生成器，它的工作原理是这样的：

  $ SQL = DB ::选择（*） -  GT;从（用户）;
$ SQL-化合物其中（'身份证'，'NOT_IN'，DB :: EXPR（SELECT partner_id从交锋WHERE user_ID的='$用户自＆GT; ID））;
$ SQL-化合物其中（'身份证'，'NOT_IN'，DB :: expr的（'选择USER_ID从交锋WHERE partner_id ='$用户自＆GT; ID和predisposition ='.Encounter ::负））;
....
等等
...
 

查询这是建造与查询生成器像一个Kohana的查询生成器是更易于阅读和理解。<​​/ P>

是否有任何宝石来解决这个问题？

解决方案

您需要 squeel 的宝石。它的扩展 AR积木，使非常复杂的查询提供方便。

只需几个特点：

 ＃not_in ==爽！ ）
Product.where {id.not_in LineItem.select {PRODUCT_ID}}
＃选择产品。*从产品WHERE产品，IDNOT IN
＃（SELECTline_items。的product_idFROMline_items）

＃外连接的纯Ruby：
LineItem.joins {product.outer}
＃LineItem的负载（0.0ms）选择line_items。* FROMline_items
＃LEFT OUTER JOIN产品ON产品，ID=line_items。PRODUCT_ID

＃Calcs（计算），别名：
Product.select {[AVG（价格）。至于（中）]}
＃SELECT AVG（产品。价格）为中从产品

＃对比
Product.where {ID！= 100500}
Product.where {价格小于10}

＃逻辑或
Product.where {（价格小于10）| （title.like'％铁轨％'）}
＃选择产品*从产品WHERE（（产品，价格＆LT;第10位
＃产品，标题LIKE'％铁轨％'））

＃xxx_any功能（也可xxx_all）
Product.where {title.like_any％W [％红宝石％％护栏％]}
＃选择产品。*从产品WHERE（（产品，标题LIKE'％红宝石％'OR
＃产品，标题LIKE'％铁轨％'））
 

请注意在使用块： {...} 这里不是哈希。还要注意没有符号。

如果你决定把它捡起，请阅读这个承载着一个重要的启示开头的部分

I need to run sql query like

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'

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?

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

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

Are there any gem to solve this problem?

解决方案

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

Just few features:

# 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%'))    

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

If you decide to pick it, read the section that starts with "This carries with it an important implication"

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