How to Properly Add Brackets to SQL Queries with 'Or' and 'And' Clauses by Using Arel

How to properly add brackets to SQL queries with 'or' and 'and' clauses by using Arel?

I've successfully used this gem: squeel which comes on top of Arel so you don't have to mess with it. So in order to generate your query you would do something like this in Squeel:

@articles = Article.
  where{
  ( user_id.eq(1) | status.eq('published') ) |
  ( user_id.in([10, 11, 12, '<...>']) & status.eq('temp') )
}

# since this is an ActiveRecord::Relation we can play around with it
@articles = @articles.select{ [ user_id, status ] }

# and you can also inspect your SQL to see what is going to come out
puts @articles.to_sql

The more complicated your queries get the more you're going to like this gem.

How to control bracket position on ActiveRecord query with `OR` clause

You can get something similar, just without the main parentheses wrapping the where clause conditions:

User.where('foo IS NULL')
    .or(User.where('10 > 20'))
    .merge(User.where(bar: nil).or(User.where('10 > 1')))
# SELECT "users".*
# FROM "users"
# WHERE ((foo IS NULL) OR (10 > 20)) AND ("users"."bar" IS NULL OR (10 > 1))

Consider foo equals 1 and bar equals 2.

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

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

Grouping ands and ors in AREL

I believe that according to the operator precedence

The problem is that AND has higher precedence than OR. So 1 AND 2 OR 3 is equivalent to (1 AND 2) OR 3.

As a side note: if you use a wrapper like this one, you can write:

User.where((User[:created_at] > 3.days.ago) & (User[:enabled] == true))

Cannot programmatically combine AND and OR conditions using Arel

Arel::Nodes::Grouping wraps nodes in parentheses.

Arel::Nodes::Grouping.new(Arel::Nodes::And.new("blah", "there")).to_sql
# => ('blah' AND 'there')

How to use AREL when using multiple databases?

table = Arel::Table.new("db_name.table_name")

Is it possible to generate SQL `OR` clauses by using scope methods?

We have been discussing this on the squeel issue tracker and the basic answer seems to be no. At least not with scopes; but if you convert the scopes into sifters you can.

---

Related Topics