I need to get a selection of users with an SQL query like this:
sql =<<~SQL
token IS NULL OR
(
token = 'some_token'
AND (
state = 'cancelled' AND created_at > ?
OR state = 'submitted'
)
)
SQL
User.where(sql, Time.now)
I don't like to have SQL in my queries — all hidden code in scopes is now pops up, and it is hard to compose.
But could I rewrite this query with ActiveRecord? Let's try:
User
.where(token: nil)
.or(
User.where(token: 'some_token').and(
User.where(state: 'cancelled')
.or(User.where('created_at > ?', Time.now))
)
)
Looks better, but does it generate the same SQL?
SELECT "users".*
FROM "users"
WHERE ("users"."token" IS NULL
OR "users"."token" = 'some_token'
AND ("users"."state" = 'cancelled'
OR created_at > '2024-04-16 10:46:43.129109'))
Hm, where do the parentheses after IS NULL OR go? Doesn't it look like a different condition?
Tests are passing, but if ActiveRecord omits parentheses - wouldn't it be problematic in some cases?
For example, if we have an expression like this a || (b && (c || d)) - would it be the same if there were no parentheses, like a || b && c || d?
We are programmers in the modern era, so let's ask ChatGPT.
ChatGPT answer:
So it seems all is okay. But let's (just for the sake of the experiment) check manually too:
booleans = [true, false]
combinations = booleans.product(booleans, booleans, booleans)
combinations.each do |a, b, c, d|
expression1 = a && (b || (c && d))
expression2 = a && b || c && d
if expression1 != expression2
puts "a: #{a}, b: #{b}, c: #{c}, Expression1: #{expression1}, Expression2: #{expression2}"
end
end
=> a: false, b: true, c: true, Expression1: false, Expression2: true
=> a: false, b: false, c: true, Expression1: false, Expression2: true
So, it's actually not. This robotic liar!
Okay, a && (b || (c && d)) and a && b || c && d are not equivalent.
Let's check another one:
combinations = booleans.product(booleans, booleans, booleans, booleans,
booleans, booleans, booleans, booleans, booleans)
combinations.each do |a, b, c, d, e, f, g, i, k|
expression1 = a && b || (c && d || (e && f || (g && i || k)))
expression2 = a && b || c && d || e && f || g && i || k
if expression1 != expression2
puts "a: #{a}, b: #{b}, c: #{c}, Expression1: #{expression1}, Expression2: #{expression2}"
end
end
These are the same! But how? So many groups we have!
How's that? It's because of logical operators' precedence: logical AND evaluates before logical OR.
So in a || b && c, it will first evaluate b && c, and then ||. We could see it like this: a || (b && c).
And it's the same in most languages - in Ruby and PostgreSQL for sure.
To make it easier to understand, we could rewrite expressions as multiplication and addition: because logical AND with binary values works exactly like normal arithmetic multiplication, and logical OR works in many senses as arithmetic addition.
E.g. a + (b * c) is the same as a + b * c.
And in our expressions:
# these are not the same
expression1 = a * (b + (c * d))
expression2 = a * b + c * d
# these are the same
expression3 = a * b + (c * d + (e * f + (g * i + k)))
expression4 = a * b + c * d + e * f + g * i + k
Now it's starting to make sense - ActiveRecord is in its right to omit parentheses in our SQL example, as it is similar to expression3. And it's actually an optimization to send less data over the network.
But let's check, does it work correctly with expressions like a && (b || (c && d))?
User
.where(token: nil)
.and(
User.where(token: 'some_token')
.or(
User.where(state: 'cancelled')
.where('created_at > ?', Time.now)
)
)
SELECT "users".*
FROM "users"
WHERE "users"."token" IS NULL
AND ("users"."token" = 'some_token'
OR "users"."state" = 'cancelled'
AND (created_at > '2024-04-16 11:15:36.584382'))
Good - parentheses after AND are in their place!
And if we rewrite OR to AND?
User
.where(token: nil)
.and(
User.where(token: 'some_token')
.and(
User.where(state: 'cancelled')
.where('created_at > ?', Time.now)
)
)
SELECT "users".*
FROM "users"
WHERE "users"."token" IS NULL
AND "users"."token" = 'some_token'
AND "users"."state" = 'cancelled'
AND (created_at > '2024-04-16 11:20:36.296399')
It omits all parentheses—and rightfully so.
Okay, but we could use not only the .or method but also OR directly, like .where("... OR ..."). Does ActiveRecord handle this?
This is easy — .where(sql) always wraps the expression inside it in parentheses:
User.where(token: 'some_token')
.where("state = 'cancelled' OR created_at > ?", Time.now)
SELECT "users".*
FROM "users"
WHERE "users"."token" = 'some_token'
AND (state = 'cancelled'
OR created_at > '2024-04-16 11:23:00.877269')
Even if it's only one condition
User.where(token: 'some_token')
.where("state = 'cancelled'").to_sql
SELECT "users".*
FROM "users"
WHERE "users"."token" = 'some_token'
AND (state = 'cancelled')
So, it seems all is good, and we could use our nice and clean ActiveRecord! Nice!
References:
Top comments (0)