ruby-on-rails - 如何在 ActiveRecord 中编写集合操作查询而不在 SQL 中编写 INTERSECT、UNION 或 EXCEPT？

Question

我有一个用于管理志愿者的 Rails 应用程序，我正在寻找“Rails 方式”来编写复杂的 SQL 查询以过滤所有志愿者记录。我有以下模型:

class Volunteer < ActiveRecord::Base
  has_many :volunteer_skills
  has_many :skills, through: :volunteer_skills
  has_many :volunteer_lists
  has_many :lists, through: :volunteer_lists
end

class VolunteerSkill < ActiveRecord::Base
  belongs_to :volunteer
  belongs_to :skill
end

class VolunteerList < ActiveRecord::Base
  belongs_to :volunteer
  belongs_to :list
end

如果@lists 是List ID 的数组，而@skills 是Skill ID 的数组

1. 我想在没有任何@skills 的情况下找到任何@lists 上的所有志愿者。
2. 我想在任何 @lists 中找到所有志愿者，但不包含所有 @skills。
3. 我想在任何 @lists 中找到所有 @skills 中的所有志愿者。
4. 我想在所有 @skills 中找到任何 @lists 上的所有志愿者。

看到这个之后StackOverflow question ，我使用 .find_by_sql 创建了这个问题的解决方案。当查询涉及寻找具有所有 @skills 的志愿者时，我构建了一个 INTERSECT 查询。当查询涉及寻找具有任何 @skills 的志愿者时，我构建了一个 UNION 查询。当查询涉及在没有 @skills 的情况下寻找志愿者时，我使用 EXCEPT 格式化查询。

不幸的是，这个解决方案不如使用 ActiveRecord 的解决方案友好。我对编程还很陌生，但我要解决的问题似乎相当简单/常见。是否有更好的解决方案利用 ActiveRecord？

我非常感谢任何想法!

Answer 1

您可以使用 ActiveRecord 完成大部分工作，尽管您仍然需要使用一些 SQL 片段。

首先，为任何 @lists 上的所有志愿者创建一个范围:

class Volunteer < ActiveRecord::Base
  scope :on_any_list -> lists { joins(:volunteer_list).where(volunteer_id: lists) }
end

然后为每个技能标准添加条件。

拥有任何@skills 的志愿者都很容易:

on_any_list(@lists).joins(:volunteer_skills).where(skill_id: @skills).distinct

对于拥有所有@skills的志愿者，您需要构建一个查询:

query = on_any_list(@lists)
@skills.each do |skill|
  query = query.where("exists (select 1 from volunteer_skills vs " +
                      "where vs.volunteer_id = volunteers.id and skill_id = ?)", skill)
end

不幸的是，ActiveRecord 的not 不允许您构造上述任何一个的相反查询，因此您需要从头开始执行缺乏技能的查询。

没有所有@skills的志愿者:

on_any_list(@lists).
  where("not exists (select 1 from volunteer_skills vs " +
        "where vs.volunteer_id = volunteers.id and skill_id in (?))", @skills)

最后，最棘手的是，没有任何@skills 的志愿者:

on_any_list(@lists).
  joins("left join volunteer_skills vs on volunteers.id = vs.volunteer_id").
  where(skill_id: @skills)
  where("volunteer_skills.id is null").
  distinct

(有 an AREL way to do the outer join ，但我不认为它更容易。)

编写和理解其中每一项所需的不同技术让我想知道，从整体上看，它们是否真的比您的 SQL 版本更好。至少 on_any_lists 部分运行良好。