How to combine ANDs and ORs in the WHERE clause using a Query Object?

by GDP   Last Updated April 20, 2018 00:10 AM

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
Tags : jdatabase sql


Answers 1


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'));
GDP
GDP
December 09, 2014 18:42 PM

Related Questions



JDatabase Update +1

Updated June 08, 2017 11:10 AM

JDatabase SQL AND query

Updated May 14, 2015 21:04 PM

Join two similar table SQL

Updated December 15, 2017 02:10 AM

Very slow query using JDatabase vs Manual

Updated June 18, 2015 07:04 AM