Clever Geek Handbook

SQL Express Query UNION Rails

I have a query that works well but is expressed in SQL. I would like to have the same query expressed using the ActiveRecord Query Interface (Arel would do fine too). The request should preferably return ActiveRecord :: Relation, or at least its result should be convertible to an array of Customer models.

The goal is to get company's

customers

unrelated import_logs

with remote_type = 'account'

, as well customers

as import_log

with remote_type = 'account'

and status = 'pending'

.

A customer

may not have at all, import_logs

or have import_log

for each, remote_type

or only for some remote_types

. There can only be one associated import_log

with a specific meaning remote_type

.

This reflects the requirement that a customer

can be imported like account

or contact

or both, and import_log

tracked the import status.

Although it import_log

has a polymorphic relationship to customer

, this is not relevant to the task.

Existing request:

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
)

Relevant parts of the ImportLog model:

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

Relevant parts of the client model:

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

And the company's models, just in case:

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


source to share


2 answers


merge

associations

In fact, there is only one association, which is driven by a query constant.

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

This is the same as:

.merge(@company.customers)

... and it looks a little safer and smarter

Arel tables

We need it soon.

customers = Customer.arel_table

NOT EXISTS ...

subquery

Arel can do this, the only less obvious thing is how to reference an external table:



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

This leads to the fact that Arel AST can submit ' where

-statement to Rails .

Now both questions become obvious:

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

This is pretty much a 1 to 1 conversion.

Union

This is the tricky part and the only solution I have found has an awfully ugly syntax and outputs a bit different query. Given A union B

, we can only build select X.* from (A union B) X

. The effect is the same.

Okay, let it get to him:

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

Of course, to make this request more readable, you must:

  • Place it as scope in a class Customer

  • Divide the reusable parts into areas and associations.
+1


source


Based on the code suggested by @ D-side, I was able to reach a working solution. Here is the originally suggested code:

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

Doing so results in this error:

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

: SELECT "customers".* FROM ( SELECT "customers".* FROM "customers"  WHERE "customers"."company_id" = $1 \
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" = $1 \
AND "import_logs"."importable_type" = 'Customer' AND "import_logs"."remote_type" = 'contact' \
AND "import_logs"."status" = 'pending' ) "customers"


This error, I believe, is an expression of Rails issue # 20077 , which is currently unresolved. Since the problem is with parameter binding, this makes the binding more explicit. Here's a working solution:

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

Note that .where(company_id: @company.id)

apply explicitly, and requests first

and second

start without registration.

0


source






More articles:


All Articles