Combining 'ANDs' and 'ORs' in a complex WHERE query - Part 2

Question

I have been pulling my hair out with this query for the last 2 days and have read and re-read the 2 other similar posts with no luck.

Scenario: I am gathering two inputs from a user: Test ID Code and email address they used when submitting the test. The test code is numbers and can have a letter in the beginning, but the testing facility may leave this number out so I add the letter to a variable in the query (reason for "newtidcode" you will see below). They can also provide up to 4 email addresses to the tester when submitting (reason for me testing on 4 email possibilities).

Here is my query:

    $query->select($db->quoteName(array('tidcode', 'cdcode','email1', 'email2', 'email3', 'email4', 'status')))
        ->from($db->quoteName('#__tid_codes'))
        ->where($db->quoteName('tidcode').' = '.$db->quote($tidcode), 'OR')
        ->where($db->quoteName('tidcode').' = '.$db->quote($newtidcode), 'AND')
        ->where('('.$db->quoteName('email1').' = '.$db->quote($email).' OR '.$db->quoteName('email2').' = '.$db->quote($email).' OR '.$db->quoteName('email3').' = '.$db->quote($email).' OR '.$db->quoteName('email4').' = '.$db->quote($email).')');

Here the SQL Statement created:

SELECT `tidcode`,`cdcode`,`email1`,`email2`,`email3`,`email4`,`status` FROM `#__tid_codes` WHERE `tidcode` = '10348' OR `tidcode` = 'F10348' OR (`email1` = '[email protected]' OR `email2` = '[email protected]' OR `email3` = '[email protected]' OR `email4` = '[email protected]')

As you can see the OR before the email should be an AND as specified in the 2nd "where" statement. I was even trying to use conditions arrays like below and still ran into the same thing.

$conditions1 = array(
    $db->quoteName('tidcode')." = ".$db->quote($tidcode),
    $db->quoteName('tidcode')." = ".$db->quote($newtidcode)
);
$conditions2 = array (
    $db->quoteName('email1')." = ".$db->quote($email),
    $db->quoteName('email2')." = ".$db->quote($email),
    $db->quoteName('email3')." = ".$db->quote($email),
    $db->quoteName('email4')." = ".$db->quote($email)
);

This is my first foray in JDatabase querying to this complexity. I have this application working fine in standard HTML and PHP, but trying to port over to Joomla. Any help is highly appreciated.

Answer 1

The second parameter of where, called $glue, does not work as you might expect.

This is the source code of the where function:

public function where($conditions, $glue = 'AND')
{
    if (is_null($this->where))
    {
        $glue = strtoupper($glue);
        $this->where = new JDatabaseQueryElement('WHERE', $conditions, " $glue ");
    }
    else
    {
        $this->where->append($conditions);
    }
    return $this;
}

You can see that it only takes the $glue of the first "WHERE" you add to your query.

I found some more information about this issue here, but the information there may be outdated.

Solution

Keep your 2 variables, $conditions1 and $conditions2 and then build your query like this:

$query->select($db->quoteName(array('tidcode', 'cdcode','email1', 'email2', 'email3', 'email4', 'status')))
    ->from($db->quoteName('#__tid_codes'))
    ->where(implode(' OR ', $conditions1))
    ->where('(' . implode(' OR ', $conditions2) . ')');

Which will result in this query:

SELECT `tidcode`,`cdcode`,`email1`,`email2`,`email3`,`email4`,`status` FROM `#__tid_codes` WHERE `tidcode` = '10348' OR `tidcode` = 'F10348' AND (`email1` = '[email protected]' OR `email2` = '[email protected]' OR `email3` = '[email protected]' OR `email4` = '[email protected]')

Answer 2

This is a bit wordy, but if you ever get tripped up on complex where() expressions or don't know about operator precedence please read this at least once.

Your question is slightly ambiguous about how the WHERE clause conditions should be evaluated. This page is a suitable location to mention Operator Precedence. Unless otherwise stated, we can all assume that the precedence is working as you require.

However, before I start explaining the impact of that rule, I first want to condense the logic of the 3rd/final portion of your WHERE clause (the email matching).

`email1` = '[email protected]'
OR `email2` = '[email protected]'
OR `email3` = '[email protected]'
OR `email4` = '[email protected]'

...can be written much more succinctly (D.R.Y. coding practice) as:

'[email protected]' IN (`email1`, `email2`, `email3`, `email4`)

---

Now I'll demonstrate operator precedence using that condensed expression.

• A will be the tidcode comparison
• B will be the newtidcode comparison
• C will be the email comparison

ANDs are evaluated before ORs (see earlier hyperlink)

A OR B AND C is the same as A OR (B AND C) ...and I think that is what you desire.

If you wanted A OR B to be evaluated before AND C, you would need to be explicit with parentheses like this: (A OR B) AND C.

---

That said, I'll offer two different Joomla-syntax solutions.

Single WHERE method call: (a lot of line width, but reads like raw sql)

->where("tidcode = " . $db->q($tidcode) . " OR tidcode = " . $db->q($newtidcode) . " AND " . $db->q($email) . " IN (email1, email2, email3, email4)");

Two WHERE method calls: (reduced line width, but reads less like raw sql)

->where("tidcode = " . $db->q($tidcode), "OR")
->where("tidcode = " . $db->q($newtidcode) . " AND " . $db->q($email) . " IN (email1, email2, email3, email4)");

Both produce:

WHERE tidcode = '10348' OR tidcode = 'F10348' AND '[email protected]' IN (email1, email2, email3, email4)

---

---

For comparison, the following snippets evaluate the OR first!:

->where("tidcode = " . $db->q($tidcode))
->extendWhere("OR", "tidcode = " . $db->q($newtidcode))
->extendWhere("AND", $db->q($email) . " IN (email1, email2, email3, email4)");

and

->where("tidcode = " . $db->q($tidcode))
->orWhere("tidcode = " . $db->q($newtidcode))
->andWhere($db->q($email) . " IN (email1, email2, email3, email4)");

produce (via $query->dump()):

WHERE 
(
(tidcode = '10348') OR 
(tidcode = 'F10348')) AND 
('[email protected]' IN (email1, email2, email3, email4))

...BUT, if you DO want to evaluate the OR first, then go ahead and repackage the first two conditions of the WHERE clause with IN too.

->where(
    array(
        "tidcode IN (" . implode(", ", $db->q(array($tidcode, $newtidcode))) . ")",
        $db->q($email) . " IN (email1, email2, email3, email4)"
    )
);

which produces:

WHERE tidcode IN ('10348', 'F10348') AND '[email protected]' IN (email1, email2, email3, email4)