6

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` = 'someone@fake.net' OR `email2` = 'someone@fake.net' OR `email3` = 'someone@fake.net' OR `email4` = 'someone@fake.net')

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.

  • You're using AND in your 2nd where clause, but it doesn't appear to be visible in your native SQL statement. It's showing OR instead. Is this the issue or purposely done? – Lodder Mar 18 '16 at 11:06
6

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` = 'someone@fake.net' OR `email2` = 'someone@fake.net' OR `email3` = 'someone@fake.net' OR `email4` = 'someone@fake.net')
  • Now that I see the "WHERE" code it makes complete sense. Thank you for steering me in the right direction. Especially with imploding the conditions, I did not think of that. I am sure this will help more than just me as I searched high and low and found nothing. – TJM Mar 18 '16 at 15:10
1

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` = 'someone@fake.net'
OR `email2` = 'someone@fake.net'
OR `email3` = 'someone@fake.net'
OR `email4` = 'someone@fake.net'

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

'someone@fake.net' 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 'someone@fake.net' 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 
('someone@fake.net' 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 'someone@fake.net' IN (email1, email2, email3, email4)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.