So... I've inherited this rather large project based on Zend Framework 1.12 and a feature that I'm trying to add involves a more complex database operation than what I'm used to doing with the project. I'm extremely new to Zend Framework, or MVC for that matter. I found an answer for Zend Framework 3 which would have been perfect but I have to make do with this version.
The function basically builds a Zend_Db_Select based on various parameters and the feature I'm trying to add will involve joining two different tables and checking if a specific combination exists in one or the other.
Here's what I have so far:
//SQL that I'm trying to do. Assume table1 and table2 are already joined.
//Ignore the imperfect syntax. I'm trying to get the concept across.
//SELECT * FROM (table1 joined to table2 by a common key)
//WHERE ( (table1.column1 = myParam1) AND (table1.column2 = myParam2) )
//OR WHERE ( (table2.column1 = myParam1) AND (table2.column2 = myParam2) )
public function buildSelect($params){
//Zend code starts here
//This one starts the Zend_Db_Select
$select = $this->select();
$table1Name = get_table_name_from_object($table1);
//lots of preexisting code here
//my code starts here.
$table2Name = get_table_name_from_object($table2);
$select->join($table2Name, "$table1Name.key = $table2Name.key", array('column1', 'column2', 'key');
//After I wrote this, I instantly realized why it won't work the way I intended it but putting it here to show what I tried at which point I got stuck.
$select->where("($table1Name.column1 = ?) OR ($table2Name.column1 = ?)",$params[1]);
$select->where( "($table1Name.column2 = ?) OR ($table2Name.column2 = ?)", $params[2]);
//more preexisting code below.
return $select
}
Obviously, if I tried this as is, the program will happily return results that include a combination of, say, an entry where param1 is in table1.column1 and param2 is in table2.column2.
I received some feedback from a friend and posting here for posterity.
They noticed my code already contains parentheses and recommended that I simply take advantage of orWhere() then write it like this:
$select->where("($tableName1.column1 = ?", $params[param1])
->where("$tableName1.column2 = ?)", $params[param2]);
$select->orWhere("($tableName1.column1 = ?",$params[param1])
->where("$tableName2.column2 = ?)",$params[param2]);