ActiveRecord 和 Arel (SQL) 的过滤链问题

Question

这是设置：

class Title
has_many :managed_rights
end

class ManagedRight
belongs_to :title
has_many :managed_right_countries
enumerize :territory_rights, in: [:all, :include, :exclude], default: :all
end

class ManagedRightCountry
belongs_to :managed_right
belongs_to :country
end

class Country; end

我想按国家/地区过滤标题，其中country_ids = [3, 5, 8, 9].

• 所有案例 如果title.managed_rights[0].territory_rights == :all选择此标题
• 如果包括大小写 仅当任何managed_right_countries在country_ids中时才选择，否则跳过。

title.managed_rights[0].territory_rights == :include 
AND title.managed_rights[0].managed_right_countries IN country_ids

• 如果排除大小写 选择所有标题，managed_right_countries中的任何标题在country_ids中
。

title.managed_rights[0].territory_rights == :exclude
AND title.managed_rights[0].managed_right_countries ALL NOT IN country_ids

这就是我对 Arel 尝试过的：

managed_rights = ManagedRight.arel_table
managed_right_countries = ManagedRightCountry.arel_table
Title.left_outer_joins(managed_rights: :managed_right_countries)
.where(
# some other filters not related to "managed_rights"
managed_rights[:territory_rights].eq(:all)
.or(
managed_rights[:territory_rights].eq(:include)
.and(managed_right_countries[:country_id].in(country_ids))
.or(
managed_rights[:territory_rights].eq(:exclude)
.and(managed_right_countries[:country_id].not_in(country_ids))
)
)
).distinct

它适用于大多数情况，除非managed_right_countries.country_id有一些 ID 不在country_ids数组中。上述查询的含义:exclude仅在所有国家/地区都在数组中时才有效。 但是，如果数组中有任何国家/地区，我需要从结果中排除标题。

我怎样才能做到它正常工作？

最好使用AREL，但是如果无法仅使用Arel，则原始SQL(PostgreSQL(也可以工作。

例：

title_01 has `managed_rights[0].territory_rights = :all`
title_02 has `managed_rights[0].territory_rights = :include`
and `managed_rights[0].managed_right_countries = [1, 2]`
title_03 has `managed_rights[0].territory_rights = :exclude`
and `managed_rights[0].managed_right_countries = [2, 3]`

• 按国家/地区 [1] 过滤时，应选择所有 3 个标题(title_02包含 1 个，不排除title_03(
• 当按国家 [2] 只选择title_01和title_02时(因为不包括三个三分之一(
• 当按国家 [3] 时，只应选择title_01，因为 3 不包括在title_02，不包括在title_03
• 如果按空国家/地区 [] 过滤，则应显示所有三个国家/地区

Answer 1

通过创建嵌套子查询来解决。

SQL 解决方案：

SELECT titles.* FROM titles
LEFT OUTER JOIN managed_rights ON managed_rights.title_id = titles.id
LEFT OUTER JOIN managed_right_countries ON managed_right_countries.managed_right_id = managed_rights.id
WHERE (titles.rights = 'all' OR managed_rights.title_id is NULL)
OR managed_rights.territory_rights = 'all'
OR (
managed_rights.territory_rights = 'include' AND managed_right_countries.country_id in (country_ids)
)
OR (
managed_rights.territory_rights = 'exclude' 
AND NOT titles.id in (
SELECT titles.id FROM titles
LEFT OUTER JOIN managed_rights ON managed_rights.title_id = titles.id
LEFT OUTER JOIN managed_right_countries ON managed_right_countries.managed_right_id = managed_rights.id
WHERE managed_rights.territory_rights = 'exclude' 
AND managed_right_countries.country_id in (country_ids)
)
)

阿雷尔解决方案：

titles = Title.arel_table
managed_rights = ManagedRight.arel_table
managed_right_countries = ManagedRightCountry.arel_table
excluded_title_ids = titles.project('titles.id')
.join(managed_rights, Arel::Nodes::OuterJoin)
.on(managed_rights[:title_id].eq(titles[:id]))
.join(managed_right_countries, Arel::Nodes::OuterJoin)
.on(managed_right_countries[:managed_right_id].eq(managed_rights[:id]))
.where(
managed_rights[:territory_rights].eq(:exclude)
.and(managed_right_countries[:country_id].in(available_countries))
)
Title.left_outer_joins(managed_rights: :managed_right_countries)
.where(
managed_rights[:territory_rights].eq(:all)
.or(
managed_rights[:territory_rights].eq(:include)
.and(managed_right_countries[:country_id].in(available_countries))
.or(
managed_rights[:territory_rights].eq(:exclude)
.and(titles[:id].not_in(excluded_title_ids))
)
)
)