PHP Where::nest方法代码示例

 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);
 }

 /**
  * Creates a new square manager object.
  *
  * Preloads all available squares from the database.
  *
  * @param SquareTable $squareTable
  * @param SquareMetaTable $squareMetaTable
  * @param string $locale
  */
 public function __construct(SquareTable $squareTable, SquareMetaTable $squareMetaTable, $locale)
 {
     $this->squareTable = $squareTable;
     $this->squareMetaTable = $squareMetaTable;
     $select = $squareTable->getSql()->select();
     $select->order('priority ASC');
     $resultSet = $squareTable->selectWith($select);
     $this->squares = SquareFactory::fromResultSet($resultSet);
     /* Load square meta data */
     if ($this->squares) {
         $sids = array();
         foreach ($this->squares as $square) {
             $sids[] = $square->need('sid');
         }
         reset($this->squares);
         $metaWhere = new Where();
         $metaWhere->in('sid', $sids);
         $metaWhere->and;
         $metaWhereNested = $metaWhere->nest();
         $metaWhereNested->isNull('locale');
         $metaWhereNested->or;
         $metaWhereNested->equalTo('locale', $locale);
         $metaWhereNested->unnest();
         $metaSelect = $this->squareMetaTable->getSql()->select();
         $metaSelect->where($metaWhere);
         $metaSelect->order('locale ASC');
         $metaResultSet = $this->squareMetaTable->selectWith($metaSelect);
         SquareFactory::fromMetaResultSet($this->squares, $metaResultSet);
         /* Prepare active squares */
         $this->activeSquares = $this->getAllVisible();
     }
 }

 /**
  * Gets the entity meta select predicate.
  *
  * @param string $id
  * @param array $eids
  * @return Select
  */
 protected function getByMetaSelect($id, array $eids)
 {
     $where = new Where();
     $where->in($id, $eids);
     $where->and;
     $nestedWhere = $where->nest();
     $nestedWhere->equalTo('locale', $this->locale);
     $nestedWhere->or;
     $nestedWhere->isNull('locale');
     $nestedWhere->unnest();
     $metaSelect = $this->entityMetaTable->getSql()->select();
     $metaSelect->where($where);
     $metaSelect->order('locale ASC');
     return $metaSelect;
 }

 /**
  * Get the list of shifts for the given Manager ID.  If a start_time and/or end_time is provided, will restrict the list of shifts to only shifts within those datetime
  * 
  * @param $id Shift ID
  * */
 public function getList($id = false)
 {
     $shifts_gateway = Shifts::factory($this->getServiceLocator());
     $manager_id = $this->params()->fromRoute('manager_id');
     // shifts within this start/end time
     $start_time = $this->params()->fromQuery('start_time');
     $end_time = $this->params()->fromQuery('end_time');
     if (!empty($start_time)) {
         $start_time = strtotime($start_time);
         if ($start_time === false || $start_time == 0) {
             return $this->responseError(400, 'invalid start time');
         }
         $start_time = date('Y-m-d H:i:s', $start_time);
     }
     if (!empty($end_time)) {
         $end_time = strtotime($end_time);
         if ($end_time === false || $end_time == 0) {
             return $this->responseError(400, 'invalid end time');
         }
         $end_time = date('Y-m-d H:i:s', $end_time);
     }
     // select all shifts for the given manager
     $result = $shifts_gateway->select(function ($select) use($manager_id, $start_time, $end_time, $shifts_gateway) {
         $where = new Where();
         $where->equalTo('manager_id', $manager_id);
         $where2 = $where->nest();
         if (!empty($start_time)) {
             $nest = $where2->or->nest();
             $nest->lessThanOrEqualTo('start_time', $start_time);
             $nest->greaterThan('end_time', $start_time);
         }
         if (!empty($end_time)) {
             $nest = $where2->or->nest();
             $nest->lessThanOrEqualTo('start_time', $end_time);
             $nest->greaterThan('end_time', $end_time);
         }
         $select->where($where);
         //echo $shifts_gateway->getSql()->getSqlStringForSqlObject($select);
     });
     return $result;
 }

 public function getTimeline()
 {
     $select = new Select();
     $select->from(array('fl' => 'fg_flicks'));
     $select->columns(array('*'));
     $select->join(array('fr' => 'fg_friends'), 'fl.owner = fr.user_one OR fl.owner = fr.user_two', array());
     $select->join(array('u' => 'fg_users'), 'fl.owner = u.id', array("nickname"));
     $where = new Where();
     $or = $where->nest();
     $or->equalTo('fr.user_one', $this->user_id);
     $or->OR->equalTo('fr.user_two', $this->user_id);
     $or->unnest();
     $where->AND->equalTo('state', 1);
     $where->AND->notEqualTo('fl.owner', $this->user_id);
     $select->where($where);
     $select->order(array('id DESC'));
     $select->group("id");
     $statement = $this->tableGateway->getSql()->prepareStatementForSqlObject($select);
     $resultSet = $statement->execute();
     // var_dump($resultSet->current());
     // exit;
     return $resultSet;
 }

 /**
  * @param bool|int $managerId
  * @param bool $active
  * @return \DDD\Domain\User\User[]|array[]|\ArrayObject
  */
 public function getPeopleList($managerId = false, $active = true, $withoutExternalUsers = true, $countryId = false)
 {
     return $this->fetchAll(function (Select $select) use($managerId, $active, $withoutExternalUsers, $countryId) {
         $select->columns(['id', 'firstname', 'lastname', 'manager_id', 'email']);
         $where = new Where();
         $where->equalTo('system', 0);
         if ($withoutExternalUsers) {
             $where->equalTo('external', 0);
         }
         if ($countryId) {
             $where->equalTo('country_id', $countryId);
         }
         if ($active || $managerId) {
             $nest = $where->nest();
         }
         if ($active) {
             $nest->equalTo('disabled', 0);
         }
         if ($managerId) {
             $nest->or->equalTo('id', $managerId);
         }
         $select->where($where);
         $select->order('firstname ASC');
     });
 }

 public function getUserInfo($id)
 {
     // Get the Contact by email or userId to find the contactId
     // Instantiate the contact controller
     $ContactController = $this->getServiceLocator()->get('ContactController');
     $ContactController->setValidateOAuth(false);
     // Get the contact with the user_id associated to the Token
     $where = new Where();
     $where->nest()->equalTo(CONTACT_EMAIL, $id)->or->equalTo(CONTACT_USERID, $id);
     $contact = $ContactController->get($where);
     if ($contact->getVariable('status') == STATUS_FAILED) {
         throw new CommonException(array('messageId' => 'globals.query.record_not_found', 'parms' => array('id' => $id), EXCEPTION_ERRORKEY => 404, 'code' => 404));
     }
     $this->setValidateOAuth(false);
     $result = parent::get($contact->getVariable($ContactController->getTable()->getPk()));
     $this->setValidateOAuth(true);
     // TODO - Validate only one record found
     return $result;
 }

 /**
  * Gets all reservations within the specified datetime interval.
  *
  * Reservations are ordered by date and start time.
  *
  * @param DateTime $dateTimeStart
  * @param DateTime $dateTimeEnd
  * @param int $limit
  * @param int $offset
  * @param boolean $loadMeta
  * @return array
  */
 public function getInRange(DateTime $dateTimeStart, DateTime $dateTimeEnd, $limit = null, $offset = null, $loadMeta = true)
 {
     if ($dateTimeStart->format('Y-m-d') == $dateTimeEnd->format('Y-m-d')) {
         return $this->getByRange($dateTimeStart, $dateTimeEnd, $dateTimeStart->format('H:i'), $dateTimeEnd->format('H:i'), $limit, $offset, $loadMeta);
     }
     $where = new Where();
     $nested = $where->nest();
     $nested->equalTo('date', $dateTimeStart->format('Y-m-d'));
     $nested->greaterThan('time_end', $dateTimeStart->format('H:i'));
     $nested->unnest();
     $where->or;
     $nested = $where->nest();
     $nested->greaterThan('date', $dateTimeStart->format('Y-m-d'));
     $nested->lessThan('date', $dateTimeEnd->format('Y-m-d'));
     $nested->unnest();
     $where->or;
     $nested = $where->nest();
     $nested->equalTo('date', $dateTimeEnd->format('Y-m-d'));
     $nested->lessThan('time_start', $dateTimeEnd->format('H:i'));
     $nested->unnest();
     return $this->getBy($where, 'date, time_start ASC', $limit, $offset, $loadMeta);
 }

 public function getFriends()
 {
     $select = new Select();
     $select->from(array('f' => 'fg_friends'));
     $select->columns(array('*'));
     $where = new Where();
     $or = $where->nest();
     $or->equalTo('user_one', $this->user_id);
     $or->OR->equalTo('user_two', $this->user_id);
     $or->unnest();
     $where->AND->equalTo('state', 1);
     $select->where($where);
     $select->join(array('u1' => 'fg_users'), 'f.user_one = u1.id', array('u1_nickname' => 'nickname', 'u1_id' => 'id'));
     $select->join(array('u2' => 'fg_users'), 'f.user_two = u2.id', array('u2_nickname' => 'nickname', 'u2_id' => 'id'));
     $statement = $this->tableGateway->getSql()->prepareStatementForSqlObject($select);
     $resultSet = $statement->execute();
     $friends = array();
     foreach ($resultSet as $result) {
         $friend = new \stdClass();
         if ($result["u1_id"] != $this->user_id) {
             $friend->{"id"} = $result["u1_id"];
             $friend->{"nickname"} = $result["u1_nickname"];
         } else {
             $friend->{"id"} = $result["u2_id"];
             $friend->{"nickname"} = $result["u2_nickname"];
         }
         $friends[] = $friend;
     }
     return $friends;
 }

 /**
  * @param $filterParams
  * @return Where
  */
 protected function constructWhereFromFilterParams($filterParams)
 {
     $where = new Where();
     if (isset($filterParams['usage']) && $filterParams['usage'] != '') {
         $where->equalTo('lock_types.' . $filterParams['usage'], 1);
     }
     if (isset($filterParams['type_id']) && $filterParams['type_id'] != '' && (int) $filterParams['type_id'] != 0) {
         $where->equalTo(DbTables::TBL_LOCKS . '.type_id', (int) $filterParams['type_id']);
     }
     if (isset($filterParams['sSearch']) && !empty($filterParams['sSearch'])) {
         $searchQuery = trim(strip_tags($filterParams['sSearch']));
         if (!empty($searchQuery)) {
             $where->nest()->like(DbTables::TBL_LOCKS . '.name', '%' . $searchQuery . '%')->or->like(DbTables::TBL_LOCKS . '.description', '%' . $searchQuery . '%')->unnest();
         }
     }
     return $where;
 }