PHP Zend_Db_Select::having方法代码示例

 public static function whereProximity(Zend_Db_Select $select, $centerLat, $centerLong, $fieldLat, $fieldLong, $distance, $calculatedDistanceAs = "calcDistance")
 {
     self::init();
     if (empty($centerLat) || empty($centerLong) || empty($fieldLat) || empty($fieldLong) || empty($distance)) {
         return $select;
     }
     $centerLat = floatval($centerLat);
     $centerLong = floatval($centerLong);
     $distance = floatval($distance);
     if ($distance >= 0 && $distance <= 12450.775) {
         $longOffset = $distance / abs(cos(deg2rad($centerLat)) * 69);
         $rectLong1 = $centerLong - $longOffset;
         $rectLong2 = $centerLong + $longOffset;
         $latOffset = $distance / 69;
         $rectLat1 = $centerLat - $latOffset;
         $rectLat2 = $centerLat + $latOffset;
         /**
          * @author Pavel Shutin
          */
         $select->where("{$fieldLat} >= ? ", $rectLat1)->where("{$fieldLat} <= ? ", $rectLat2)->where("{$fieldLong} >= ? ", $rectLong1)->where("{$fieldLong} <= ? ", $rectLong2);
     }
     if (!empty($calculatedDistanceAs)) {
         /* Distance Formula : 3956 * 2 * ASIN(SQRT( POWER(SIN((orig.lat - dest.lat) * pi()/180 / 2), 2) + COS(orig.lat * pi()/180) * COS(dest.lat * pi()/180) * POWER(SIN((orig.lon -dest.lon) * pi()/180 / 2), 2) ))as distance */
         $earthDiameter = 7912;
         $piOver180 = 0.01745329;
         $select->columns(array($calculatedDistanceAs => new Zend_Db_Expr("{$earthDiameter} * ASIN(SQRT( POWER(SIN(({$centerLat} - {$fieldLat}) * {$piOver180} / 2), 2) + COS({$centerLat} * {$piOver180}) * COS({$fieldLat} * {$piOver180}) * POWER(SIN(({$centerLong} - {$fieldLong}) * {$piOver180} / 2), 2) ))")));
         /**
          * @author Pavel Shutin
          */
         $select->having($calculatedDistanceAs . ' <= ?', $distance);
     }
     //var_dump($select->__toString());exit;
     return $select;
 }

 /**
  * filter
  *
  * @param string $column
  * @param string $filter
  */
 public function filter($column, $filter)
 {
     $this->_select->having($column . ' LIKE ?', str_replace('*', '%', $filter));
 }

 /**
  * add to/cc/bcc and flags custom filters
  *
  * @param  Zend_Db_Select                       $_select
  * @param  Felamimail_Backend_Cache_Sql_Message $_backend
  * @param  array                                $_filterData
  * @return void
  */
 protected function _addRecipientAndFlagsSql($_select, $_backend, $_filterData)
 {
     $db = $_backend->getAdapter();
     $foreignTables = $_backend->getForeignTables();
     // add conditions
     $tablename = $foreignTables[$_filterData['field']]['table'];
     if ($_filterData['field'] !== 'flags') {
         $fieldName = $tablename . '.name';
         $fieldEmail = $tablename . '.email';
     }
     // add filter value
     if (!is_array($_filterData['value'])) {
         $value = '%' . $_filterData['value'] . '%';
     } else {
         $value = array();
         foreach ((array) $_filterData['value'] as $customValue) {
             $value[] = '%' . $customValue . '%';
         }
     }
     if ($_filterData['field'] == 'flags') {
         $columns = $_select->getPart(Zend_Db_Select::COLUMNS);
         $flags = '';
         foreach ($columns as $column) {
             if ($column[2] == 'flags') {
                 $flags = '(' . $column[1] . ')';
                 break;
             }
         }
         if ($_filterData['operator'] == 'equals' || $_filterData['operator'] == 'contains') {
             $_select->having($db->quoteInto($flags . ' LIKE ?', $value));
         } else {
             if ($_filterData['operator'] == 'in' || $_filterData['operator'] == 'notin') {
                 if (empty($value)) {
                     $whereString = $flags . ' IS NULL';
                 } else {
                     $value = (array) $value;
                     $where = array();
                     $op = $_filterData['operator'] == 'in' ? 'LIKE' : 'NOT LIKE';
                     $opImplode = $_filterData['operator'] == 'in' ? ' OR ' : ' AND ';
                     foreach ($value as $flag) {
                         $where[] = $db->quoteInto($flags . ' ' . $op . ' ?', $flag);
                     }
                     $whereString = implode($opImplode, $where);
                     if ($_filterData['operator'] == 'notin') {
                         $whereString = '(' . $whereString . ') OR ' . $flags . ' IS NULL';
                     }
                 }
                 $_select->having($whereString);
             } else {
                 $_select->having($db->quoteInto($flags . ' NOT LIKE ? OR ' . $flags . ' IS NULL', $value));
             }
         }
     } else {
         $_select->where($db->quoteInto($fieldName . ' LIKE ?', $value) . ' OR ' . $db->quoteInto($fieldEmail . ' LIKE ?', $value));
     }
 }

 /**
  * appends sql to given select statement
  *
  * @param  Zend_Db_Select                    $_select
  * @param  Tinebase_Backend_Sql_Abstract     $_backend
  */
 public function appendFilterSql($_select, $_backend)
 {
     if ($this->_value === 'all') {
         $_select->where('1=1');
         return;
     }
     $gs = new Tinebase_Backend_Sql_Filter_GroupSelect($_select);
     $adapter = $_backend->getAdapter();
     //if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) Tinebase_Core::getLogger()->debug(__METHOD__ . ' (' . __LINE__ . ') value: ' . print_r($this->_value, true));
     foreach ($this->_value as $attenderValue) {
         if (in_array($attenderValue['user_type'], array(Calendar_Model_Attender::USERTYPE_USER, Calendar_Model_Attender::USERTYPE_GROUPMEMBER))) {
             // @todo user_id might contain filter in the future -> get userids from adressbook controller with contact filter
             // transform CURRENTCONTACT
             $attenderValue['user_id'] = $attenderValue['user_id'] == Addressbook_Model_Contact::CURRENTCONTACT ? Tinebase_Core::getUser()->contact_id : $attenderValue['user_id'];
             $attendee = array(array('user_type' => Calendar_Model_Attender::USERTYPE_USER, 'user_id' => $attenderValue['user_id']), array('user_type' => Calendar_Model_Attender::USERTYPE_GROUPMEMBER, 'user_id' => $attenderValue['user_id']));
         } else {
             if ($attenderValue['user_type'] == self::USERTYPE_MEMBEROF) {
                 // resolve group members
                 $group = Tinebase_Group::getInstance()->getGroupById($attenderValue['user_id']);
                 $attendee = array();
                 // fetch list only if list_id is not NULL, otherwise we get back an empty list object
                 if (!empty($group->list_id)) {
                     $contactList = Addressbook_Controller_List::getInstance()->get($group->list_id);
                     foreach ($contactList->members as $member) {
                         $attendee[] = array('user_type' => Calendar_Model_Attender::USERTYPE_USER, 'user_id' => $member);
                         $attendee[] = array('user_type' => Calendar_Model_Attender::USERTYPE_GROUPMEMBER, 'user_id' => $member);
                     }
                 }
             } else {
                 $attendee = array($attenderValue);
             }
         }
         foreach ($attendee as $attender) {
             $gs->orWhere($adapter->quoteInto($adapter->quoteIdentifier('attendee.user_type') . ' = ?', $attender['user_type']) . ' AND ' . $adapter->quoteInto($adapter->quoteIdentifier('attendee.user_id') . ' = ?', $attender['user_id']));
         }
     }
     if (substr($this->_operator, 0, 3) === 'not') {
         // join attendee to be excluded as a new column. records having this column NULL don't have the attendee
         $dname = 'attendee-not-' . Tinebase_Record_Abstract::generateUID(5);
         $_select->joinLeft(array($dname => $_backend->getTablePrefix() . 'cal_attendee'), $adapter->quoteIdentifier($dname . '.cal_event_id') . ' = ' . $adapter->quoteIdentifier($_backend->getTableName() . '.id') . ' AND ' . $gs->getSQL(), array($dname => $_backend->getDbCommand()->getAggregate($dname . '.id')));
         $_select->having($_backend->getDbCommand()->getAggregate($dname . '.id') . ' IS NULL');
     } else {
         $gs->appendWhere(Zend_Db_Select::SQL_OR);
     }
 }

 /**
  * Apply the given tree to the query, either as where or as having clause
  *
  * @param Tree $tree                        The tree representing the filter
  * @param \Zend_Db_Select $baseQuery        The query to apply the filter on
  */
 public function treeToSql(Tree $tree, $baseQuery)
 {
     if ($tree->root == null) {
         return;
     }
     $sql = $this->nodeToSqlQuery($tree->normalizeTree($tree->root));
     if ($this->filtersAggregate()) {
         $baseQuery->having($sql);
     } else {
         $baseQuery->where($sql);
     }
 }

 /**
  * add to/cc/bcc and flags custom filters
  * 
  * @param  Zend_Db_Select                       $_select
  * @param  Felamimail_Backend_Cache_Sql_Message $_backend
  * @param  array                                $_filterData
  * @return void
  */
 protected function _addRecipientAndFlagsSql($_select, $_backend, $_filterData)
 {
     $db = $_backend->getAdapter();
     $foreignTables = $_backend->getForeignTables();
     // add conditions
     $tablename = $foreignTables[$_filterData['field']]['table'];
     if ($_filterData['field'] !== 'flags') {
         $fieldName = $tablename . '.name';
         $fieldEmail = $tablename . '.email';
     }
     // add filter value
     if (!is_array($_filterData['value'])) {
         $value = '%' . $_filterData['value'] . '%';
     } else {
         $value = array();
         foreach ((array) $_filterData['value'] as $customValue) {
             $value[] = '%' . $customValue . '%';
         }
     }
     if ($_filterData['field'] == 'flags') {
         $havingColumn = $db instanceof Zend_Db_Adapter_Pdo_Pgsql ? Tinebase_Backend_Sql_Command::factory($db)->getAggregate('felamimail_cache_msg_flag.flag') : 'flags';
         if ($_filterData['operator'] == 'equals' || $_filterData['operator'] == 'contains') {
             $_select->having($db->quoteInto($havingColumn . ' LIKE ?', $value));
         } else {
             if ($_filterData['operator'] == 'in' || $_filterData['operator'] == 'notin') {
                 if (empty($value)) {
                     $whereString = 'flags IS NULL';
                 } else {
                     $value = (array) $value;
                     $where = array();
                     $op = $_filterData['operator'] == 'in' ? 'LIKE' : 'NOT LIKE';
                     $opImplode = $_filterData['operator'] == 'in' ? ' OR ' : ' AND ';
                     foreach ($value as $flag) {
                         $where[] = $db->quoteInto('flags ' . $op . ' ?', $flag);
                     }
                     $whereString = implode($opImplode, $where);
                     if ($_filterData['operator'] == 'notin') {
                         $whereString = '(' . $whereString . ') OR flags IS NULL';
                     }
                 }
                 $_select->having(str_replace('flags', $havingColumn, $whereString));
             } else {
                 $_select->having($db->quoteInto($havingColumn . ' NOT LIKE ? OR ' . $havingColumn . ' IS NULL', $value));
             }
         }
     } else {
         $_select->where($db->quoteInto($fieldName . ' LIKE ?', $value) . ' OR ' . $db->quoteInto($fieldEmail . ' LIKE ?', $value));
     }
 }

 /**
  * filter
  *
  * @param string $column
  * @param string $filter
  */
 public function filter($column, $filter)
 {
     //TODO: use Sphinx
     $this->_select->having($column . ' LIKE ?', '%' . str_replace(array('%', '_'), array('\\%', '\\_'), $filter) . '%');
 }