Build crazy queries with ActiveRecord and Arel

Today I have had to fix a rather complex query. Turned into plain old SQL it rendered as :

SELECT products.* FROM products
WHERE EXISTS (
  SELECT prices.* FROM prices
  WHERE prices.country_code IN ['FR', 'DE'] AND 
        prices.customer_price > 0 AND
        current_date BETWEEN(
          COALESCE(prices.valid_from, 'yesterday'::DATE) 
      and COALESCE(prices.valid_to, 'tomorrow'::DATE)
        ) AND
        prices.product_id = products.id
)

The goal here is to display products when they have, at least, an active (valid at current_date), positive customer_price.

The model Price looks like:

class Price < ApplicationRecord
  belongs_to :product
  scope :active, -> {
    where <<~SQL
      current_date BETWEEN 
        coalesce("prices".valid_from, 'yesterday'::DATE) 
    AND coalesce("prices".valid_to, 'tomorrow'::DATE)
    SQL
  }
  scope :defined, ->(country, level) {
    where(country_code: countries).
    where("\"prices\".#{level.to_s}_price > 0").
    active
  }
end

And the model Product resembles:

class Product < ApplicationRecord
  has_many :prices
  scope :with_active_price, ->(company, country, level) {
    countries = [ country, company.default_country ]
    where( 
      Price.defined(countries, level)
           .where('"prices".product_id = "products".id')
           .exists 
    )
  }
end

The query at top can now be generated using ActiveRecord using:

Product.with_active_price(Company.find(1), ‘FR’, :customer)

But today my customer found that it cannot deactivate a product by setting its Price attributes to 0 when a Price exists for the Company#default_country (because countries are mixed in a single array).

The query able to fix that problem is more complex :

SELECT "products".* FROM "products"
WHERE EXISTS(
  SELECT * FROM "prices"
  WHERE "prices"."country_code" = 'FR' AND
        "prices"."customer_price" > 0 AND
        current_date BETWEEN 
          COALESCE("prices"."valid_from", 'yesterday'::DATE) 
      AND COALESCE("prices"."valid_to", 'tomorrow'::DATE) AND
        "prices"."product_id" = "products"."id"
  ) OR (
    NOT EXISTS(
  SELECT * FROM "prices"
  WHERE "prices"."country_code" = 'FR' AND
         current_date BETWEEN 
           COALESCE("prices"."valid_from", 'yesterday'::DATE) 
       AND COALESCE("prices"."valid_to", 'tomorrow'::DATE) AND
         "prices"."product_id" = "products"."id"
    ) AND EXISTS(
  SELECT * FROM "prices"
  WHERE "prices"."country_code" = 'DE' AND    
        "prices"."distributor_price" > 0 AND                 
        current_date BETWEEN 
          COALESCE("prices"."valid_from", 'yesterday'::DATE) 
      AND COALESCE("prices"."valid_to", 'tomorrow'::DATE) AND
        "prices"."product_id" = "products"."id"
  )
)

… and a bit… hard, if not impossible to write using pure ActiveRecord, so I gave a try to Arel. Given that the only source of examples of complex use of Arel is the ActiveRecord source code himself, I think that my discoveries may help.

The game here is to merge 3 similar subqueries using OR and AND.

Something like:

SELECT * FROM products WHERE q1 OR (q2 AND q3)

To generate these subqueries we need a reference to the Price “Arel Table”:

price = Price.arel_table

Then, we should rewrite the BETWEEN clause using Arel so we will be able to reuse it :

valid_from = Arel::Nodes::NamedFunction.new('COALESCE', [
  price[:valid_from], 
  Arel.sql("'yesterday'::DATE")
])
valid_to = Arel::Nodes::NamedFunction.new('COALESCE', [ 
  price[:valid_to], 
  Arel.sql("'tomorrow'::DATE")
])
between = Arel::Nodes::Between.new(
  Arel.sql('current_date'), 
  valid_from.and(valid_to)
)

Easy, no? Raw SQL is escaped using Arel.sql(), references to fields are obtained using arel_table#[]. Between is already an Arel::Nodes::Node but SQL function COALESCE is not defined however we can easily wrap it within an Arel::Nodes::NamedFunction.

Then we rewrite the 3 subqueries (arel_table[:id] refers to “self” which is the Product class) :

select = Arel::SelectManager.new(
  Price.arel_engine, Price.arel_table
)
q1 = select.where(price[:country_code].eq(country_code))
           .where(price["#{price_level}_price"].gt(0))
           .where(between)
           .where(price[:product_id].eq(arel_table[:id]))
           .exists
select = Arel::SelectManager.new(
  Price.arel_engine, Price.arel_table
)
q2 = select.where(price[:country_code].eq(country_code))
           .where(between)
           .where(price[:product_id].eq(arel_table[:id]))
           .exists.not
select = Arel::SelectManager.new(
  Price.arel_engine, Price.arel_table
)
q3 = select.where(price[:country_code].
                    eq(company.default_country))
           .where(price["#{price_level}_price"].gt(0))
           .where(between)
           .where(price[:product_id].eq(arel_table[:id]))
           .exists

(You are right, I should refactor theses blocs into a builder method/class…) and finally we can mix these 3 queries together using OR and ANDs:

where(q1.or(q2.and(q3)))

The key here was to use Arel::SelectManager (like ActiveRecord do!) to be able to transform the final subqueries into “[NOT] EXISTS()” clauses.

What about ActiveRecord ? Why was it so complex to fix original code ?

Calling “exists” on an ActiveRecord::Relation transforms that relation into an Arel AST, so that *should* do the trick:

scope :with_active_price, ->(company, country, level) {
  q1 = Price.defined(countries, level)
            .where('"prices".product_id = "products".id')
            .exists 
  q2 = Price.not_defined(countries)
            .where('"prices".product_id = "products".id')
            .exists.not
  q3 = Price.defined(company.default_country, 
                     price_level)
            .where('"prices".product_id = "products".id')
            .exists
  where(q1.or(q2.and(q3)))
}

But that don’t work because params assigned in Price.defined() scope are not retained into the Arel AST and when the query is executed, it misses these params.

Because the error message looks furiously like https://github.com/rails/rails/issues/20077 I checked if I could give a try to the trick consisting in transforming subqueries to SQL and assembles them as a string at the end.

scope :with_active_price, ->(company, country, level) {
  q1 = Price.defined(countries, level)
            .where('"prices".product_id = "products".id')
            .exists
            .to_sql
  q2 = Price.not_defined(countries)
            .where('"prices".product_id = "products".id')
            .exists.not
            .to_sql
  q3 = Price.defined(company.default_country, 
                     price_level)
            .where('"prices".product_id = "products".id')
            .exists
            .to_sql
  where("{q1} or (#{q2} and #{q3})")
}

I get this error (translated from French):

PG::UndefinedFunction (ERROR:  operator does not exists : character varying = integer
LINE 1: ...es".* FROM "prices" WHERE "prices"."country_code" = $1 AND (...
                                                             ^
HINT:  No operator correspond to the given name and argument types.
You should add explicit type conversions.

I finally found a way by transforming intermediate subqueries and build EXISTS () or (NOT EXISTS () and EXISTS()) manually.

scope :with_active_price, ->(company, country, level) {
  q1 = Price.defined(countries, level)
            .where('"prices".product_id = "products".id')
            .to_sql
  q2 = Price.not_defined(countries)
            .where('"prices".product_id = "products".id')
            .to_sql
  q3 = Price.defined(company.default_country, 
                     price_level)
            .where('"prices".product_id = "products".id')
            .to_sql
  where("EXISTS (#{q1}) or (NOT EXISTS(#{q2}) and EXISTS(#{q3}))")
}

Finally I think this way the code is more compact and maybe more explicit.