ruby-on-rails – Rails SQL查询构建器…或ActiveRecord查询构建

发布时间：2020-12-16 20:46:48 所属栏目：百科来源：网络整理

导读：我需要像运行sql查询一样 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_

我需要像运行sql查询一样

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执行,但之前的代码可读性差.
是否有任何查询构建器,可以构建该查询？

例如,Kohana(它是PHP框架,我是php开发人员,但我想将这种孩子语言改为ruby / rails)有一个查询构建器,其工作方式如下：

$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',DB::expr('SELECT user_id FROM encounters WHERE partner_id = '.$user->id.' AND predisposition = '.Encounter::Negative));
....
etc
...

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

有什么宝石可以解决这个问题吗？

解决方法

你需要 squeel宝石.它使用块扩展AR,并且可以轻松地进行非常复杂的查询.

只有少数功能：

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

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

如果你决定选择它,请阅读以“这带来一个重要的含义”开头的部分.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!