PHP Where::like方法代码示例

 public function getTranslationListForSearch($filterParams = [])
 {
     $where = new Where();
     $hasPageQuery = false;
     if ((int) $filterParams["id_translation"] > 0) {
         $where->equalTo($this->getTable() . '.id', $filterParams["id_translation"]);
     } else {
         if ($filterParams["srch_txt"] != '') {
             $where->like($this->getTable() . '.en_html_clean', '%' . strip_tags(trim($filterParams["srch_txt"])) . '%');
         }
         if ($filterParams['category'] == 1 && isset($filterParams['un_type'][0])) {
             $pages = explode(',', $filterParams['un_type'][0]);
             $where->in('pr.page_id', $pages);
         }
         if ($filterParams["description"] != '') {
             $where->like($this->getTable() . '.description', '%' . strip_tags(trim($filterParams["description"])) . '%');
         }
     }
     $columns = array('id' => 'id', 'content' => 'en', 'description' => 'description', 'page_name' => new Expression("GROUP_CONCAT(p.name SEPARATOR ', ')"));
     $sortColumns = ['id', 'en'];
     $result = $this->fetchAll(function (Select $select) use($columns, $sortColumns, $where) {
         $select->columns($columns);
         $select->join(['pr' => DbTables::TBL_UN_TEXTLINE_PAGE_REL], $this->getTable() . '.id = pr.textline_id', []);
         $select->join(['p' => DbTables::TBL_PAGES], 'p.id = pr.page_id', []);
         if ($where !== null) {
             $select->where($where);
         }
         $select->group($this->getTable() . '.id');
         $select->quantifier(new Expression('SQL_CALC_FOUND_ROWS'));
     });
     $statement = $this->adapter->query('SELECT FOUND_ROWS() as count');
     $result2 = $statement->execute();
     $row = $result2->current();
     return ['result' => $result, 'count' => $row['count']];
 }

 /**
  * Construct Where object from query parameters
  *
  * @param array $filterParams
  * @param bool $testApartments
  * @return Where
  */
 public function constructWhereFromFilterParams($filterParams, $testApartments = true)
 {
     $auth = $this->getServiceLocator()->get('library_backoffice_auth');
     $hasDevTestRole = $auth->hasRole(Roles::ROLE_DEVELOPMENT_TESTING);
     $where = new Where();
     $table = DbTables::TBL_APARTMENTS;
     $productStatusGroups = Objects::getProductStatusGroups();
     if (!$testApartments || !$hasDevTestRole) {
         $where->expression($table . '.id NOT IN(' . Constants::TEST_APARTMENT_1 . ', ' . Constants::TEST_APARTMENT_2 . ')', []);
     }
     if (isset($filterParams["status"]) && $filterParams["status"] != '0') {
         $statusGroup = $productStatusGroups[$filterParams["status"]];
         $where->in($table . ".status", $statusGroup);
     }
     if (isset($filterParams["building_id"]) && $filterParams["building_id"] != '0') {
         $where->expression($table . '.id IN (SELECT `apartment_id` FROM ' . DbTables::TBL_APARTMENT_GROUP_ITEMS . ' JOIN ' . DbTables::TBL_APARTMENT_GROUPS . ' ON `apartment_group_id` = ' . DbTables::TBL_APARTMENT_GROUPS . '.id WHERE ' . DbTables::TBL_APARTMENT_GROUPS . '.id = ' . $filterParams['building_id'] . ' ) ', []);
     }
     if (isset($filterParams["address"]) && $filterParams["address"] != '') {
         $addressQuery = $filterParams["address"];
         $nestedWhere = new Where();
         $nestedWhere->like($table . '.name', '%' . $addressQuery . '%')->or->like('det1.name', '%' . $addressQuery . '%')->or->like('det2.name', '%' . $addressQuery . '%')->or->like($table . '.address', '%' . $addressQuery . '%')->or->like($table . '.unit_number', '%' . $addressQuery . '%')->or->like($table . '.postal_code', '%' . $addressQuery . '%');
         $where->addPredicate($nestedWhere);
     }
     if (isset($filterParams['createdDate']) && $filterParams['createdDate'] !== '') {
         $createdDate = explode(' - ', $filterParams['createdDate']);
         $where->between($table . '.create_date', $createdDate['0'], $createdDate['1']);
     }
     return $where;
 }

 public function search($str)
 {
     $select = new Select('video');
     $spec = new Where();
     $spec->like('title', '% ' . $str . ' %');
     $select->where($spec);
     $select->where->OR->like('title', $str . ' %');
     $select->where->OR->like('title', '% ' . $str);
     //$select->where(array('title'=>$str));
     //$select->where(array('catelog'=>$title,'description'=>$description));
     $select->order('id ASC');
     /*
                  // create a new result set based on the Album entity
                  $resultSetPrototype = new ResultSet();
                  $resultSetPrototype->setArrayObjectPrototype(new Search());
                  // create a new pagination adapter object
                  $paginatorAdapter = new DbSelect(
                      // our configured select object
                      $select,
                      // the adapter to run it against
                      $this->tableGateway->getAdapter(),
                      // the result set to hydrate
                      $resultSetPrototype
                  );
                  $paginator = new Paginator($paginatorAdapter);
                 
                  return $paginator;
     */
     return $this->tableGateway->selectWith($select);
 }

 public function getUsersAndAbove(bool $paginated, $name = '', $roles = [])
 {
     $select = new Select('account');
     $where = new Where();
     if ($roles) {
         $sub = $where->nest();
         for ($i = 0; $i < count($roles); $i++) {
             $sub->equalTo('role', $roles[$i]);
             if ($i < count($roles) - 1) {
                 $sub->or;
             }
         }
         $sub->unnest();
     } else {
         $where->greaterThan('role', '0');
         $where->lessThan('role', '32');
     }
     if ($name) {
         $where->like('name', '%' . $name . '%');
     }
     $select->where($where)->order('name ASC');
     if ($paginated) {
         $resultSetPrototype = new ResultSet();
         $resultSetPrototype->setArrayObjectPrototype(new Account());
         $paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype);
         return new Paginator($paginatorAdapter);
     }
     return $this->tableGateway->select($select);
 }

 public function fetchAll_user($user)
 {
     $user_new = $user;
     $where = new Where();
     $where->like('id_uzytkownika', $user_new);
     $resultSet = $this->tableGateway->select(array($where));
     return $resultSet;
 }

 /**
  * @param $params
  * @param $userId
  * @return array
  */
 public function getAllBudgets($params, $userId)
 {
     $this->resultSetPrototype->setArrayObjectPrototype(new \ArrayObject());
     $where = new Where();
     if (isset($params['name']) && $params['name']) {
         $where->like($this->getTable() . '.name', $params['name'] . '%');
     }
     if (isset($params['status']) && $params['status']) {
         $where->equalTo($this->getTable() . '.status', $params['status']);
     }
     if (isset($params['user']) && $params['user']) {
         $where->equalTo($this->getTable() . '.user_id', $params['user']);
     }
     if (isset($params['period']) && $params['period']) {
         $dateRange = Helper::refactorDateRange($params['period']);
         $where->greaterThanOrEqualTo($this->getTable() . '.to', $dateRange['date_from']);
         $where->lessThanOrEqualTo($this->getTable() . '.from', $dateRange['date_to']);
     }
     if (isset($params['frozen']) && $params['frozen'] >= 0) {
         $where->equalTo($this->getTable() . '.frozen', $params['frozen']);
     }
     if (isset($params['archived']) && $params['archived'] >= 0) {
         $where->equalTo($this->getTable() . '.archived', $params['archived']);
     }
     if ($userId) {
         $where->equalTo($this->getTable() . '.user_id', $userId);
     }
     if (isset($params['department']) && $params['department'] >= 0) {
         $where->equalTo($this->getTable() . '.department_id', $params['department']);
     }
     if (isset($params['country']) && $params['country'] >= 0) {
         $where->equalTo($this->getTable() . '.country_id', $params['country']);
     }
     if (isset($params['global']) && $params['global'] >= 0) {
         $where->equalTo($this->getTable() . '.is_global', $params['global']);
     }
     $offset = $params['iDisplayStart'];
     $limit = $params['iDisplayLength'];
     $sortCol = $params['iSortCol_0'];
     $sortDir = $params['sSortDir_0'];
     $result = $this->fetchAll(function (Select $select) use($offset, $limit, $sortCol, $sortDir, $where) {
         $sortColumns = ['status', 'name', 'department_name', 'from', 'amount', 'balance', 'user_name'];
         $select->columns(['id', 'name', 'from', 'to', 'amount', 'description', 'status', 'user_id', 'department_id', 'country_id', 'is_global', 'balance']);
         $select->join(['users' => DbTables::TBL_BACKOFFICE_USERS], $this->getTable() . '.user_id = users.id', ['user_name' => new Expression('CONCAT(firstname, " ", lastname)')], Select::JOIN_LEFT);
         $select->join(['teams' => DbTables::TBL_TEAMS], $this->getTable() . '.department_id = teams.id', ['department_name' => 'name'], Select::JOIN_LEFT);
         $select->where($where);
         $select->group($this->getTable() . '.id')->order($sortColumns[$sortCol] . ' ' . $sortDir)->offset((int) $offset)->limit((int) $limit);
         $select->quantifier(new Expression('SQL_CALC_FOUND_ROWS'));
     });
     $statement = $this->adapter->query('SELECT FOUND_ROWS() as total');
     $resultCount = $statement->execute();
     $row = $resultCount->current();
     $total = $row['total'];
     return ['result' => $result, 'total' => $total];
 }

 public function fetch($filter = null)
 {
     $select = new Select();
     $select->from('contact_company')->order('name');
     if ($filter !== null) {
         $where = new Where();
         $where->like('name', '%' . $filter . '%')->OR->like('display_name', '%' . $filter . '%');
         return $this->select($select->where($where));
     } else {
         return $this->select($select);
     }
 }

 /**
  * @param string $filename
  * @param string $match_pattern
  * @return Paginator
  */
 public function matchingFilename($filename, $match_pattern = self::MATCH_CONTAINS)
 {
     $select = $this->getTableGateway()->getSql()->select();
     $select->join(['file' => 'commit_file_status'], 'commit.commit_id = file.commit_id', []);
     $where = new Where();
     $where->like('file.name', sprintf($match_pattern, $filename));
     $select->where($where);
     $selectAdapter = new DbSelect($select, $this->adapter, $this->getTableGateway()->getResultSetPrototype());
     $paginator = new CommitPaginator($selectAdapter);
     $paginator->setCommitFileStatusMapper($this->getCommitFileStatusMapper());
     $paginator->setRepositoryMapper($this->getRepositoryMapper());
     return $paginator;
 }

 public function getSerieTitre($titre)
 {
     $titre = (string) $titre;
     $where = new Where();
     $where->like("titre", "%" . $titre . "%");
     $rowset = $this->tableGateway->select($where);
     $row = $rowset->current();
     if ($row) {
         return $row;
     } else {
         throw new \Exception("Could not find serie {$titre}");
     }
 }

 public function search(array $params)
 {
     $where = new Where();
     if (isset($params['product_type_id'])) {
         $where->equalTo('product_type_id', $params['product_type_id']);
     }
     if (isset($params['query']) && trim($params['query'])) {
         $where->like('name', "%{$params['query']}%");
     } else {
         //todo: make this less hacky
         return array();
     }
     $select = $this->getSelect()->where($where);
     return $this->selectManyModels($select);
 }

 public function fetchByDirector($director)
 {
     $select = $this->tableGateway->getSql()->select();
     $where = new WherePredicate();
     $select->join('tbldirectors', $this->tableGateway->getTable() . '.directorId', array('firstName', 'lastName', 'dateOfBirth', 'nationality'), \Zend\Db\Sql\Select::JOIN_LEFT);
     if (is_int($director)) {
         $where->equalTo($this->tableGateway->getTable() . '.directorId', $director);
     }
     if (is_string($director)) {
         $where->like('firstName', $director . '%')->or->like('lastName', $director . '%');
     }
     $select->where($where)->order('releaseDate DESC');
     $results = $this->tableGateway->selectWith($select);
     return $results->buffer();
 }

 /**
  * @param $params
  * @return array
  */
 public function getAllEspms($params)
 {
     $this->setEntity(new \ArrayObject());
     $where = new Where();
     if (isset($params['transaction_account']) && $params['transaction_account']) {
         $where->equalTo($this->getTable() . '.transaction_account_id', $params['transaction_account']);
     }
     if (isset($params['account']) && $params['account']) {
         $where->equalTo($this->getTable() . '.external_account_id', $params['account']);
     }
     if (isset($params['type']) && $params['type']) {
         $where->equalTo($this->getTable() . '.type', $params['type']);
     }
     if (isset($params['status']) && $params['status']) {
         $where->equalTo($this->getTable() . '.status', $params['status']);
     }
     if (isset($params['reason']) && $params['reason']) {
         $where->like($this->getTable() . '.reason', '%' . $params['reason'] . '%');
     }
     if (isset($params['amount']) && $params['amount']) {
         $where->equalTo($this->getTable() . '.amount', $params['amount']);
     }
     if (isset($params['is_archived']) && $params['is_archived'] != 2) {
         $where->equalTo($this->getTable() . '.is_archived', $params['is_archived']);
     }
     $offset = $params['iDisplayStart'];
     $limit = $params['iDisplayLength'];
     $sortCol = $params['iSortCol_0'];
     $sortDir = $params['sSortDir_0'];
     $this->setEntity(new \ArrayObject());
     $result = $this->fetchAll(function (Select $select) use($offset, $limit, $sortCol, $sortDir, $where) {
         $sortColumns = ['transaction_account_id', 'external_account_id', 'type', 'status', 'amount', 'creator_id'];
         $select->columns(['id', 'transaction_account_id', 'external_account_id', 'type', 'status', 'amount', 'creator_id', 'supplier_name' => new Expression("ifnull(\n                            ifnull(b_p.partner_name, s.name),\n                            concat(b_u.firstname, ' ', b_u.lastname)\n                    )")]);
         $select->join(['users' => DbTables::TBL_BACKOFFICE_USERS], $this->getTable() . '.creator_id = users.id', ['creator' => new Expression('CONCAT(users.firstname, " ", users.lastname)')], Select::JOIN_LEFT)->join(['external_account' => DbTables::TBL_EXTERNAL_ACCOUNT], $this->getTable() . '.external_account_id = external_account.id', ['external_account_name' => 'name'], Select::JOIN_LEFT)->join(['t_a' => DbTables::TBL_TRANSACTION_ACCOUNTS], $this->getTable() . '.transaction_account_id = t_a.id', ['transaction_type' => 'type'], Select::JOIN_LEFT)->join(['b_u' => DbTables::TBL_BACKOFFICE_USERS], new Expression('t_a.holder_id = b_u.id AND t_a.type=' . Account::TYPE_PEOPLE), [], Select::JOIN_LEFT)->join(['s' => DbTables::TBL_SUPPLIERS], new Expression('t_a.holder_id = s.id AND t_a.type=' . Account::TYPE_SUPPLIER), [], Select::JOIN_LEFT)->join(['b_p' => DbTables::TBL_BOOKING_PARTNERS], new Expression('t_a.holder_id = b_p.gid AND t_a.type=' . Account::TYPE_PARTNER), [], Select::JOIN_LEFT)->join(['currency' => DbTables::TBL_CURRENCY], $this->getTable() . '.currency_id = currency.id', ['currency_code' => 'code'], Select::JOIN_LEFT);
         $select->where($where);
         $select->group($this->getTable() . '.id')->order($sortColumns[$sortCol] . ' ' . $sortDir)->offset((int) $offset)->limit((int) $limit);
         $select->quantifier(new Expression('SQL_CALC_FOUND_ROWS'));
     });
     $statement = $this->adapter->query('SELECT FOUND_ROWS() as total');
     $resultCount = $statement->execute();
     $row = $resultCount->current();
     $total = $row['total'];
     return ['result' => $result, 'total' => $total];
 }

 public function getPlayerByName($name)
 {
     $adapter = $this->tableGateway->getAdapter();
     $where = new Where();
     $sql = new Sql($adapter);
     $select = $sql->select();
     $select->from('player');
     $select->join('haircolor', 'haircolor.id = player.hairColorId ', array('haircolor' => 'name'), $type = self::JOIN_LEFT);
     $select->join('skincolor', 'skincolor.id = player.skincolorId ', array('skincolor' => 'name'), $type = self::JOIN_LEFT);
     // $select->columns(array('name'));
     $where->like('player.name', '%' . $name . '%');
     $select->where($where);
     $statement = $sql->prepareStatementForSqlObject($select);
     $rowset = $statement->execute();
     $row = $rowset->current();
     //if (!$row) {
     // throw new \Exception("Could not find row $id");
     //}
     return $row;
 }

 /**
  * Get by params
  *
  * @param array $params
  * @return array|\ArrayObject|null
  */
 public function getExternalAccountsByParams($params = [])
 {
     $result = $this->fetchAll(function (Select $select) use($params) {
         $where = new Where();
         $where->equalTo('transaction_account_id', $params['transactionAccountID']);
         if (isset($params['status']) && $params['status'] > 0) {
             $where->equalTo('status', $params['status']);
         }
         if (!empty($params['sort'])) {
             foreach ($params['sort'] as $col => $dir) {
                 $select->order($col . ' ' . $dir);
             }
         }
         if (!empty($params['search'])) {
             $where->like('name', '%' . $params['search'] . '%');
         }
         $select->where($where)->order('id DESC');
     });
     return $result;
 }

 public function searchByNickname($name)
 {
     /*
     SELECT id, nickname, MAX(state) 'friendship' FROM fg_users u
     LEFT JOIN fg_friends f ON u.id = f.user_one OR u.id = f.user_two
     WHERE UCASE(nickname) LIKE "%LI%"
     GROUP BY u.id*/
     $select = new Select();
     // $subSelect = new Select();
     $select->from(array('u' => 'fg_users'))->columns(array('id', 'nickname'));
     // $select->from(array('u' => 'fg_users'), array('MAX(state)', 'UCASE(nickname)') );
     // $select->columns(array('id' => 'id', 'nickname' => 'nickname', 'friendship' => 'friendship'));
     // $select->join(array('f' => 'fg_friends'),	'u.id = f.user_one OR u.id = f.user_two', array(), 'inner');
     $where = new Where();
     $where->like(new Expression('UCASE(nickname)'), '%' . strtoupper($name) . '%');
     $where->AND->notEqualTo('id', $this->user_id);
     $select->where($where);
     // $select->group('id');
     $statement = $this->tableGateway->getSql()->prepareStatementForSqlObject($select);
     $resultSet = $statement->execute();
     return $resultSet;
 }