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