PHP Where::in方法代码示例

 public function findCourses($free, $disability, $child_care, $lat, $lng)
 {
     $sql = new Sql($this->tableGateway->getAdapter());
     $select = $sql->select();
     $select->from(array("co" => "course"))->join(array("cc" => "course_centre"), "co.id = cc.course_id")->join(array("ce" => "centre"), "ce.id = cc.centre_id", array("centre_id" => "id", "centreName" => "name", "location" => new Expression("AsWKT(location)"), "post_code", "address", "buses", "tube", "accebility", "accebility_condition", "other_information"));
     //especificar las columnas que queremos en el resultados de las consultas.
     $select->columns(array("id", "courseName" => "name", "class_type", "levels", "who_join", "how_join", "when_join", "how_long", "cost_free", "cost_condition", "times", "documentation_required", "contact_phone", "contact_email", "contact_person", "child_care", "child_condition", "organization_id", "other_information"));
     $where = new Where();
     //TODO terminar de poner prefijos a las tablas
     if (isset($free)) {
         if ($free == 'Yes') {
             $where->in("co.cost_free", array("y", "c"));
         } else {
             if ($free == 'No') {
                 $where->equalTo("co.cost_free", "n");
             }
         }
     }
     if (isset($disability)) {
         if ($disability == 'Yes') {
             $where->in("ce.accebility", array("y", "c"));
         } else {
             if ($disability == 'No') {
                 $where->equalTo("ce.accebility", "n");
             }
         }
     }
     if (isset($child_care)) {
         if ($child_care == 'Yes') {
             $where->in("co.child_care", array("y", "c"));
         } else {
             if ($child_care == 'No') {
                 $where->equalTo("co.child_care", "n");
             }
         }
     }
     //hacemos busquedas en 3km a la redonda, se puede convertir en un parametro
     $R = 6371;
     //radio de la tierra en km
     if (isset($lat) && isset($lng)) {
         $maxLat = $lat + rad2deg(1.5 / $R);
         $minLat = $lat - rad2deg(1.5 / $R);
         $maxLng = $lng + rad2deg(1.5 / $R / cos(deg2rad($lat)));
         $minLng = $lng - rad2deg(1.5 / $R / cos(deg2rad($lat)));
         $where->between(new Expression("X(ce.location)"), $minLat, $maxLat);
         $where->between(new Expression("y(ce.location)"), $minLng, $maxLng);
     }
     //TODO terminar con el resto de los filtros de busqueda
     $select->where($where);
     $statement = $sql->prepareStatementForSqlObject($select);
     $rowset = $statement->execute();
     return $rowset;
 }

 /**
  * 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();
     }
 }

 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']];
 }

 /**
  * @param array $typeList
  * @param bool|true $onlyActive
  * @return \DDD\Domain\Warehouse\Category\Category[]
  */
 public function getCategoriesByTypeList($typeList = [], $onlyActive = true, $selectedId = 0, $returnArray = false)
 {
     $entity = $this->getEntity();
     $this->setEntity(new \DDD\Domain\Warehouse\Category\Category());
     if ($returnArray) {
         $this->setEntity(new \ArrayObject());
     }
     $result = $this->fetchAll(function (Select $select) use($typeList, $onlyActive, $selectedId) {
         $select->columns(['id', 'name', 'type' => 'type_id', 'inactive']);
         $nestedWhere = new Where();
         $where = new Where();
         if (!empty($typeList)) {
             $nestedWhere->in('type_id', $typeList);
         }
         if ($onlyActive) {
             $nestedWhere->equalTo('inactive', AssetsCategoryService::CATEGORY_STATUS_ACTIVE);
         }
         if ($selectedId) {
             $where->equalTo('id', $selectedId)->orPredicate($nestedWhere);
         } else {
             $where = $nestedWhere;
         }
         $select->where($where)->order('type_id');
     });
     $this->setEntity($entity);
     return $result;
 }

 /**
  * 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;
 }

 /**
  * @param $backofficeUserId int
  * @return bool
  */
 public function unassignUserFromTasks($backofficeUserId)
 {
     /**
      * @var UserManager $backofficeUsersDao
      */
     $userManagerDao = $this->getServiceLocator()->get('dao_user_user_manager');
     $taskStaffDao = $this->getServiceLocator()->get('dao_task_staff');
     $userInfo = $userManagerDao->fetchOne(['id' => $backofficeUserId]);
     $where = new Where();
     $where->in('type', [TaskService::STAFF_HELPER, TaskService::STAFF_FOLLOWER])->equalTo('user_id', $backofficeUserId);
     $taskStaffDao->deleteWhere($where);
     $taskStaffs = $taskStaffDao->getStaffTasks($backofficeUserId);
     $managerId = $userInfo->getManager_id();
     $oneMonthBeforeNow = strtotime('-1 month');
     if ($taskStaffs->count()) {
         foreach ($taskStaffs as $taskStaff) {
             if ($taskStaff->getType() == TaskService::STAFF_VERIFIER) {
                 if (strtotime($taskStaff->getStartDate()) >= $oneMonthBeforeNow) {
                     $taskStaffDao->save(['user_id' => $managerId], ['id' => $taskStaff->getIId()]);
                 } else {
                     $taskStaffDao->save(['user_id' => UserService::AUTO_VERIFY_USER_ID], ['id' => $taskStaff->getIId()]);
                 }
             }
             if ($taskStaff->getType() == TaskService::STAFF_RESPONSIBLE) {
                 if ($taskStaff->getTaskStatus() != TaskService::STATUS_DONE && $taskStaff->getTaskStatus() != TaskService::STATUS_VERIFIED) {
                     $taskStaffDao->deleteWhere(['user_id' => $backofficeUserId, 'id' => $taskStaff->getIId()]);
                 }
             }
         }
     }
     return true;
 }

 /**
  * @param $ids
  * @param $limitAttempt
  * @return int
  */
 public function incrementAttempts($ids, $limitAttempt)
 {
     if ($limitAttempt) {
         $sql = "IF(attempts >= {$limitAttempt} , {$limitAttempt}, attempts + 1)";
     } else {
         $sql = "attempts + 1";
     }
     $where = new Where();
     $where->in('id', $ids);
     return $this->update(['attempts' => new Expression($sql)], $where);
 }

 public function getMostRecentListing($id = null)
 {
     $subSelect = new Select();
     $subSelect->columns(array('mostRecent' => new Expression('MAX(`listings_id`)')));
     $subSelect->from(ListingsTable::$tableName);
     $where = new Where();
     $where->in('listings_id', $subSelect);
     $select = new Select();
     $select->where($where)->from(ListingsTable::$tableName);
     return $this->selectWith($select)->current();
 }

 /**
  * @param array $roles
  * @return \Zend\Db\ResultSet\ResultSet
  */
 public function getRouteData(array $roles)
 {
     $results = $this->select(function (Select $select) use($roles) {
         $where = new Where();
         if (!empty($roles)) {
             $where->in('rp.roleId', $roles);
         } else {
             $where->expression(' 1 = ?', 0);
         }
         $select->join(array('rp' => 'tbl_route_permission'), 'tbl_route.routeId = rp.routeId', array('routeId'), Select::JOIN_INNER)->where($where)->quantifier(Select::QUANTIFIER_DISTINCT);
     });
     return $results;
 }

 public function getLatestListing()
 {
     $adapter = $this->getAdapter();
     $platform = $adapter->getPlatform();
     $quoteId = $platform->quoteIdentifier($this->listingsId);
     $select = new Select();
     $select->from($this->tableName);
     $expression = new Expression(sprintf('MAX(%s)', $quoteId));
     $subSelect = new Select();
     $subSelect->from($this->tableName)->columns(array($expression));
     $where = new Where();
     $where->in($this->listingsId, $subSelect);
     $select->where($where);
     return $this->selectWith($select)->current();
 }

 /**
  * 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;
 }

 public function getLatestTitle()
 {
     $adapter = $this->getAdapter();
     $platform = $adapter->getPlatform();
     $quoteId = $platform->quoteIdentifier($this->primaryKey);
     $select = new Select();
     $select->from(self::$tableName);
     $expression = new Expression(sprintf('MAX(%s)', $quoteId));
     $subSelect = new Select();
     $subSelect->from(self::$tableName)->columns(array($expression));
     $where = new Where();
     $where->in($this->primaryKey, $subSelect);
     $select->where($where);
     //echo $select->getSqlString($platform);
     return $this->selectWith($select)->current();
 }

 /**
  * Garbage Collection
  * Only delete sessions that have expired.
  *
  * @param int $maxlifetime
  * @return true
  */
 public function gc($maxlifetime)
 {
     $platform = $this->tableGateway->getAdapter()->getPlatform();
     $where = new Where();
     $where->lessThan($this->options->getModifiedColumn(), new Expression('(' . time() . ' - ' . $platform->quoteIdentifier($this->options->getLifetimeColumn()) . ')'));
     $rows = $this->tableGateway->select($where);
     $ids = [];
     /* @var \UthandoSessionManager\Model\Session $row */
     foreach ($rows as $row) {
         $ids[] = $row->{$this->options->getIdColumn()};
     }
     if (count($ids) > 0) {
         $where = new Where();
         $result = (bool) $this->tableGateway->delete($where->in($this->options->getIdColumn(), $ids));
     } else {
         $result = false;
     }
     return $result;
 }

 public function detailAction()
 {
     $storeID = $this->queryData['storeID'];
     $auctionStatus = $this->queryData['auctionStatus'];
     $order = $this->queryData['order'];
     $sort = $this->queryData['sort'];
     $storeCategoryID = $this->queryData['storeCategoryID'];
     $where = array('storeID' => $storeID);
     $storeInfo = $this->storeModel->fetch($where);
     $storeCategories = $this->storeCategoryModel->select(array('storeID' => $storeID))->toArray();
     /*$storeRecommendProducts = $this->productModel->getProducts(array('Product.isStoreRecommend' => 1, 'Product.storeID' => $storeID, 'Product.auctionStatus' => array(1, 2)));
       $storeRecommendProductsData = $storeRecommendProducts['data'];
       foreach($storeRecommendProductsData as $k => $v){
           $storeRecommendProductsData[$k]['leftTime'] = Utility::getLeftTime(time(), $v['endTime']);
       }*/
     if (!empty($order) && !empty($sort)) {
         $order = 'Product.' . $order . ' ' . $sort;
     }
     $where = new Where();
     $where->equalTo('Product.storeID', $storeID);
     if (!empty($auctionStatus)) {
         $where->equalTo('Product.auctionStatus', $auctionStatus);
     } else {
         $where->in('Product.auctionStatus', array(1, 2));
     }
     $where->isNull('Product.specialID');
     if (!empty($storeCategoryID)) {
         $where->and->nest()->or->equalTo('Product.firstStoreCategoryID', $storeCategoryID)->or->equalTo('Product.secondStoreCategoryID', $storeCategoryID)->or->equalTo('Product.thirdStoreCategoryID', $storeCategoryID);
     }
     $storeProducts = $this->productModel->getProducts($where, $this->pageNum, $this->limit, $order);
     $storeProductsData = $storeProducts['data'];
     foreach ($storeProductsData as $k => $v) {
         $storeProductsData[$k]['leftTime'] = Utility::getLeftTime(time(), $v['endTime']);
     }
     $this->view->setVariables(array('storeInfo' => $storeInfo, 'storeProducts' => $storeProductsData, 'storeCategories' => $storeCategories, 'pages' => $storeProducts['pages'], 'auctionStatus' => $auctionStatus, 'order' => $this->queryData['order'], 'sort' => $sort, 'storeCategoryID' => $storeCategoryID));
     return $this->view;
 }

 /**
  * @param null $parentId
  * @param array $roles
  * @return array
  */
 public function getMenuList($parentId = null, array $roles)
 {
     $results = $this->select(function (Select $select) use($parentId, $roles) {
         try {
             $where = new Where();
             if (empty($parentId)) {
                 $where->isNull('parentId');
             } else {
                 $where->equalTo('parentId', $parentId);
             }
             if (!empty($roles)) {
                 $where->in('mp.roleId', $roles);
             } else {
                 $where->expression(' 1 = ?', 0);
             }
             $select->join(array('mp' => 'tbl_menu_permission'), 'tbl_menu.menuId = mp.menuId', array('menuId'), Select::JOIN_INNER)->where($where)->quantifier(Select::QUANTIFIER_DISTINCT)->order(array('priority ASC'));
         } catch (\Exception $ex) {
             throw $ex;
         }
     });
     $menus = array();
     foreach ($results as $menu) {
         $nav = array();
         $nav['id'] = $menu->getMenuId();
         $pages = $this->getMenuList($menu->getMenuId(), $roles);
         $caret = '';
         if (!empty($pages)) {
             $nav['pages'] = $pages;
         }
         $icon = '';
         if ($menu->getIcon()) {
             $icon = '<span class="' . $menu->getIcon() . '"></span>&nbsp;';
         }
         $nav['title'] = $menu->getDescription();
         $nav['label'] = $icon . htmlspecialchars($menu->getTitle()) . $caret;
         $nav['order'] = $menu->getPriority();
         $nav['rel'] = array('divider' => $menu->getHasDivider());
         if ($menu->getUrlType() == 'R') {
             $nav['route'] = $menu->getUrls();
         } else {
             $nav['uri'] = $menu->getUrls();
         }
         $menus[] = $nav;
     }
     return $menus;
 }