PHP Zend_Db_Select::where方法代码示例

 public function getTotal($type, $start = null, $end = null)
 {
     $select = new Zend_Db_Select($this->getAdapter());
     $select->from($this->info('name'), 'SUM(value) as sum')->where('type = ?', $type);
     // Can pass "today" into start
     switch ($start) {
         case 'day':
             $start = mktime(0, 0, 0, gmdate("n"), gmdate("j"), gmdate("Y"));
             $end = mktime(0, 0, 0, gmdate("n"), gmdate("j") + 1, gmdate("Y"));
             break;
         case 'week':
             $start = mktime(0, 0, 0, gmdate("n"), gmdate("j") - gmdate('N') + 1, gmdate("Y"));
             $end = mktime(0, 0, 0, gmdate("n"), gmdate("j") - gmdate('N') + 1 + 7, gmdate("Y"));
             break;
         case 'month':
             $start = mktime(0, 0, 0, gmdate("n"), gmdate("j"), gmdate("Y"));
             $end = mktime(0, 0, 0, gmdate("n") + 1, gmdate("j"), gmdate("Y"));
             break;
         case 'year':
             $start = mktime(0, 0, 0, gmdate("n"), gmdate("j"), gmdate("Y"));
             $end = mktime(0, 0, 0, gmdate("n"), gmdate("j"), gmdate("Y") + 1);
             break;
     }
     if (null !== $start) {
         $select->where('date >= ?', gmdate('Y-m-d', $start));
     }
     if (null !== $end) {
         $select->where('date < ?', gmdate('Y-m-d', $end));
     }
     $data = $select->query()->fetch();
     if (!isset($data['sum'])) {
         return 0;
     }
     return $data['sum'];
 }

 /**
  * Run query and returns matches, or null if no matches are found.
  *
  * @param  String $value
  * @return Array when matches are found.
  */
 protected function _query($value)
 {
     /**
      * Check for an adapter being defined. if not, fetch the default adapter.
      */
     if ($this->_adapter === null) {
         $this->_adapter = Zend_Db_Table_Abstract::getDefaultAdapter();
     }
     /**
      * Build select object
      */
     $select = new Zend_Db_Select($this->_adapter);
     $select->from($this->_table, array($this->_field), $this->_schema)->where($this->_adapter->quoteIdentifier($this->_field) . ' = ?', $value);
     if ($this->_exclude !== null) {
         if (is_array($this->_exclude)) {
             $select->where($this->_adapter->quoteIdentifier($this->_exclude['field']) . ' != ?', $this->_exclude['value']);
         } else {
             $select->where($this->_exclude);
         }
     }
     $select->limit(1);
     /**
      * Run query
      */
     $result = $this->_adapter->fetchRow($select, array(), Zend_Db::FETCH_ASSOC);
     return $result;
 }

 function getById($jobid)
 {
     // do Bacula ACLs
     Zend_Loader::loadClass('Job');
     $table = new Job();
     if (!$table->isJobIdExists($jobid)) {
         return FALSE;
     }
     $select = new Zend_Db_Select($this->db);
     switch ($this->db_adapter) {
         case 'PDO_SQLITE':
             // bug http://framework.zend.com/issues/browse/ZF-884
             $select->distinct();
             $select->from(array('l' => 'Log'), array('logid' => 'LogId', 'jobid' => 'JobId', 'LogTime' => 'Time', 'logtext' => 'LogText'));
             $select->where("JobId = ?", $jobid);
             $select->order(array('LogId', 'LogTime'));
             break;
         default:
             // mysql, postgresql
             $select->distinct();
             $select->from(array('l' => 'Log'), array('LogId', 'JobId', 'LogTime' => 'Time', 'LogText'));
             $select->where("JobId = ?", $jobid);
             $select->order(array('LogId', 'LogTime'));
     }
     //$sql = $select->__toString(); echo "<pre>$sql</pre>"; exit; // for !!!debug!!!
     $stmt = $select->query();
     return $stmt->fetchAll();
 }

 /**
  * 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 === self::VALUE_NOTSET) {
         return;
     }
     $action = $this->_opSqlMap[$this->_operator];
     $db = $_backend->getAdapter();
     // prepare value
     $value = $this->_value ? 1 : 0;
     if (!empty($this->_options['fields'])) {
         foreach ((array) $this->_options['fields'] as $fieldName) {
             $quotedField = $db->quoteIdentifier(strpos($fieldName, '.') === false ? $_backend->getTableName() . '.' . $fieldName : $fieldName);
             if ($value) {
                 $_select->where($quotedField . $action['sqlop'], $value);
             } else {
                 $_select->orwhere($quotedField . $action['sqlop'], $value);
             }
         }
     } else {
         if (!empty($this->_options['leftOperand'])) {
             $_select->where($this->_options['leftOperand'] . $action['sqlop'], $value);
         } else {
             $_select->where($this->_getQuotedFieldName($_backend) . $action['sqlop'], $value);
         }
     }
 }

 /**
  * Returns the select object to be used for validation.
  * @return Zend_Db_Select $select
  */
 public function getSelect()
 {
     if (null === $this->_select) {
         $db = $this->getAdapter();
         // Zend_Debug::dump($this->_excludeId ); // die(0);
         $select = new Zend_Db_Select($db);
         $select->from($this->_table, array($this->_field), $this->_schema);
         if ($db->supportsParameters('named')) {
             $select->where($db->quoteIdentifier($this->_field, true) . ' = :value');
             // named
         } else {
             $select->where($db->quoteIdentifier($this->_field, true) . ' = ?');
             // positional
         }
         $select->limit(1);
         if (!empty($this->_meetingId)) {
             $select->where($db->quoteInto('`meeting_id` = ?', $this->_meetingId));
         }
         if (!empty($this->_excludeId)) {
             $select->where($db->quoteInto('`id` != ?', $this->_excludeId));
         }
         $this->_select = $select;
     }
     return $this->_select;
 }

 /**
  * appends sql to given select statement
  *
  * @param Zend_Db_Select                $_select
  * @param Tinebase_Backend_Sql_Abstract $_backend
  */
 public function appendFilterSql($_select, $_backend)
 {
     $action = $this->_opSqlMap[$this->_operator];
     if (empty($this->_value) && $this->_value != '0') {
         // prevent sql error
         if ($this->_operator == 'in' || $this->_operator == 'equals') {
             if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) {
                 Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' Empty value with "in" operator (model: ' . (isset($this->_options['modelName']) ? $this->_options['modelName'] : 'unknown / no modelName defined in filter options') . ')');
             }
             $_select->where('1=0');
         }
     } else {
         if ($this->_operator == 'equals' && is_array($this->_value)) {
             if (Tinebase_Core::isLogLevel(Zend_Log::NOTICE)) {
                 Tinebase_Core::getLogger()->notice(__METHOD__ . '::' . __LINE__ . ' Unexpected array value with "equals" operator (model: ' . (isset($this->_options['modelName']) ? $this->_options['modelName'] : 'unknown / no modelName defined in filter options') . ')');
             }
             $_select->where('1=0');
         } else {
             $type = $this->_getFieldType($_backend);
             $this->_enforceValueType($type);
             $field = $this->_getQuotedFieldName($_backend);
             // finally append query to select object
             $_select->where($field . $action['sqlop'], $this->_value, $type);
         }
     }
 }

 public static function _setSelectRules(Zend_Db_Select $select)
 {
     $select->where('accountStatus != ?', self::STATUS_DELETED);
     if (!is_null(self::getMyType())) {
         $select->where('type = ?', static::getMyType());
     }
 }

 /**
  * Add attribute to filter
  *
  * @param int $storeId
  * @param string $attributeCode
  * @param mixed $value
  * @param string $type
  * @return Zend_Db_Select
  */
 protected function _addFilter($storeId, $attributeCode, $value, $type = '=')
 {
     if (!isset($this->_attributesCache[$attributeCode])) {
         $attribute = Mage::getSingleton('catalog/product')->getResource()->getAttribute($attributeCode);
         $this->_attributesCache[$attributeCode] = array('entity_type_id' => $attribute->getEntityTypeId(), 'attribute_id' => $attribute->getId(), 'table' => $attribute->getBackend()->getTable(), 'is_global' => $attribute->getIsGlobal() == Mage_Catalog_Model_Resource_Eav_Attribute::SCOPE_GLOBAL, 'backend_type' => $attribute->getBackendType());
     }
     $attribute = $this->_attributesCache[$attributeCode];
     if (!$this->_select instanceof Zend_Db_Select) {
         return false;
     }
     switch ($type) {
         case '=':
             $conditionRule = '=?';
             break;
         case 'in':
             $conditionRule = ' IN(?)';
             break;
         default:
             return false;
             break;
     }
     $this->_select->join(array('t1_' . $attributeCode => $attribute['table']), 'r.entity_pk_value=t1_' . $attributeCode . '.entity_id AND t1_' . $attributeCode . '.store_id=0', array())->where('t1_' . $attributeCode . '.attribute_id=?', $attribute['attribute_id']);
     if ($attribute['is_global']) {
         $this->_select->where('t1_' . $attributeCode . '.value' . $conditionRule, $value);
     } else {
         $ifCase = $this->getCheckSql('t2_' . $attributeCode . '.value_id > 0', 't2_' . $attributeCode . '.value', 't1_' . $attributeCode . '.value');
         $this->_select->joinLeft(array('t2_' . $attributeCode => $attribute['table']), $this->_getWriteAdapter()->quoteInto('t1_' . $attributeCode . '.entity_id = t2_' . $attributeCode . '.entity_id AND t1_' . $attributeCode . '.attribute_id = t2_' . $attributeCode . '.attribute_id AND t2_' . $attributeCode . '.store_id=?', $storeId), array())->where('(' . $ifCase . ')' . $conditionRule, $value);
     }
     return $this->_select;
 }

 /**
  * appends sql to given select statement
  *
  * @param Zend_Db_Select                $_select
  * @param Tinebase_Backend_Sql_Abstract $_backend
  */
 public function appendFilterSql($_select, $_backend)
 {
     // quote field identifier, set action and replace wildcards
     $field = $this->_getQuotedFieldName($_backend);
     $action = $this->_opSqlMap[$this->_operator];
     $value = $this->_replaceWildcards($this->_value);
     if (in_array($this->_operator, array('in', 'notin')) && !is_array($value)) {
         $value = explode(' ', $this->_value);
     }
     if (in_array($this->_operator, array('equals', 'greater', 'less', 'in', 'notin'))) {
         $value = str_replace(array('%', '\\_'), '', $value);
         if (is_array($value) && empty($value)) {
             $_select->where('1=' . (substr($this->_operator, 0, 3) == 'not' ? '1/* empty query */' : '0/* impossible query */'));
         } elseif ($this->_operator == 'equals' && ($value === '' || $value === NULL || $value === false)) {
             $_select->where($field . 'IS NULL');
         } else {
             // finally append query to select object
             $_select->where($field . $action['sqlop'], $value, $this->valueType);
         }
     } else {
         // finally append query to select object
         $_select->where($field . $action['sqlop'], $value);
     }
     if (in_array($this->_operator, array('not', 'notin')) && $value !== '') {
         $_select->orWhere($field . ' IS NULL');
     }
 }

 /**
  * appends sql to given select statement
  *
  * @param Zend_Db_Select                $_select
  * @param Tinebase_Backend_Sql_Abstract $_backend
  */
 public function appendFilterSql($_select, $_backend)
 {
     // prepare value
     if ($this->_operator === 'equals' && empty($this->_value)) {
         // @see 0009362: allow to filter for empty datetimes
         $operator = 'isnull';
         $value = array($this->_value);
     } else {
         $operator = $this->_operator;
         $value = $this->_getDateValues($operator, $this->_value);
         if (!is_array($value)) {
             // NOTE: (array) null is an empty array
             $value = array($value);
         }
     }
     // quote field identifier
     $field = $this->_getQuotedFieldName($_backend);
     $db = Tinebase_Core::getDb();
     $dbCommand = Tinebase_Backend_Sql_Command::factory($db);
     // append query to select object
     foreach ((array) $this->_opSqlMap[$operator]['sqlop'] as $num => $operator) {
         if (isset($value[$num]) || array_key_exists($num, $value)) {
             if (get_parent_class($this) === 'Tinebase_Model_Filter_Date' || in_array($operator, array('isnull', 'notnull'))) {
                 $_select->where($field . $operator, $value[$num]);
             } else {
                 $_select->where($dbCommand->setDate($field) . $operator, new Zend_Db_Expr($dbCommand->setDateValue($value[$num])));
             }
         } else {
             if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) {
                 Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' No filter value found, skipping operator: ' . $operator);
             }
         }
     }
 }

 /**
  * appends sql to given select statement
  *
  * @param Zend_Db_Select                $_select
  * @param Tinebase_Backend_Sql_Abstract $_backend
  */
 public function appendFilterSql($_select, $_backend)
 {
     if (empty($this->_value)) {
         $_select->where('1=1/* empty query */');
         return;
     }
     $db = Tinebase_Core::getDb();
     switch ($this->_operator) {
         case 'contains':
         case 'equals':
         case 'startswith':
             $queries = explode(' ', $this->_value);
             foreach ($queries as $query) {
                 $whereParts = array();
                 foreach ($this->_options['fields'] as $qField) {
                     // if field has . in name, then we already have tablename
                     if (strpos($qField, '.') !== FALSE) {
                         $whereParts[] = Tinebase_Backend_Sql_Command::factory($db)->prepareForILike(Tinebase_Backend_Sql_Command::factory($db)->getUnaccent($db->quoteIdentifier($qField))) . ' ' . Tinebase_Backend_Sql_Command::factory($db)->getLike() . Tinebase_Backend_Sql_Command::factory($db)->prepareForILike(Tinebase_Backend_Sql_Command::factory($db)->getUnaccent('(?)'));
                     } else {
                         $whereParts[] = Tinebase_Backend_Sql_Command::factory($db)->prepareForILike(Tinebase_Backend_Sql_Command::factory($db)->getUnaccent($db->quoteIdentifier($_backend->getTableName() . '.' . $qField))) . ' ' . Tinebase_Backend_Sql_Command::factory($db)->getLike() . Tinebase_Backend_Sql_Command::factory($db)->prepareForILike(Tinebase_Backend_Sql_Command::factory($db)->getUnaccent('(?)'));
                     }
                 }
                 $whereClause = '';
                 if (!empty($whereParts)) {
                     $whereClause = implode(' OR ', $whereParts);
                 }
                 if (!empty($whereClause)) {
                     if ($this->_operator == 'equals') {
                         $_select->where($db->quoteInto($whereClause, trim($query)));
                     } else {
                         if ($this->_operator == 'startswith') {
                             $_select->where($db->quoteInto($whereClause, trim($query) . '%'));
                         } else {
                             $_select->where($db->quoteInto($whereClause, '%' . trim($query) . '%'));
                         }
                     }
                 }
             }
             break;
         case 'in':
             foreach ($this->_options['fields'] as $qField) {
                 // if field has . in name, then we allready have tablename
                 if (strpos($qField, '.') !== FALSE) {
                     $whereParts[] = $db->quoteInto($db->quoteIdentifier($qField) . ' IN (?)', (array) $this->_value);
                 } else {
                     $whereParts[] = $db->quoteInto($db->quoteIdentifier($_backend->getTableName() . '.' . $qField) . ' IN (?)', (array) $this->_value);
                 }
             }
             if (!empty($whereParts)) {
                 $whereClause = implode(' OR ', $whereParts);
             }
             if (!empty($whereClause)) {
                 $_select->where($whereClause);
             }
             break;
         default:
             throw new Tinebase_Exception_InvalidArgument('Operator not defined: ' . $this->_operator);
     }
 }

 /**
  * Padrão de filtros para os relatórios FEFOP  
  * 
  * @access protected
  * @param Zend_Db_Select $select
  * @return void
  */
 protected function _whereDefault(Zend_Db_Select $select)
 {
     if (!empty($this->_data['fk_id_dec'])) {
         $select->where('SysUser.fk_id_dec IN(?)', $this->_data['fk_id_dec']);
     }
     if (!empty($this->_data['id_fefop_programs'])) {
         $select->where('FEFOP_Contract.fk_id_fefop_programs IN(?)', $this->_data['id_fefop_programs']);
     }
     if (!empty($this->_data['id_fefop_modules'])) {
         $select->where('FEFOP_Contract.fk_id_fefop_modules IN(?)', $this->_data['id_fefop_modules']);
     }
     if (!empty($this->_data['id_adddistrict'])) {
         $select->where('AddDistrict.id_adddistrict IN(?)', $this->_data['id_adddistrict']);
     }
     if (!empty($this->_data['id_scholarity_area'])) {
         $select->where('DRH_TrainingPlan.fk_id_scholarity_area = ?', $this->_data['id_scholarity_area']);
     }
     if (!empty($this->_data['id_profocupationtimor'])) {
         $select->where('DRH_TrainingPlan.fk_id_profocupationtimor = ?', $this->_data['id_profocupationtimor']);
     }
     if (!empty($this->_data['id_fefpeduinstitution'])) {
         $select->where('DRH_TrainingPlan.fk_id_fefpeduinstitution = ?', $this->_data['id_fefpeduinstitution']);
     }
     if (!empty($this->_data['id_fefop_status'])) {
         $select->where('s.id_fefop_status IN(?)', $this->_data['id_fefop_status']);
     }
     if (!empty($this->_data['id_beneficiary'])) {
         $select->where('b.id = ?', $this->_data['id_beneficiary']);
     }
 }

 /**
  * appends sql to given select statement
  *
  * @param  Zend_Db_Select                    $_select
  * @param  Tinebase_Backend_Sql_Abstract     $_backend
  */
 public function appendFilterSql($_select, $_backend)
 {
     $correlationName = Tinebase_Record_Abstract::generateUID(30);
     $db = $_backend->getAdapter();
     $_select->joinLeft(array($correlationName => $db->table_prefix . 'addressbook_list_members'), $db->quoteIdentifier($correlationName . '.contact_id') . ' = ' . $db->quoteIdentifier('addressbook.id'), array());
     if (null === $this->_value) {
         $_select->where($db->quoteIdentifier($correlationName . '.list_id') . ' IS NULL');
     } else {
         $_select->where($db->quoteIdentifier($correlationName . '.list_id') . ' IN (?)', (array) $this->_value);
     }
 }

 /**
  * Get Canonical Collection ( all different link for that product )
  *
  * @param string $storeId
  * @param string $prodId
  *
  * @return Zend_Db_Statement_Interface
  */
 public function _getCollectionCE($storeId, $prodId, $catId = null)
 {
     $adapter = $this->_getReadAdapter();
     $urlConditions = array('e.product_id=ur.product_id', 'e.category_id=ur.category_id', $adapter->quoteInto('ur.store_id=?', $storeId), 'ur.is_system="1"');
     $this->_select = $adapter->select()->from(array('e' => $this->getMainTable()), array('category_id'))->where('e.product_id =?', $prodId)->where('e.store_id =?', $storeId)->where('e.is_parent= "1"');
     if ($catId) {
         $this->_select->where('e.category_id =?', $catId);
     }
     $this->_select = $this->_select->join(array('ur' => $this->getTable('core/url_rewrite')), join(' AND ', $urlConditions), array('url' => 'request_path'));
     //        die((string)($this->_select));
     $query = $adapter->query($this->_select);
     return $query;
 }

 public function getAllUsers($resource)
 {
     $select = new Zend_Db_Select($this->getAdapter());
     $select->from($this->info('name'), array('active', 'user_id'));
     $select->where('resource_id = ?', $resource->getIdentity());
     $select->where('active = ?', '1');
     $users = array();
     foreach ($select->query()->fetchAll() as $data) {
         $users[] = $data['user_id'];
     }
     $users = array_values(array_unique($users));
     return Engine_Api::_()->getItemMulti('user', $users);
 }