如何写的ActiveRecord /阿雷尔路口查询 [英] How to write Activerecord/Arel intersection query

问题描述

我得到了这个工作在PSQL：

I got this working in 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的查询或阿雷尔。

I'd like to convert it to ActiveRecord query or 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)

的这工作：的

This works:

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 输出的结果，而不是它只是输出阿雷尔对象...

Is there a way to get it done in one step? Or a way to get the ps to output the results instead it just outputs an Arel object...

推荐答案

您可以参加2次integration_profiles和集成这样的：

You can join 2 times integration_profiles and integrations like this:

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")

---

如果你想要一个更可读的查询，这样定义两个范围：

---

If you want a more readable query, define two scopes like this:

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查询，但该读好多了。

You will end up with 2 queries but this reads much better.

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：

---

If you want only the ids for profiles you can take another path: extract profile_id from integration_profiles:

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的查询，但这些都是简单的：

This will still generate 2 queries, but those are simpler:

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)

这篇关于如何写的ActiveRecord /阿雷尔路口查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！