ruby-on-rails - 如何编写Activerecord/Arel交集查询

Question

我在 PSQL 中得到了这个:

SELECT "profiles".id FROM "profiles"
INNER JOIN "integration_profiles"
ON "integration_profiles"."profile_id" = "profiles"."id"
INNER JOIN "integrations"
ON "integrations"."id" = "integration_profiles"."integration_id"
WHERE "integrations"."provider" = 'csv'
INTERSECT
SELECT "profiles".id FROM "profiles"
INNER JOIN "integration_profiles"
ON "integration_profiles"."profile_id" = "profiles"."id"
INNER JOIN "integrations"
ON "integrations"."id" = "integration_profiles"."integration_id"
WHERE "integrations"."provider" = 'kickstarter'

我想将其转换为 ActiveRecord 查询或 Arel。

我试过:

Arel::Nodes::Intersect.new(
  Profile.joins(:integrations).where(integrations: { provider: 'csv' }),
  Profile.joins(:integrations).where(integrations: { provider: 'kickstarter' })
)

[Debug]   Profile Load (408.2ms)  SELECT "profiles".* FROM "profiles" INNER JOIN "integration_profiles" ON "integration_profiles"."profile_id" = "profiles"."id" INNER JOIN "integrations" ON "integrations"."id" = "integration_profiles"."integration_id" WHERE "integrations"."provider" = 'csv' (pid:65570)
 [Debug]   Profile Load (1.9ms)  SELECT "profiles".* FROM "profiles" INNER JOIN "integration_profiles" ON "integration_profiles"."profile_id" = "profiles"."id" INNER JOIN "integrations" ON "integrations"."id" = "integration_profiles"."integration_id" WHERE "integrations"."provider" = 'kickstarter' (pid:65570)

这个有效:

ps = (
  Profile.select(:id)
    .joins(:integrations)
    .where(integrations: { provider: 'csv' })
    .intersect(
      Profile.select(:id)
        .joins(:integrations)
        .where(integrations: {provider: 'kickstarter'})
    )
)
Profile.from(Profile.arel_table.create_table_alias(ps, :profiles))

有没有办法一步搞定？或者一种让 ps 输出结果的方法，而不是它只输出一个 Arel 对象...

Answer 1

您可以像这样加入 2 次 integration_profiles 和集成:

Profile
  .joins("INNER JOIN integration_profiles AS ip_csv ON ip_csv.profile_id = profiles.id")
  .joins("INNER JOIN integrations AS i_csv ON i_csv.id = ip_csv.integration_id")
  .joins("INNER JOIN integration_profiles AS ip_kickstarter ON ip_kickstarter.profile_id = profiles.id")
  .joins("INNER JOIN integrations AS i_kickstarter ON i_kickstarter.id = ip_kickstarter.integration_id")
  .where("i_csv.provider='csv' AND i_kickstarter.provider='kickstarter'")
  .pluck("profiles.id")

---

如果您想要更具可读性的查询，请像这样定义两个范围:

class Profile < ActiveRecord::Base
  has_many :integration_profiles
  has_many :integrations, through: :integration_profiles

  scope :csv,         ->{joins(:integrations).where(integrations: { provider: 'csv' })}
  scope :kickstarter, ->{joins(:integrations).where(integrations: { provider: 'kickstarter' })}
end

然后:

Profile.csv.where(id: Profile.kickstarter.pluck(:id) ).pluck(:id)

您最终会得到 2 个查询，但这样读起来好多了。

SELECT "profiles"."id" FROM "profiles"
INNER JOIN "integration_profiles" ON "integration_profiles"."profile_id" = "profiles"."id" 
INNER JOIN "integrations" ON "integrations"."id" = "integration_profiles"."integration_id" 
WHERE "integrations"."provider" = 'kickstarter'

SELECT "profiles"."id" FROM "profiles"
INNER JOIN "integration_profiles" ON "integration_profiles"."profile_id" = "profiles"."id" 
INNER JOIN "integrations" ON "integrations"."id" = "integration_profiles"."integration_id" 
WHERE "integrations"."provider" = 'csv' AND "profiles"."id" IN (1, 3)

---

如果您只想要配置文件的 ID，您可以采用另一条路径:从 integration_profiles 中提取 profile_id:

class Integration < ActiveRecord::Base
  has_many :integration_profiles
  has_many :profiles, through: :integration_profiles

  scope :csv,         -> { where(provider: 'csv') }
  scope :kickstarter, -> { where(provider: 'kickstarter') }
end

class IntegrationProfile < ActiveRecord::Base
  belongs_to :integration
  belongs_to :profile
end

IntegrationProfile.joins(:integration).csv.where(
  profile_id: IntegrationProfile.joins(:integration).kickstarter.pluck(:profile_id)
).pluck(:profile_id)

这仍然会生成 2 个查询，但这些更简单:

SELECT "integration_profiles"."profile_id" FROM "integration_profiles"
INNER JOIN "integrations" ON "integrations"."id" = "integration_profiles"."integration_id" 
WHERE "integrations"."provider" = 'kickstarter'

SELECT "integration_profiles"."profile_id" FROM "integration_profiles"
INNER JOIN "integrations" ON "integrations"."id" = "integration_profiles"."integration_id" 
WHERE "integrations"."provider" = 'csv' AND "integration_profiles"."profile_id" IN (1, 3)