How to dynamic, handle nested WHERE AND/OR queries using Rails and SQL

Question

I'm currently building a feature that requires me to loop over an hash, and for each key in the hash, dynamically modify an SQL query.

The actual SQL query should look something like this:

select * from space_dates d
    inner join space_prices p on p.space_date_id = d.id
    where d.space_id = ?
    and d.date between ? and ?
    and (
        (p.price_type = 'monthly' and p.price_cents <> 9360) or
        (p.price_type = 'daily' and p.price_cents <> 66198) or
        (p.price_type = 'hourly' and p.price_cents <> 66198) # This part should be added in dynamically
    )

The last and query is to be added dynamically, as you can see, I basically need only one of the conditions to be true but not all.

query = space.dates
      .joins(:price)
      .where('date between ? and ?', start_date, end_date)

# We are looping over the rails enum (hash) and getting the key for each key value pair, alongside the index

SpacePrice.price_types.each_with_index do |(price_type, _), index|
  amount_cents = space.send("#{price_type}_price").price_cents
  query = if index.positive? # It's not the first item so we want to chain it as an 'OR'
            query.or(
              space.dates
               .joins(:price)
               .where('space_prices.price_type = ?', price_type)
               .where('space_prices.price_cents <> ?', amount_cents)
             )
           else
             query # It's the first item, chain it as an and
               .where('space_prices.price_type = ?', price_type)
               .where('space_prices.price_cents <> ?', amount_cents)
           end
end

The output of this in rails is:

SELECT "space_dates".* FROM "space_dates"
  INNER JOIN "space_prices" ON "space_prices"."space_date_id" = "space_dates"."id"
  WHERE "space_dates"."space_id" = $1 AND (
   (
     (date between '2020-06-11' and '2020-06-11') AND
     (space_prices.price_type = 'hourly') AND (space_prices.price_cents <> 9360) OR
     (space_prices.price_type = 'daily') AND (space_prices.price_cents <> 66198)) OR
     (space_prices.price_type = 'monthly') AND (space_prices.price_cents <> 5500)
   ) LIMIT $2 

Which isn't as expected. I need to wrap the last few lines in another set of round brackets in order to produce the same output. I'm not sure how to go about this using ActiveRecord.

It's not possible for me to use find_by_sql since this would be dynamically generated SQL too.

Answer 1

So, I managed to solve this in about an hour using Arel with rails

 dt = SpaceDate.arel_table
 pt = SpacePrice.arel_table

 combined_clauses = SpacePrice.price_types.map do |price_type, _|
   amount_cents = space.send("#{price_type}_price").price_cents
   pt[:price_type]
     .eq(price_type)
     .and(pt[:price_cents].not_eq(amount_cents))
 end.reduce(&:or)

  space.dates
    .joins(:price)
    .where(dt[:date].between(start_date..end_date).and(combined_clauses))
  end

And the SQL output is:

SELECT "space_dates".* FROM "space_dates"
INNER JOIN "space_prices" ON "space_prices"."space_date_id" = "space_dates"."id" 
WHERE "space_dates"."space_id" = $1
AND "space_dates"."date" BETWEEN '2020-06-11' AND '2020-06-15'
AND (
  ("space_prices"."price_type" = 'hourly'
   AND "space_prices"."price_cents" != 9360
   OR "space_prices"."price_type" = 'daily'
   AND "space_prices"."price_cents" != 66198)
  OR "space_prices"."price_type" = 'monthly'
  AND "space_prices"."price_cents" != 5500
) LIMIT $2

What I ended up doing was:

1. Creating an array of clauses based on the enum key and the price_cents
2. Reduced the clauses and joined them using or
3. Added this to the main query with an and operator and the combined_clauses