Rails/Arel - precedence when combining AREL predicates

Question

A model has two attributes of interest, forename and town. I want to search for Richard (London) and Brian (Madrid).

In long-hand,

p=Person.scoped
pred1=p.table[:forename].eq('Richard').and(p.table[:town].eq('London'))
pred2=p.table[:forename].eq('Brian').and(p.table[:town].eq('Madrid'))
pred3=pred1.or(pred2)

I would expect this to wrap the predicates in parentheses to maintain the integrity of the query. But looking at pred3.to_sql gives an unexpected response:

"(`person`.`forename` = 'Richard' AND `person`.`town` = 'London' OR `person`.`forename` = 'Brian' AND `person`.`town` = 'Madrid')"

How can I have Arel generate the correct query?

Did you ever find a solution to this? I'm having the same problem and the suggested answers don't work. — Adam Gotterer, Dec 12 '12 at 22:45

score 2 · Accepted Answer · answered Jun 06 '12 at 14:21

This actually is the correct query, as AND has higher operator precedence than OR in most SQL dialects. If you were to flip it and AND 2 OR predicates, it would wrap the OR predicates in paren.

t = Arel::Table.new('blah')

a = (t[:a].eq(nil).and(t[:b].eq(nil)))
b = (t[:a].not_eq(nil).and(t[:b].not_eq(nil)))
a.or(b).to_sql

  => "(`blah`.`a` IS NULL AND `blah`.`b` IS NULL OR `blah`.`a` IS NOT NULL AND `blah`.`b` IS NOT NULL)"

a = (t[:a].eq(nil).or(t[:b].eq(nil)))
b = (t[:a].not_eq(nil).or(t[:b].not_eq(nil)))
a.and(b).to_sql

  => "(`blah`.`a` IS NULL OR `blah`.`b` IS NULL) AND (`blah`.`a` IS NOT NULL OR `blah`.`b` IS NOT NULL)"

score 1 · Answer 2 · answered May 13 '13 at 09:39

For Rails 3.2.13, use Arel::Nodes::Grouping. Also see FactoryMethods#grouping-instance_method RubyDoc.

pt = Person.arel_table
pred1 = person_table[:forename].eq("Richard").and(person_table[:town].eq("London"))
pred2 = person_table[:forename].eq("Rick").and(person_table[:town].eq("US"))
pt.grouping(pred1).or(pt.grouping(pred2)).to_sql

=> (("people"."forename" = 'Richard' AND "people"."town" = 'London') OR ("people"."forename" = 'Rick' AND "people"."town" = 'US'))

score 0 · Answer 3 · answered Jul 01 '11 at 07:39

Try using arel_table directly

person_table = Person.arel_table
pred1 = person_table[:forename].eq("Richard").and(person_table[:town].eq("London"))
pred2 = person_table[:forename].eq("Rick").and(person_table[:town].eq("US"))
pred1.or(pred2).to_sql

This yields (("people"."forename" = 'Richard' AND "people"."town" = 'London') OR ("people"."forename" = 'Rick' AND "people"."town" = 'US'))

Rails/Arel - precedence when combining AREL predicates

3 Answers3

Linked