grouping orWhere clauses

[Gunter Sammet]

Hi all:
It would be nice if Zend_Db_Select could do a grouping of the orWhere clauses. E.g. to create something like this:

 "... where a = 'x' and b = 'y' and (c = 'a' or d = 'a')". I solved it by manually creating the grouped or portion:

<code>
        $db0 = SaSo_Registry::get( 'db0' );
        $select = $db0->select()
                      ->from( array('p' => 'photos'), array('p.photo_id', ' p.photo_name', 'p.photo_tn', 'p.photo_image', 'p.location_link', 'p.keyword_id_primary', 'p.keyword_id_secondary') )
                      ->join(array('m' => 'municipalities'), ' p.mun_id = m.mun_id', array());
        if(false != $this->_quickSearch){
            SaSo_Registry::get( 'log' )->log('Quick search not yet defined', SaSo_Log::ERR);
        }else{
            if( 'western' == $this->_municipalitySize ){
                //western where clause here. not yet defined
                SaSo_Registry::get( 'log' )->log('western where clause here. not yet defined', SaSo_Log::ERR);
            }elseif( 0 != $this->_municipalitySize ){
                $munSize = explode('_', $this->_municipalitySize);
                if( (0 <= (int)$munSize[0]) && ((int)$munSize[1] > (int)$munSize[0]) ){
                    $select->where( ' m.population > ?', (int)$munSize[0] )
                           ->where( 'm.population < ?', (int)$munSize[1] );
                }
            }
            if( 0 < $this->_keywordPrimary){
                $select->where( 'p.keyword_id_primary = ?', $this->_keywordPrimary );
            }
            if( 0 < $this->_keywordSecondary){
                $select->where( 'p.keyword_id_secondary = ?', $this->_keywordSecondary );
            }
            if( false != $this->_customSearchKeyword){
                $keywordWhere = ' ( 1=1 )';
                $searchFields = array(' p.photo_name', 'p.photo_desc');
                foreach($searchFields as $searchField){
                    $keywordWhere .= ' OR ( ' . $db0->quoteInto($searchField . ' = ?', $this->_customSearchKeyword) . ' ) ';
                }
                $select->where($keywordWhere);
            }
        }
        $select->limit($this->_dataRowsPerPage, $this->_dataRowsOffset);
</code>


If somebody is aware of an existing solution to this problem, I would appreciate a reply.
Thanks,

Gunter

[Bill Karwin from Zend]

Zend_Db_Select does not provide an interface to all possible boolean constructions.  For example, it does not support NOT predicates or nested boolean expressions.

 

Currently the solution to do what you describe is the following:

 

$select = $db0->select()

  ->from(...)

  ->where('a = ?', 'x')

  ->where('b = ?', 'y')

  ->where("c = 'a' OR d = 'a'");

 

In other words, any expressions deeper than what is supported by the current where() and orWhere() methods is up to you to construct as a string.

 

Note that Zend_Db_Select does put parentheses around AND terms, so if the expression within your term contains an OR, you don't get surprised by operator precedence issues.

 

IMO, a programmatic interface to constructing deeper boolean expressions does not have enough value to justify the complexity of creating it.  It makes it harder to write code and harder to read code.  Complex boolean expressions must be written as strings.

 

Hypothetically, if I were to design a solution to do this programmatically, it might have the usage below:

 

The where() optionally would accept an array argument. The array would be processed as terms that should be joined with the AND operation:

 

  // produces (a = 'x') AND (b = 'y')

  ->where(array("a = 'x'", "b = 'y'"))

 

If an array element were itself an array, its elements would be joined with an OR operation and then the result joined to the top array with AND.

 

  // produces (a = 'x') AND (b = 'y') AND ((c = 'a') OR (d = 'a'))

  ->where(array("a = 'x'", "b = 'y'", array("c = 'a'", "d = 'a'")))

 

If an element of the inner array were an array, its elements would be joined with AND and then the result processed as an OR term.  Each level of nested array would alternate to the complementary boolean operator.

 

The usage of orWhere() would be similar.  Each level of nested array would alternate just as in the where() method, but the top-level would start with the OR operator.

 

However, this solution would not support the quoted parameter processing currently supported by where() and orWhere(). You'd have to use quoteInto(). Also, it would have no programmatic interface for the boolean NOT operation.

 

Again, for readers who skim these messages instead of reading them carefully:  THIS IS AN HYPOTHETICAL DISCUSSION.  ZEND_DB_SELECT DOESN'T SUPPORT THE USAGE DESCRIBED ABOVE.

 

Regards,

Bill Karwin

---

From: Gunter Sammet [mailto:[hidden email]]
Sent: Monday, August 27, 2007 5:23 PM
To: [hidden email]
Subject: [fw-db] grouping orWhere clauses

Hi all:
It would be nice if Zend_Db_Select could do a grouping of the orWhere clauses. E.g. to create something like this:

 "... where a = 'x' and b = 'y' and (c = 'a' or d = 'a')". I solved it by manually creating the grouped or portion:

<code>
        $db0 = SaSo_Registry::get( 'db0' );
        $select = $db0->select()
                      ->from( array('p' => 'photos'), array('p.photo_id', ' p.photo_name', 'p.photo_tn', 'p.photo_image', 'p.location_link', 'p.keyword_id_primary', 'p.keyword_id_secondary') )
                      ->join(array('m' => 'municipalities'), ' p.mun_id = m.mun_id', array());
        if(false != $this->_quickSearch){
            SaSo_Registry::get( 'log' )->log('Quick search not yet defined', SaSo_Log::ERR);
        }else{
            if( 'western' == $this->_municipalitySize ){
                //western where clause here. not yet defined
                SaSo_Registry::get( 'log' )->log('western where clause here. not yet defined', SaSo_Log::ERR);
            }elseif( 0 != $this->_municipalitySize ){
                $munSize = explode('_', $this->_municipalitySize);
                if( (0 <= (int)$munSize[0]) && ((int)$munSize[1] > (int)$munSize[0]) ){
                    $select->where( ' m.population > ?', (int)$munSize[0] )
                           ->where( 'm.population < ?', (int)$munSize[1] );
                }
            }
            if( 0 < $this->_keywordPrimary){
                $select->where( 'p.keyword_id_primary = ?', $this->_keywordPrimary );
            }
            if( 0 < $this->_keywordSecondary){
                $select->where( 'p.keyword_id_secondary = ?', $this->_keywordSecondary );
            }
            if( false != $this->_customSearchKeyword){
                $keywordWhere = ' ( 1=1 )';
                $searchFields = array(' p.photo_name', 'p.photo_desc');
                foreach($searchFields as $searchField){
                    $keywordWhere .= ' OR ( ' . $db0->quoteInto($searchField . ' = ?', $this->_customSearchKeyword) . ' ) ';
                }
                $select->where($keywordWhere);
            }
        }
        $select->limit($this->_dataRowsPerPage, $this->_dataRowsOffset);
</code>


If somebody is aware of an existing solution to this problem, I would appreciate a reply.
Thanks,

Gunter

[Bill Karwin from Zend]

Try this to eliminate the ugly "( 1=1 )" hack.  Also you should compare the keyword using "false !==" in case the keyword is a value that evaluates to false, like empty string or 0.  And I would quote the identifiers for the search fields in case they are SQL keywords.

 

if (false !== $this->_customSearchKeyword) {

  $searchFields = array('p.photo_name', 'p.photo_desc');

  $keywordWhere = array();

  if (!empty($searchFields)) {

    foreach ($searchFields as $searchField) {

      $searchField = $db0->quoteIdentifier($searchField);

      $keywordWhere[] = '(' . $db0->quoteInto("$searchField = ?", $this->_customSearchKeyword) . ')';

    }

    $select->where( implode(' OR ', $keywordWhere) );

  }

}

 

Regards,

Bill Karwin

---

From: Gunter Sammet [mailto:[hidden email]] 

            if( false != $this->_customSearchKeyword){
                $keywordWhere = ' ( 1=1 )';
                $searchFields = array(' p.photo_name', 'p.photo_desc');
                foreach($searchFields as $searchField){
                    $keywordWhere .= ' OR ( ' . $db0->quoteInto($searchField . ' = ?', $this->_customSearchKeyword) . ' ) ';
                }
                $select->where($keywordWhere);
            }
        }
        $select->limit($this->_dataRowsPerPage, $this->_dataRowsOffset);
</code>


If somebody is aware of an existing solution to this problem, I would appreciate a reply.
Thanks,

Gunter