Grouping ands and ors in AREL

Question

I'm trying to query the equivalent of this sql snippet using arel:

WHERE (("participants"."accepted" = 'f' AND "participants"."contact_id" = 1) 
  OR "participants"."id" IS NULL)

So I want (accepted && contact_id=1) OR NULL

Here's what I've got in AREL

participants[:accepted].eq(false).and(participants[:contact_id].eq(1).
  or(participants[:id].is(nil)

Problem is, this generates:

("participants"."accepted" = 'f' AND "participants"."contact_id" = 1 OR "participants"."id" IS NULL)

Note the lack of parentheses around my and conditions. I believe that according to the operator precedence, that I'm getting:

accepted && (contact_id=1 OR NULL)

Adding parentheses in in the AREL query has no affect. Any thoughts?

Raney

Answer 1

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))

Transference

That's pretty much exactly what Arel gives you. How is it too low-level? – Grumous

Squeel looks great, though (I hadn't known about it). But calling it less low-level than Arel isn't really accurate -- it's just a syntax modification. – Grumous

Given the context, it is not clear to me that "low level" is meant to refer to the library as a whole -- it looks like it's more meant to refer to the internals that Aaron was writing. – Grumous

And no, "low level" and "high level" are not subjective, at least as relative terms. You said Squeel was higher level than Arel. So far as I can tell, it is just a syntax mod of Arel, and therefore works on the exact same level. The exception would be sifters, which abstract Arel and therefore work at a higher level. – Grumous

mmm... ya you're right. I was actually trying to translate a LEFT OUTER JOIN participants written by hand to use includes(:participants). Problem is I have additional conditions on my join. So I was hoping I could just tack them into a where but they don't see to be equivalent. Have to modify my question – Raney

ugh, I'm doubly messing up here, the reason I thought this was failing was because my previously written tests were failing, (the actual query is a bit more complex) But it was failing because I refactored != in the sql to .eq in the arel... oops. – Raney

thx for the wrapper suggestion btw! I actually like working with ARel, I find I can do more complex queries than ActiveRecord allows for, that wrapper syntax is kind of nice though, I generally just use arel_table – Raney

Answer 2

You can generate parentheses using Arel::Nodes::Grouping.

participants = Arel::Table.new("participants")

arel = participants.grouping(
  participants[:accepted].eq(false).and(participants[:contact_id].eq(1))
).or(participants[:id].eq(nil))

arel.to_sql # => (("participants"."accepted" = 'f' AND "participants"."contact_id" = 1) OR "participants"."id" IS NULL)

Drying

Thanks, .grouping worked great. Instead of participants = Arel::Table.new("participants"), I used participants = Participant.arel_table – Stomatic

This solution works elegantly and solves the issue at hand. This would be my preferred answer to the problem. Great options all around though :-) – Botanize

Answer 3

Why not flip them around. Should be equivalent to:

participants[:id].is(nil).or(participants[:accepted].eq(false).and(participants[:contact_id].eq(1))

hopefully I've got the parens properly set in the above, but you see what I mean...

Asquint

Ya I think I was wrong about needing this, Tokland is right, the precedence is not my problem, it's actually that my query is different than what I need. – Raney

Answer 4

Here's a method that allows you to use existing AR methods such as where and perhaps any existing scopes, etc in your code. So it's easy to reuse existing AR code with Arel as needed.

class Participant
  scope :grouped, -> {
    group1 = arel_table.grouping(where(accepted: false, contact_id: 1).arel.constraints)
    group2 = arel_table.grouping(where(id: nil).arel.constraints)

    where(group1.or(group2))
  }
end

This works in Rails 7, and perhaps Rails 6.x as well.

Biauriculate