How to group combined AND with OR statements using the Sequel GEM in Ruby?

I am fairly good at PHP (OOP & flat). And for the past year or so I have been tasked with maintaining a Ruby codebase; a skill I am still learning. I am not too clear on how to properly structure a filtering chain using Sequel so AND as well OR statements can be properly contained.

This is the MySQL query structure I want to have:

SELECT * FROM `some_objects`
WHERE (
  (
    ((`datebegin` >= 1950) AND (`datebegin` <= 1959)) OR ((`dateend` >= 1950) AND (`dateend` <= 1959))
  )
  OR
  ((`datebegin` <= 1950) AND (`dateend` >= 1959))
  AND
  (NOT `datebegin` = 0) AND (NOT `dateend` = 0)
)
;

And this is the Sequel code fragment I am using:

some_objects = where{((datebegin >= start_year) & (datebegin <= end_year)) | ((dateend >= start_year) & (dateend <= end_year))}.
               or{(datebegin <= start_year) & (dateend >= end_year)}.
               where(~:datebegin => 0, ~:dateend => 0)

And this is what I am actually getting:

SELECT * FROM `some_objects`
WHERE (
  (
    ((`datebegin` >= 1950) AND (`datebegin` <= 1959)) OR ((`dateend` >= 1950) AND (`dateend` <= 1959))
    OR
    ((`datebegin` <= 1950) AND (`dateend` >= 1959))
  )
  AND
  (NOT `datebegin` = 0) AND (NOT `dateend` = 0)
)
;

I have also tried different variations on the same Sequel code such as this:

some_objects = where(:datebegin => start_year..end_year).
               or(:dateend => start_year..end_year).
               or{|o|(o.datebegin <= start_year) & (o.dateend >= end_year)}.
               where(~:datebegin => 0, ~:dateend => 0)

And this:

some_objects = where(:datebegin => start_year..end_year).
               or(:dateend => start_year..end_year).
               or{(datebegin <= start_year) & (dateend >= end_year)}.
               where(~:datebegin => 0, ~:dateend => 0)

But I still end up with the first SQL structure where this whole chunk is basically ((AND OR AND OR)):

(
  ((`datebegin` >= 1950) AND (`datebegin` <= 1959)) OR ((`dateend` >= 1950) AND (`dateend` <= 1959))
  OR
  ((`datebegin` <= 1950) AND (`dateend` >= 1959))
)

When I want ((AND OR AND) OR):

(
  ((`datebegin` >= 1950) AND (`datebegin` <= 1959)) OR ((`dateend` >= 1950) AND (`dateend` <= 1959))
)
OR
((`datebegin` <= 1950) AND (`dateend` >= 1959))

Solution

The issue with your initial query is that you are depending on the OR/AND precedence rules instead of using explicit parentheses. Your initial query could be expressed as:

SELECT * FROM `some_objects`
WHERE (
  ((`datebegin` >= 1950) AND (`datebegin` <= 1959))
  OR
  ((`dateend` >= 1950) AND (`dateend` <= 1959))
  OR
  ((`datebegin` <= 1950) AND (`dateend` >= 1959) AND (NOT `datebegin` = 0) AND (NOT `dateend` = 0))
)

You could express this in Sequel as:

DB[:some_objects].where{((datebegin >= start_year) & (datebegin <= end_year)) | ((dateend >= start_year) & (dateend <= end_year))}.
  or{((datebegin <= start_year) & (dateend >= end_year)) & Sequel.negate(:datebegin => 0)}

Which yields the following SQL:

SELECT * FROM `some_objects` WHERE (
  ((`datebegin` >= 1950) AND (`datebegin` <= 1959))
  OR
  ((`dateend` >= 1950) AND (`dateend` <= 1959))
  OR
  ((`datebegin` <= 1950) AND (`dateend` >= 1959) AND (`datebegin` != 0))
)

This should perform the same as the SQL you used, but it is a bit more readable (e.g. datebegin != 0 instead of NOT datebegin = 0). Note that you don't need the NOT dateend = 0 filter, because it is a subset of the dateend >= 1959 filter.