Rails ActiveRecord WHERE EXISTS 查询

Question

我有一个 SQL 查询可以返回我需要的结果，但是我在将其转换为“Rails方式”的活动记录查询时遇到了麻烦。

我的 SQL 查询如下：

SELECT * from trips 
WHERE trips.title LIKE "%Thailand%"
AND EXISTS (SELECT * from places WHERE places.trip_id = trips.id AND places.name LIKE "%Bangkok%")
AND EXISTS (SELECT * from places WHERE places.trip_id = trips.id AND places.name LIKE "%Phuket%")

我正在尝试使用Rails做类似这样的事情：

@trips=Trip.where("trips.title LIKE ?", "%Thailand%")
@trips=@trips.includes(:places).where(("places.name LIKE ?","%Bangkok%").exists?) => true, ("places.name LIKE ?","%Phuket%").exists?) => true)

但是似乎没有起作用，我不知道该尝试什么。

- Josh Wood

---

你使用的Rails版本是哪个？ - rdnewman

我正在使用Rails 4.2.1。 - Josh Wood

我正在为您准备一份更长的答案，但由于您在SQL中使用了LIKE，您可能想查看http://stackoverflow.com/questions/30042146/make-search-not-case-sensitive-on-my-rails-app/30042240#30042240，因为`LIKE`在不同的数据库中的行为是不同的（尽管这不是您在此查询中遇到问题的原因 - 只是一个快速的警告）。 - rdnewman

请查看 https://github.com/Casecommons/pg_search 宝石。 - blnc

Answer 1

RAILS 5/6 编辑：从https://github.com/rails/rails/pull/29619开始，Rails 开始不鼓励直接调用原始答案中的.exists。我已经更新了使用新语法的内容，该语法首先调用arel代理(.arel.exists)。此外，从 Rails 5 开始，在 EXISTS 子句中使用哈希条件也可以正常工作。

考虑到这一切，纯 ActiveRecord 方法现在是：

Trip.where("trips.title LIKE ?", "%Thailand%")
    .where( Place.where('trip_id = trips.id AND name LIKE ?', '%Bangkok%').arel.exists )
    .where( Place.where('trip_id = trips.id AND name LIKE ?', '%Phuket%').arel.exists )

如果这看起来有一点可怕，您还有其他选择：

• 您可以直接在应用程序中嵌入SQL语句，如.where('EXISTS(SELECT 1 FROM places WHERE trip_id = trips.id AND name LIKE ?', '%Bangkok%')。这不太时髦或酷，但从长远来看它可能更易于维护 - 它极不可能被弃用或在未来版本的Rails中停止工作。
• 选择一个gem，例如activerecord_where_assoc，它会使语法更清晰，并且可以防止您犯简单的错误（例如未正确限定您的EXISTS查询，就像我的原始答案所做的那样）。

- Robert Nubel

---

这是最接近我原始尝试的解决方案，看起来也是最简单的解决方案。你提到的警告并不影响我，因为我只需要使用LIKE，它需要字面字符串（据我所知）。 - Josh Wood

这个答案实际上似乎是错误的，因为生成的 SQL 语句中缺少原始查询中的 places.trip_id = trips.id EXISTS，并且有一个随机不必要的 JOIN。因此，它似乎只是检查具有这些名称的地点是否存在，并且 Trip 是否与任何地点链接而没有条件。这是使用 gem 的另一个原因 :) - Maxime Lapointe

@MaximeLapointe 谢谢你指出这一点 - 我很惊讶它花了将近五年的时间！我已经更新了答案以反映Rails的当前行为，并且还包括了关于你的gem的说明。 - Robert Nubel

Answer 2

使用Where Exists宝石（公正声明：我是它的作者）：

Trip.where("trips.title LIKE ?", "%Thailand%")
  .where_exists(:places, ['name LIKE ?', '%Bangkok%'])
  .where_exists(:places, ['name LIKE ?', '%Phuket%'])

- EugZol

---

不知道这个存在，看起来很不错！ - Edgar Ortega

我看到你做了什么 :) - Rimian

Answer 3

这是我对第一次尝试进行了重构，结果更接近您的预期目标（对于第一个误导性的版本，我表示歉意）。

在app/models/trip.rb中：

scope :with_title, ->(title) { where(arel_table[:title].matches('%' + title + '%')) }

def self.has_place(place_name)
  _trips = Trip.arel_table
  _places = Place.arel_table
  where(Place.joins(:trip).where(_places[:name].eq(place_name)).exists)
end

然后您可以编写以下内容：

Trip.for_title('Thailand').has_place('Bangkok').has_place('Phuket')

这将生成以下SQL：

SELECT "trips".* FROM "trips" WHERE ("trips"."title" LIKE '%Thailand%') AND (EXISTS (SELECT "places".* FROM "places" INNER JOIN "trips" ON "trips"."id" = "places"."trip_id" WHERE "places"."name" = 'Bangkok')) AND (EXISTS (SELECT "places".* FROM "places" INNER JOIN "trips" ON "trips"."id" = "places"."trip_id" WHERE "places"."name" = 'Phuket'))

这样做可以让你在组合查询时具有灵活性，而不必依赖于硬编码的SQL查询，并且在数据库引擎之间是可移植的。

此解决方案假定Place belongs_to Trip（和它的补集）。如果关联不同，请调整joins子句。

此解决方案部分受到How to do "where exists" in Arel 和 Make search NOT case sensitive on my rails app 的启发。

- rdnewman

Answer 4

不使用 gem 会更容易出现错误。目前接受的答案（截至撰写本文）是手动完成的，实际上并没有生成正确的 SQL。（已经修复，但这突显了错误的风险。）

存在另一个可用于此的 gem：activerecord_where_assoc（我是作者）

使用它，您可以这样做：

Trip.where("trips.title LIKE ?", "%Thailand%")
    .where_assoc_exists(:places, ['name LIKE ?', '%Bangkok%'])
    .where_assoc_exists(:places, ['name LIKE ?', '%Phuket%'])

activerecord_where_assoc 更加强大，有完整文档，并且对Rails 4.2到6.0进行了CI测试。这里是介绍。

- Maxime Lapointe