Rails SQL 查询构建器...或者ActiveRecord查询构建器

Question

我需要运行类似于以下的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', 'NOT_IN', DB::expr('SELECT user_id FROM encounters WHERE partner_id = '.$user->id.' AND predisposition = '.Encounter::Negative));
....
etc
...

使用像 Kohana 查询构建器这样的查询方式更易读及理解。

有没有类似的 gem 可以解决这个问题呢？

- Mark Pegasov

Answer 1

你需要使用 squeel gem。它通过块扩展了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%'))    

请注意使用的块：{...} 这里不是散列。还要注意符号的缺失。 如果您决定选择它，请阅读以“这带有一个重要的含义”开头的部分。

- jdoe

Answer 2

有一个使用关系代数的 Ruby 库，它被称为 ARel。如果您正在使用 Rails 3.x，则已经拥有了。

ids   = Partner.where(user_id: self.id).pluck(:partner_id) << self.id
users = User.where("id NOT IN #{ ids.join(',') }")

- oldhomemovie

---

它能否将数据库表达式粘贴到查询中，以构建像“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 + ')'这样的查询？ - Mark Pegasov

在这种情况下为什么不执行左连接？ - Omar Qureshi

Answer 3

以下是将同一查询转换为Rails AREL术语的代码。目前它不够简洁，毕竟这是一个复杂的查询。

User.where("id = ? AND "
           "id NOT IN (SELECT artner_id FROM encounters WHERE user_id = ?) AND " +  
           "id NOT IN (SELECT user_id FROM encounters WHERE partner_id = ? AND predisposition = ? ) AND " + 
            "cfg_sex = ? AND cfg_country = ? AND cfg_city = ?)", 
            self.id, self.id, self.id, Encounter::Negative, 
            self.sex, self.country, self.city).order(" rand() ").limit(1)

我没有测试过这个代码，所以可能存在拼写错误。

我建议您做几件事：

当您有复杂的where子句时，可以将它们链接在一起，AREL通常会很好地将它们组合在一起。这使您可以在模型类中使用作用域并将它们链接在一起。

例如，您可以这样做：

class User < ActiveRecord::Base
  def self.in_city_state_country(city, state, country)
    where("cfg_sex = ? AND cfg_country = ? AND cfg_city = ?", city, state, country)
  end

  def self.is_of_sex(sex)
    where("cfg_sex = ?", sex)
  end
end

那么你可以这样重写查询的这些部分：

User.is_of_sex(user.sex).in_city_state_country(user.city, user.state, user.country)

等等等。

将查询分解成较小的部分也使得您可以更轻松地使用rspec测试其中的特定部分。这会导致更模块化、可维护的代码。

有关更多详细信息，请查看Rails Guide - Active Record Query Interface。

- Kevin Bedell