21

Given the desired SQL below, where Cond1 and Cond2 must be met OR Cond3 must be met for selection, what is the correct way to use getQuery() to achieve it?

Desired SQL: Condition1 and Condition2 within parentheses)

SELECT * FROM #__myTable 
WHERE (condition1=true AND condition2=true) OR condition3=true

With Chaining: specifying OR in the ->where()

$query = $db->getQuery(true);
$query->select('* FROM #__myTable')
->where('condition1 = true AND condition2 = true','OR')
->where('condition3 = true');

Resulting SQL: (SQL is missing parentheses)

SELECT * FROM scm_myTable
WHERE condition1 = true AND condition2 = true OR condition3 = true

With Arrays specifying OR in the ->where()

$query = $db->getQuery(true);
$conditions12 = array(
    'condition1 = true',
    'condition2 = true'
);
$conditions3 = array(
    'condition3 = true'
);
$query->select('* FROM #__myTable')
->where($conditions12, 'OR')
->where($conditions3);

Resulting SQL: (SQL is missing parentheses)

SELECT * FROM scm_myTable
WHERE condition1 = true OR condition2 = true OR condition3 = true
19

Including the parentheses that are needed, with Chaining , produced the desired SQL:

With Chaining: specifying OR and wrapping Cond1/2 in parentheses

$query = $db->getQuery(true);
$query->select('* FROM #__myTable')
->where('(condition1 = true AND condition2 = true)','OR')
->where('condition3 = true');

Resulting SQL: (includes parentheses)

SELECT * FROM scm_myTable
WHERE (condition1 = true AND condition2 = true) OR condition3 = true

OR use orWhere/andWhere

$query = $db->getQyery(true);
$query->select('*')
->from($db->quoteName('#__myTable'))
->where($db-quoteName('condition3') . ' = TRUE')
->orWhere(array($db->quoteName('condition1') . ' = TRUE', $db->quoteName('condition2') . ' = TRUE'));

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.