sql - 如何使用 Rails 和 SQL 动态处理嵌套的 WHERE AND/OR 查询

Question

我目前正在构建一个功能，该功能需要我遍历一个散列，并且对于散列中的每个键，动态修改一个 SQL 查询。

实际的 SQL 查询应如下所示：

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
    )

最后一个and查询是动态添加的，如你所见，我基本上只需要一个条件为真，但不是全部。

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

这个在rails中的输出是：

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 

这并不像预期的那样。我需要将最后几行包裹在另一组圆括号中，以产生相同的输出。我不确定如何使用 ActiveRecord 来解决这个问题。

我无法使用它，find_by_sql因为这也是动态生成的 SQL。

标签： sqlruby-on-railsrubyactiverecordarel

Answer 1

所以，我设法在大约一个小时内使用 Arel 和 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

SQL 输出为：

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

我最终做的是：

1. 根据枚举键和price_cents
2. 减少子句并使用or
3. 将此添加到带有and运算符的主查询和combined_clauses