PHP Select::where方法代码示例

 protected function __listTables($cache = TRUE)
 {
     if ($cache && sizeof($this->tables)) {
         return $this->tables;
     }
     $select = new Select('information_schema.TABLES');
     $select->where(array(array('field' => 'TABLE_SCHEMA', 'value' => $this->params['name'])));
     return $this->tables = $select->execute('\\apf\\db\\mysql5\\Table', $smart = FALSE);
 }

 public function testSelectComWheresComOrEAnd()
 {
     $select = new Select();
     $where = new Where();
     $where->set('id', '=', 1);
     $where->set('last_name', '=', 1, 'or');
     $where->set('name', '=', 'Erik', 'or');
     $select->table = 'pages';
     $select->where($where);
     $sql = $select->sql();
     $this->assertEquals('SELECT * FROM pages WHERE `id`=:id and `last_name`=:last_name or `name`=:name;', $sql);
 }

 /**
  * Cláusula WHERE
  *
  * @example database::Query::where
  * <code>
  * <?php
  *     # vide Query::factory
  *     $query = Query::factory(..);
  *
  *     # foo = 'foo'
  *     $filter = $query->foo->equals('foo');
  *
  *     # aplica condicao a query
  *     $query->where($filter);
  * ?>
  * </code>
  *
  * {@ineritdoc}
  * */
 public function where(RelationalAbstract $conditional, $operator = Where::T_AND_OPERATOR)
 {
     if (NULL == $this->_where) {
         # a Query e' requerida para permitir o encadeamento de filtro
         # ex.: $query->where()->having()..->otherPossibleFilter();
         $this->_where = Where::factory($this);
         # registra WHERE no SELECT
         $this->_select->where($this->_where);
     }
     # adiciona o filtro ao WHERE
     $this->_where->add($conditional, $operator);
     # retorna regerencia de WHERE para o objeto que invocou o metodo
     return $this;
 }

 protected static function getAllOnWhere($table_name, $class, $where = false, $values = false, $order = false, $ask = true, $count = false, $offset = false)
 {
     $select = new Select();
     $select->from($table_name, "*");
     if ($where) {
         $select->where($where, $values);
     }
     if ($order) {
         $select->order($order, $ask);
     } else {
         $select->order("id");
     }
     if ($count) {
         $select->limit($count, $offset);
     }
     $data = self::$db->select($select);
     return AbstractObjectDB::buildMultiple($class, $data);
 }

 /**
  * Builds where clause, for filtering on simple list mode
  *
  * @param Select $select Original select
  *
  * @return string SQL WHERE clause
  */
 private function _buildWhereClause($select)
 {
     global $zdb, $login;
     $field = 'date_debut_cotis';
     switch ($this->_date_field) {
         case self::DATE_RECORD:
             $field = 'date_enreg';
             break;
         case self::DATE_END:
             $field = 'date_fin_cotis';
             break;
         case self::DATE_BEGIN:
         default:
             $field = 'date_debut_cotis';
             break;
     }
     try {
         if ($this->_start_date_filter != null) {
             $d = new \DateTime($this->_start_date_filter);
             $select->where->greaterThanOrEqualTo($field, $d->format('Y-m-d'));
         }
         if ($this->_end_date_filter != null) {
             $d = new \DateTime($this->_end_date_filter);
             $select->where->lessThanOrEqualTo($field, $d->format('Y-m-d'));
         }
         if ($this->_payment_type_filter != null) {
             $select->where->equalTo('type_paiement_cotis', $this->_payment_type_filter);
         }
         if ($this->_from_transaction !== false) {
             $select->where->equalTo(Transaction::PK, $this->_from_transaction);
         }
         if ($this->_max_amount !== null && is_int($this->_max_amount)) {
             $select->where('(montant_cotis <= ' . $this->_max_amount . ' OR montant_cotis IS NULL)');
         }
         if (!$login->isAdmin() && !$login->isStaff()) {
             //non staff members can only view their own contributions
             $select->where(array('a.' . Adherent::PK => $login->id));
         } else {
             if ($this->_filtre_cotis_adh != null) {
                 $select->where('a.' . Adherent::PK . ' = ' . $this->_filtre_cotis_adh);
             }
         }
         if ($this->_filtre_transactions === true) {
             $select->where('a.trans_id IS NULL');
         }
     } catch (\Exception $e) {
         Analog::log(__METHOD__ . ' | ' . $e->getMessage(), Analog::WARNING);
     }
 }

 /**
  * Builds where clause, for filtering on simple list mode
  *
  * @param Select $select Original select
  *
  * @return string SQL WHERE clause
  */
 private function _buildWhereClause($select)
 {
     global $zdb, $login;
     try {
         if ($this->_start_date_filter != null) {
             $d = new \DateTime($this->_start_date_filter);
             $select->where->greaterThanOrEqualTo('trans_date', $d->format('Y-m-d'));
         }
         if ($this->_end_date_filter != null) {
             $d = new \DateTime($this->_end_date_filter);
             $select->where->lessThanOrEqualTo('trans_date', $d->format('Y-m-d'));
         }
         if (!$login->isAdmin() && !$login->isStaff()) {
             //non staff members can only view their own transactions
             $select->where('t.' . Adherent::PK . ' = ' . $login->id);
         } else {
             if ($this->_filtre_cotis_adh != null) {
                 $select->where('t.' . Adherent::PK . ' = ' . $this->_filtre_cotis_adh);
             }
         }
     } catch (\Exception $e) {
         Analog::log(__METHOD__ . ' | ' . $e->getMessage(), Analog::WARNING);
     }
 }

 /**
  * Generate WHERE clause from user-supplied string or array
  *
  * @param  string|array $where  OPTIONAL An SQL WHERE clause.
  * @return Select
  */
 protected function _where(Select $select, $where)
 {
     $where = (array) $where;
     foreach ($where as $key => $val) {
         // is $key an int?
         if (is_int($key)) {
             // $val is the full condition
             $select->where($val);
         } else {
             // $key is the condition with placeholder,
             // and $val is quoted into the condition
             $select->where($key, $val);
         }
     }
     return $select;
 }

 /**
  * Builds where clause, for filtering on simple list mode
  *
  * @param Select $select Original select
  *
  * @return string SQL WHERE clause
  */
 private function _buildWhereClause($select)
 {
     global $zdb, $login;
     try {
         if ($this->_filters->email_filter == self::FILTER_W_EMAIL) {
             $select->where('email_adh != \'\'');
         }
         if ($this->_filters->email_filter == self::FILTER_WO_EMAIL) {
             $select->where('(email_adh = \'\' OR email_adh IS NULL)');
         }
         if ($this->_filters->filter_str != '') {
             $token = $zdb->platform->quoteValue('%' . strtolower($this->_filters->filter_str) . '%');
             switch ($this->_filters->field_filter) {
                 case self::FILTER_NAME:
                     if (TYPE_DB === 'pgsql') {
                         $sep = " || ' ' || ";
                         $pre = '';
                         $post = '';
                     } else {
                         $sep = ', " ", ';
                         $pre = 'CONCAT(';
                         $post = ')';
                     }
                     //$sep = ( TYPE_DB === 'pgsql' ) ? " || ' ' || " : ', " ", ';
                     $select->where('(' . $pre . 'LOWER(nom_adh)' . $sep . 'LOWER(prenom_adh)' . $sep . 'LOWER(pseudo_adh)' . $post . ' LIKE ' . $token . ' OR ' . $pre . 'LOWER(prenom_adh)' . $sep . 'LOWER(nom_adh)' . $sep . 'LOWER(pseudo_adh)' . $post . ' LIKE ' . $token . ')');
                     break;
                 case self::FILTER_COMPANY_NAME:
                     $select->where('LOWER(societe_adh) LIKE ' . $token);
                     break;
                 case self::FILTER_ADDRESS:
                     $select->where('(' . 'LOWER(adresse_adh) LIKE ' . $token . ' OR ' . 'LOWER(adresse2_adh) LIKE ' . $token . ' OR ' . 'cp_adh LIKE ' . $token . ' OR ' . 'LOWER(ville_adh) LIKE ' . $token . ' OR ' . 'LOWER(pays_adh) LIKE ' . $token . ')');
                     break;
                 case self::FILTER_MAIL:
                     $select->where('(' . 'LOWER(email_adh) LIKE ' . $token . ' OR ' . 'LOWER(url_adh) LIKE ' . $token . ' OR ' . 'LOWER(msn_adh) LIKE ' . $token . ' OR ' . 'LOWER(icq_adh) LIKE ' . $token . ' OR ' . 'LOWER(jabber_adh) LIKE ' . $token . ')');
                     break;
                 case self::FILTER_JOB:
                     $select->where('LOWER(prof_adh) LIKE ' . $token);
                     break;
                 case self::FILTER_INFOS:
                     $more = '';
                     if ($login->isAdmin() || $login->isStaff()) {
                         $more = ' OR LOWER(info_adh) LIKE ' . $token;
                     }
                     $select->where('(LOWER(info_public_adh) LIKE ' . $token . $more . ')');
                     break;
                 case self::FILTER_NUMBER:
                     $select->where->equalTo('a.id_adh', $this->_filters->filter_str);
                     break;
             }
         }
         if ($this->_filters->membership_filter) {
             switch ($this->_filters->membership_filter) {
                 case self::MEMBERSHIP_NEARLY:
                     $now = new \DateTime();
                     $duedate = new \DateTime();
                     $duedate->modify('+1 month');
                     $select->where->greaterThan('date_echeance', $now->format('Y-m-d'))->lessThan('date_echeance', $duedate->format('Y-m-d'));
                     break;
                 case self::MEMBERSHIP_LATE:
                     $select->where->lessThan('date_echeance', date('Y-m-d', time()))->equalTo('bool_exempt_adh', new Expression('false'));
                     break;
                 case self::MEMBERSHIP_UP2DATE:
                     $select->where('(' . 'date_echeance > \'' . date('Y-m-d', time()) . '\' OR bool_exempt_adh=true)');
                     break;
                 case self::MEMBERSHIP_NEVER:
                     $select->where('date_echeance IS NULL')->where('bool_exempt_adh = false');
                     break;
                 case self::MEMBERSHIP_STAFF:
                     $select->where->lessThan('p.priorite_statut', self::NON_STAFF_MEMBERS);
                     break;
                 case self::MEMBERSHIP_ADMIN:
                     $select->where->equalTo('bool_admin_adh', true);
                     break;
                 case self::MEMBERSHIP_NONE:
                     $select->where->equalTo('a.id_statut', Status::DEFAULT_STATUS);
                     break;
             }
         }
         if ($this->_filters->account_status_filter) {
             switch ($this->_filters->account_status_filter) {
                 case self::ACTIVE_ACCOUNT:
                     $select->where('activite_adh=true');
                     break;
                 case self::INACTIVE_ACCOUNT:
                     $select->where('activite_adh=false');
                     break;
             }
         }
         if ($this->_filters->group_filter) {
             $select->join(array('g' => PREFIX_DB . Group::GROUPSUSERS_TABLE), 'a.' . Adherent::PK . '=g.' . Adherent::PK, array('*'), $select::JOIN_LEFT)->join(array('gs' => PREFIX_DB . Group::TABLE), 'gs.' . Group::PK . '=g.' . Group::PK, array('*'), $select::JOIN_LEFT)->where('(g.' . Group::PK . ' = ' . $this->_filters->group_filter . ' OR gs.parent_group = NULL OR gs.parent_group = ' . $this->_filters->group_filter . ')');
         }
         if ($this->_filters instanceof AdvancedMembersList) {
             if ($this->_filters->rbirth_date_begin || $this->_filters->rbirth_date_end) {
//.........这里部分代码省略.........