问题:Express SQL UNION查询Rails方式

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

目标是获取company's``customers与remote_type = 'account'没有关联的import_logs,以及customers具有import_log与remote_type = 'account'和status = 'pending'。

一个customer可以根本没有关联的import_logs,或者每个remote_type都有一个import_log,或者只针对一些remote_types。只能有一个关联的import_log与特定的remote_type值。

这反映了一个要求,customer可以作为account或contact或两者导入,并且import_log跟踪导入的状态。

虽然import_log与customer具有多态关联,但这与任务无关。

现有查询:

Customer.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
)

ImportLog 模型的相关部分:

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

客户模型的相关部分:

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

解答

merge关联

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

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

它与以下内容相同:

.merge(@company.customers)

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

阿雷尔表

我们很快就会需要它。

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 的where-statement。

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

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类中

• 将可重用部分拆分为范围和关联