Grouping AND and OR clauses with Arel

Question

I am trying to build a scope in my rails model that, when invoked, would give me a set of nested AND and OR clauses on 7 boolean fields. Here's an example with simplified column names for clarity:

SELECT * FROM mytable
WHERE (a AND b AND c) OR (a AND d AND e) OR (a AND f AND g);

Note that column a is present in all three clauses. Another way to write it would be:

SELECT * FROM mytable
WHERE a AND ((b AND c) OR (d AND e) OR (f AND g));

Arel seems less forgiving on the second form. I've gotten really close with the follwing scope:

scope :needs_any_delivery, lambda {
  table = self.arel_table
  common_assert = table[:a].eq(true)
  where(
    common_assert.and(
      table[:b].eq(true).and(
        table[:c].eq(false)
      )
    ).or(
      common_assert.and(
        table[:d].eq(true).and(
          table[:e].eq(false)
        )
      ).or(
        common_assert.and(
          table[:f].eq(true).and(
            table[:g].eq(false)
          )
        )
      )
    )
  )
}

This produces the following query:

SELECT * FROM mytable
WHERE (
  (a = 't' AND b = 't' AND c = 'f'
    OR (a = 't' AND d = 't' AND e = 'f' OR a = 't' AND f = 't' AND g = 'f')
  )
)

It's close, but the third AND group is not being separated from the second AND group. I've found that if I put some additional bogus or clause onto the end of the third group then Arel appropriately groups the third clause on its own... but that seems like a hack.

Wondering if any rails/arel gurus out there have any ideas. Thanks!

score 4 · Answer 1 · answered Sep 10 '15 at 18:46

If you're like me and really want to continue to use Arel for this functionality, I've found that creating a new or method seems like the best route.

I added a new initializer called arel_fixed_or.rb with the following content:

Arel::Nodes::Node.class_eval do

  ###
  # Factory method to create a Nodes:Or that has a Nodes::Grouping
  # node as a child.
  def fixed_or right
    Arel::Nodes::Or.new self, Arel::Nodes::Grouping.new(right)
  end

end

And wherever you would normally want to use an or statement, you can go ahead and use fixed_or which will then append an or-ed grouped statement onto the end of your clause, which to me should be the expected method. Feel free to remove the Arel::Nodes::Grouping portion if this doesn't make sense to you.

One thing to note is that you may still have to manually place groupings in order for the correct elements to be surrounded by brackets. For example:

table = Arel::Table.new(:mytable)
common_assert = table[:a].eq(true)

first_or = table[:b].eq(true).and(table[:c].eq(true))
second_or = table[:d].eq(true).and(table[:e].eq(true))
third_or = table[:f].eq(true).and(table[:g].eq(true))

common_assert.and(
  table.grouping(
    table.grouping(first_or)
      .fixed_or(second_or)
      .fixed_or(third_or)
  )
)

And its to_sql output becomes:

"mytable"."a" = 't' AND (
  ("mytable"."b" = 't' AND "mytable"."c" = 't') OR
  ("mytable"."d" = 't' AND "mytable"."e" = 't') OR
  ("mytable"."f" = 't' AND "mytable"."g" = 't')
)

score 1 · Accepted Answer · answered Feb 23 '13 at 07:10

Unless I'm reading wrong, this might be easier using something like active_record_or rather than using arel directly.

Using that gem, you should be able to get the right result doing something like:

common_assert = where(a: true) # WHERE a
option_one = where(b: true).where(c: true) # b AND c
option_two = where(d: true).where(e: true) # d AND e
option_three = where(f: true).where(g: true) # f AND g
combined_optionals = option_one.or.option_two.or.option_three # (b AND c) OR (d AND e) OR (f AND g)
common_assert.merge(combined_optionals) # WHERE a AND ((b AND c) OR (d AND e) OR (f AND g))

Interesting. I'm not sure I will be using anything other than Arel in this case, but thanks for the response and answer! — localshred, Feb 25 '13 at 15:50

score 0 · Answer 3 · answered Apr 16 '18 at 16:27

You could also something like

def dnf(clauses)
  clauses
    .map { |clause| clause.reduce(:and) }
    .reduce(:or)
end

table = Arel::Table.new(:some_fancy_table)
table[:a].eq(true).and dnf [
  [table[:b].eq(true), table[:c].eq(false)],
  [table[:d].eq(true), table[:e].eq(false)],
  [table[:f].eq(true), table[:g].eq(false)],
]

Also, I don't think that you actually need the parenthesis around and-connected sub-clauses, see SQL Logic Operator Precedence: And and Or

Grouping AND and OR clauses with Arel

3 Answers3