How to combine several sets of 'ANDs' and 'ORs' in a complex WHERE query object?

Question

I want to create a complex WHERE query that checks:

If (condition1 = TRUE) AND (condition2 = TRUE) AND (condition3 = TRUE OR condition4 = TRUE)

So the first two conditions MUST always be true and then either condition 3 or 4 MUST be true.

I am trying with the code:

->select ($db->quoteName(array('c.event','b.date','b.type','a.mob','a.joined','a.left')))
->from ($db->quoteName('app_mob_animal', 'a'))
->join('INNER', $db->quoteName('app_event_animal', 'b') . 'ON (' .$db->quoteName('a.mob'). '=' .$db->quoteName('b.mob').')')    
->join('INNER', $db->quoteName('app_events', 'c') . 'ON (' .$db->quoteName('b.event'). '=' .$db->quoteName('c.id').')') 
->join('INNER', $db->quoteName('app_mob', 'd') . 'ON (' .$db->quoteName('a.mob'). '=' .$db->quoteName('d.id').')')
->where (($db->quoteName('a.animal') . 'LIKE' . $db->quote($anid)), 'AND')
->where (($db->quoteName('a.joined') . '<=' . $db->quoteName('b.date')), 'AND') 
->where (($db->quoteName('a.left') . '>=' . $db->quoteName('b.date') .'OR'. ($db->quoteName('a.left') .'LIKE'. $db->quote($emptyDate))));

But when I spit out my responses I am getting results that clearly break the second condition. Here's an except of my results, the second one does not fulfill the criteria.

 [0]=>
  object(stdClass)#891 (6) {
    ["event"]=>
    string(7) "weighed"
    ["date"]=>
    string(19) "2015-04-19 00:00:00"
    ["type"]=>
    string(3) "mob"
    ["mob"]=>
    string(2) "11"
    ["joined"]=>
    string(19) "2015-04-18 11:39:14"
    ["left"]=>
    string(19) "0000-00-00 00:00:00"
  }
  [1]=>
  object(stdClass)#888 (6) {
    ["event"]=>
    string(9) "vet visit"
    ["date"]=>
    string(19) "2015-03-31 11:46:08"
    ["type"]=>
    string(3) "mob"
    ["mob"]=>
    string(2) "11"
    ["joined"]=>
    string(19) "2015-04-18 11:39:14"
    ["left"]=>
    string(19) "0000-00-00 00:00:00"
  }

What is the correct syntax for the WHERE part of this query?

Answer 1

Your OR condition is breaking it. Right now it is.

if condition1 = TRUE AND condition2 = TRUE AND condition3 = TRUE OR condition4 = TRUE

So you don't have () around conditions 3 and 4. This will also give results where only condition4 is true.

Try

->select ($db->quoteName(array('c.event','b.date','b.type','a.mob','a.joined','a.left')))
->from ($db->quoteName('app_mob_animal', 'a'))
->join('INNER', $db->quoteName('app_event_animal', 'b') . ' ON (' .$db->quoteName('a.mob'). '=' .$db->quoteName('b.mob').')')    
->join('INNER', $db->quoteName('app_events', 'c') . ' ON (' .$db->quoteName('b.event'). '=' .$db->quoteName('c.id').')') 
->join('INNER', $db->quoteName('app_mob', 'd') . ' ON (' .$db->quoteName('a.mob'). '=' .$db->quoteName('d.id').')')
->where ($db->quoteName('a.animal') . ' LIKE ' . $db->quote($anid), 'AND')
->where ($db->quoteName('a.joined') . '<=' . $db->quoteName('b.date'), 'AND') 
->where ('('.$db->quoteName('a.left') . '>=' . $db->quoteName('b.date') .' OR '. $db->quoteName('a.left') .' LIKE '. $db->quote($emptyDate).')');

You can print out query to see how it is built.

For example, if your query variable is $query, then do

echo $query->dump(); // Thanks to @DmitryRekun

and it will print full query what gets executed. This way you can see what's wrong with it. Just for simple debugging.

Answer 2

I was going to curb my urge to post after a working solution was accepted, but I later decided that there is value in explaining how the query building process can be altered to improve code readability, intent, brevity, and (to an unnoticeable degree) performance while maintaining security/functionality. I am also going to bake in a few suggestions that are solely a matter of personal coding preference. I just want to say, in advance, that Rene's answer is secure and completely functional.

Here's how I might code up the query and then why:

$anid = "animal's string";  // hypothetical
$emptyDate = "2018-06-21";  // hypothetical
$db = JFactory::getDBO();
try {
    $query = $db->getQuery(true)
                ->select(
                    array(
                        "c.event",
                        "b.date",
                        "b.type",
                        "a.mob",
                        "a.joined",
                        $db->qn("a.left")
                    )
                )
                ->from("app_mob_animal a")
                ->innerJoin("app_event_animal b ON a.mob = b.mob")
                ->innerJoin("app_events c ON b.event = c.id")
                ->innerJoin("app_mob d ON a.mob = d.id")
                ->where(
                    array(
                        "a.joined <= b.date",
                        "a.animal = " . $db->q($anid)
                    )
                )
                ->andWhere(
                    array(
                        $db->qn("a.left") . " >= b.date",
                        $db->qn("a.left") . " = " . $db->q($emptyDate)
                    ),
                    "OR"
                );
    echo $query->dump();
    $db->setQuery($query);
    if (!$result = $db->loadAssocList()) {  // declare variable and check for empty array
        echo "<div>No Qualifying Rows</div>";
    } else {
        foreach ($result as $row) {
            // ... do what you like with $row['event'] etc.  (table names/alias are omitted from the resultset keys)
        }
    }
} catch (Exception $e) {
    echo "<div>Syntax error, please contact the developer</div>";
    // echo $e->getMessage();  // <-- not to be displayed publicly
}

This is the rendered query (from ->dump()) which delivers the intended query logic:

SELECT c.event,b.date,b.type,a.mob,a.joined,`a`.`left`
FROM app_mob_animal a
INNER JOIN app_event_animal b ON a.mob = b.mob
INNER JOIN app_events c ON b.event = c.id
INNER JOIN app_mob d ON a.mob = d.id
WHERE
(a.joined <= b.date AND a.animal = 'animal\'s string') AND
(`a`.`left` >= b.date OR `a`.`left` = '2018-06-19')

Explanations:

• SELECT clause columns are in array form but can just as simply be written as a single string of comma-separated values. Only the left column name NEEDS to be backtick-wrapped -- this can be done safely with hardcoded backticks or with a qn() call.
• To sum up this credible reference, only table names, column names, and aliases (let's call them "database entities") that satisfy one or more of the following conditions MUST be backtick-wrapped:
  1. contains whitespace characters
  2. contains characters outside of this whitelist: Latin letters, digits, underscore, dollarsign
  3. contains MySQL Reserved Keywords.
• Given the above criteria, it is advisable to design your table and column names to contain only Latin letters, digits, underscores, and dollarsign AND avoid using Reserved words. Furthermore, it would clarify coding intent to avoid using the non-reserved words too. In the original post, left is a reserved word. event, date, and type are MySQL keywords, but not reserved. IMO, eliminating function calls that provide no benefit is a good coding habit.
• Writing entities in all lowercase and MySQL words in all uppercase is a terrific way to instantly differentiate between entities and MySQL functions/reserved words.
• I am using qn() as shorthand for quoteName(), q() for quote(), and innerJoin() for join("INNER",...) for brevity.
• I don't like to use multiple where() calls in a single query, because it is too much of a departure from raw query construction. You see, each new innerJoin() writes a new INNER JOIN line, but repeated where() calls just extend the single clause. I am using andWhere() not only for the "inner glue" parameter (OR) but also because it wraps its expressions in parentheses, and forces the initial where() expressions to be wrapped in parentheses to isolate the logical grouping. If you wish to write multiple expressions into a where() (or select()) call, then I recommend feeding it an array() of conditions.
• IMPORTANTLY There are two uses of LIKE in the original query, but they are not being used to perform a partial match. I don't know if this was a coincidence or a misconception about how to compare a column and a string, but this is not best practice and may confuse future researchers. If your logic is to make a full string match, then use the = comparison operator. If your logic is to make a partial match, then use LIKE with a string that includes _ or %. See this guide to Joomla Documentation recommended syntax for quoting a variable in your LIKE expression.
• Finally, if anyone prefers the "syntactic sugar" of AS between tables/columns and their respective aliases, by all means use it.