Grouping WHERE clauses with Zend_Db_Table_Abstract
Solution 1
In order to achieve this, you have to construct the grouped clause within a single call to the where
method.
If both values of conditions are the same, you can do this:
$select->where('client_email = ? OR client_email_alt = ?', $client_email)
If there are multiple placeholders within the string, the DB adapter's quoteInto
method will replace all placeholders with the provided value.
If you need to group an OR
with different values for each field, you have to manually quote the values. It's a bit more complex:
$select->where(
$db->quoteInto('client_email = ?', $email1) . ' OR ' . $db->quoteInto('client_email_alt = ?', $email2)
); // $db is your instance of Zend_Db_Adapter_*
// You can get it from a Zend_Db_Table_Abstract
//subclass by calling its getAdapter() method
Solution 2
You can use getPart()
to get WHERE statement and then connect sub-queries.
$select->where('client_email = ?', $client_email)
->orWhere('client_email_alt = ?', $client_email);
$subquery = $select->getPart(Zend_Db_Select::WHERE);
$select ->reset(Zend_Db_Select::WHERE);
$select ->where('company_id = ?', $company_id)
->where(implode(' ',$subquery));
Solution 3
For Zend Framework Version 2, things differ a bit:
See http://framework.zend.com/apidoc/2.2/classes/Zend.Db.Sql.Predicate.Predicate.html#nest
$table->select()
->where(['company_id'=> $company_id])
->nest
->where('client_email = ?', $client_email)
->or
->where('client_email_alt = ?', $client_email)
->unnest();
works fine and feels much cleaner than the ZF1 methods.
Solution 4
I needed to combine AND/OR statements but including OR statements conditionally, adding them only in some cases. This solution is an adaptation of what I did, based on small modifications of the accepted answer.
$sql = $table->select()
->where('company_id = ?', $company_id);
$orWhereClauses = [];
// We could add a conditional statement to add this statement
$orWhereClauses[] = $db->quoteInto('client_email = ?', $email1);
// Same applies to this statement
$orWhereClauses[] = $db->quoteInto('client_email_alt = ?', $email2);
$sql->where(implode(" OR ", $orWhereClauses));
Mark
Updated on June 12, 2022Comments
-
Mark almost 2 years
Does anyone know of a way to group where clauses with Zend_Db? Basically I have this query
$sql = $table->select() ->where('company_id = ?', $company_id) ->where('client_email = ?', $client_email) ->orWhere('client_email_alt = ?', $client_email);
Which is giving me this:
SELECT `clients`.* FROM `clients` WHERE (company_id = '1') AND (client_email = '[email protected]') OR (client_email_alt = '[email protected]')
But I need it to give me this, where the OR statement is grouped:
SELECT `clients`.* FROM `clients` WHERE (company_id = '1') AND ((client_email = '[email protected]') OR (client_email_alt = '[email protected]'))
-
Angry Dan about 12 yearsWhat we need is $select->startWhereGroup() and $select->endWhereGroup().
-
Wolfgang Stengel over 11 yearsThe Zend Framework 2 \Zend\Db\Sql\Select looks promising in this regard.
-
mmmdearte about 5 yearsAwesome! thanks Jason, I have been looking for this solution for ages. It works like a treat. Thanks :)