sql - Express SQL UNION查询Rails方式

sql - Express SQL UNION查询Rails方式,第1张

我得到的查询效果很好,但用SQL表示。我想使用ActiveRecord查询接口表示相同的查询(Arel也会很好)。该查询最好返回ActiveRecord :: Relation,或者至少它的结果应该可以转换为Customer模型数组。

目标是获取company's customers没有关联import_logsremote_type = 'account',以及customers import_logremote_type = 'account' {1}}和status = 'pending'

customer根本没有关联import_logs,或者import_log每个remote_type,或仅remote_types import_log。只有一个关联的remote_type具有特定的customer值。

这反映了要求account可以contactimport_log或两者导入,import_log跟踪导入状态。

虽然customerCustomer.find_by_sql( <<-SQL SELECT customers.* FROM customers WHERE company_id = #{@company.id} AND NOT EXISTS ( SELECT * FROM import_logs WHERE import_logs.importable_id = customers.id AND import_logs.importable_type = 'Customer' AND import_logs.remote_type = 'account' ) UNION SELECT customers.* FROM customers, import_logs WHERE import_logs.importable_id = customers.id AND import_logs.importable_type = 'Customer' AND company_id = #{@company.id} AND import_logs.remote_type = 'account' AND import_logs.status = 'pending'; SQL ) 具有多态关联,但这与任务无关。

现有查询:

create_table "import_logs", force: true do |t|
  t.integer  "importable_id"
  t.string   "importable_type"
  t.string   "status",          default: "pending", null: false
  t.string   "remote_type"
  ...
end

add_index "import_logs", ["importable_id", "importable_type", "remote_type"], unique: true ...

class ImportLog < ActiveRecord::Base
  ...
  belongs_to :importable, polymorphic: true
  ...
end

ImportLog模型的相关部分:

create_table "customers", force: true do |t|
  t.integer  "company_id"
  ...
end

class Customer < ActiveRecord::Base
  ...
  belongs_to :company
  has_many :import_logs, as: :importable
  ...
end

客户模型的相关部分:

class Company < ActiveRecord::Base
  ...
  has_many :customers
  ...
end

和公司模型一样,以防万一:

{{1}}

最佳答案:

2 个答案:

答案 0 :(得分:1)

merge个关联

实际上,只有一个由查询常量驱动的关联。

"customers"."company_id" = #{@company.id}

它与:

相同
.merge(@company.customers)

......这看起来更安全,更明智

Arel表

我们很快就会需要它。

customers = Customer.arel_table

NOT EXISTS ...子查询

Arel可以做到这一点,唯一不那么明显的是如何引用外部表:

ne_subquery = ImportLog.where(
                importable_type: Customer.to_s,
                  importable_id: customers[:id],
                    remote_type: 'account'
              ).exists.not

这会产生一大块Arel AST,我们可以提供给Rails&#39; where - 声明

现在两个查询都变得很明显了:

first  = @company.customers.where(ne_subquery)
second = @company.customers.joins(:import_logs).merge(
           ImportLog.where(
           # importable_id: customers[:id], # `joins` already does it
           importable_type: Customer.to_s,
               remote_type: 'acoount',
                    status: 'pending'
           )
         )

这几乎是一对一的转换。

联盟

这是一个棘手的部分,我发现的唯一解决方案具有非常难看的语法并输出一些不同的查询。鉴于A union B,我们只能构建select X.* from (A union B) X。效果是一样的。

好的,让我们来看看:

Customer.from(
  customers.create_table_alias(
    first.union(second),
    Customer.table_name
  )
)

当然,为了使这个查询更具可读性,你应该:

  • 将其作为范围放在Customer class
  • 将可重复使用的部分拆分为范围和关联

答案 1 :(得分:0)

根据@ D-side建议的代码,我能够找到一个有效的解决方案。这是最初建议的代码:

customers = Customer.arel_table

ne_subquery = ImportLog.where(
  importable_type: Customer.to_s,
  importable_id: customers['id'],
  remote_type: 'account'
).exists.not

first  = @company.customers.where(ne_subquery)
second = @company.customers.joins(:import_logs).merge(
  ImportLog.where(
    importable_type: Customer.to_s,
    remote_type: 'account',
    status: 'pending'
  )
)

Customer.from(
  customers.create_table_alias(
    first.union(second),
    Customer.table_name
  )
)

运行它会导致此错误:

PG::ProtocolViolation: ERROR:  bind message supplies 0 parameters, but prepared statement "" requires 1

: SELECT "customers".* FROM ( SELECT "customers".* FROM "customers"  WHERE "customers"."company_id" =  \
AND (NOT (EXISTS (SELECT "import_logs".* FROM "import_logs"  WHERE "import_logs"."importable_type" = 'Customer' \
AND "import_logs"."importable_id" = "customers"."id"))) UNION SELECT "customers".* FROM "customers" \
INNER JOIN "import_logs" ON "import_logs"."importable_id" = "customers"."id" \
AND "import_logs"."importable_type" = 'Customer' WHERE "customers"."company_id" =  \
AND "import_logs"."importable_type" = 'Customer' AND "import_logs"."remote_type" = 'contact' \
AND "import_logs"."status" = 'pending' ) "customers"

我认为这个错误是Rails issue #20077的一种表现形式,此时尚未解决。由于该问题与参数绑定有关,因此使该绑定更明确有帮助。这是一个有效的解决方案:

customers = Customer.arel_table

ne_subquery = ImportLog.where(
  importable_type: Customer.to_s,
  importable_id: customers['id'],
  remote_type: 'account'
).exists.not

first  = Customer.where(ne_subquery).where(company_id: @company.id)
second = Customer.joins(:import_logs).merge(
  ImportLog.where(
    importable_type: Customer.to_s,
    remote_type: 'account',
    status: 'pending'
  )
).where(company_id: @company.id)

Customer.from(
  customers.create_table_alias(
    first.union(second),
    Customer.table_name
  )
)

请注意,.where(company_id: @company.id)已明确应用,firstsecond查询开始无范围。

本文经用户投稿或网站收集转载,如有侵权请联系本站。

发表评论

0条回复