PHP Select::limit方法代码示例

 /**
  *
  * @param int $days_threshold days threshold
  * @param int $ca_threshold turnover threshold
  * @param int $limit
  * @return type
  */
 public function getCustomerGeo($days_threshold = 300, $ca_threshold = 1000, $min_accuracy = 6, $limit = 1000)
 {
     $akilia2db = $this->configuration['synchronizer']['db_akilia2'];
     $select = new Select();
     $bcg = new \Zend\Db\Sql\TableIdentifier('base_customer_geo', $akilia2db);
     $bc = new \Zend\Db\Sql\TableIdentifier('base_customer', $akilia2db);
     $bs = new \Zend\Db\Sql\TableIdentifier('base_state', $akilia2db);
     $bco = new \Zend\Db\Sql\TableIdentifier('base_country', $akilia2db);
     $so = new \Zend\Db\Sql\TableIdentifier('sal_order', $akilia2db);
     $sol = new \Zend\Db\Sql\TableIdentifier('sal_order_line', $akilia2db);
     $select->from(["bc" => $bc], [])->join(['bcg' => $bcg], "bc.id = bcg.customer_id", [], Select::JOIN_LEFT)->join(['bs' => $bs], "bs.id = bc.state_id", [], Select::JOIN_LEFT)->join(['bco' => $bco], "bco.id = bc.country_id", [], Select::JOIN_LEFT)->join(['so' => $so], "bc.id = so.customer_id", [], Select::JOIN_INNER)->join(['sol' => $sol], "so.id = sol.order_id", [], Select::JOIN_INNER)->where('bc.flag_archived <> 1');
     $columns = ['customer_id' => new Expression('bc.id'), 'name' => new Expression('bc.name'), 'street' => new Expression('bc.street'), 'street_2' => new Expression('bc.street_2'), 'street_number' => new Expression('bc.street_number'), 'state_reference' => new Expression('bs.reference'), 'state_name' => new Expression('bs.name'), 'zipcode' => new Expression('bc.zipcode'), 'city' => new Expression('bc.city'), 'country' => new Expression('bco.name'), 'accuracy' => new Expression('bcg.accuracy'), 'latitude' => new Expression('bcg.latitude'), 'longitude' => new Expression('bcg.longitude')];
     $select->columns(array_merge($columns, ['total_net' => new Expression('sum(sol.price_total_net)')]), true);
     $select->group($columns);
     $select->having("sum(sol.price_total_net) > {$ca_threshold}");
     $select->where(function (Where $where) use($min_accuracy) {
         //$where->greaterThan('so.date_order', '2012-12-31');
         $where->notLike('bc.name', '%FINISHED%');
         $where->nest->lessThan('accuracy', $min_accuracy)->or->isNull('accuracy')->unnest;
     });
     $select->where(new Expression("(TO_DAYS(NOW()) - TO_DAYS(so.date_order)) < {$days_threshold}"));
     if ($limit > 0) {
         $select->limit($limit);
     }
     $store = $this->getStore($select);
     $data = $store->getData()->toArray();
     return $data;
 }

 public function getList($where = array(), $order = null, $offset = null, $limit = null)
 {
     if (empty($where['ProductFilterOption.productCategoryFilterOptionID'])) {
         $select = new Select();
         $select->from(array('b' => 'Product'));
         $select->join(array('c' => 'ProductCategory'), 'b.productCategoryID = c.productCategoryID', array('categoryName'));
         $select->where($where);
         $select->offset($offset);
         $select->limit($limit);
         $select->order($order);
     } else {
         $select = $this->getSelect();
         $select->columns(array())->join(array('b' => 'Product'), 'ProductFilterOption.productID = b.productID')->join(array('c' => 'ProductCategory'), 'b.productCategoryID = c.productCategoryID', array('categoryName'))->where($where)->offset($offset)->limit($limit)->group(array('ProductFilterOption.productID'))->having('count(ProductFilterOption.productID) > ' . (count($where['ProductFilterOption.productCategoryFilterOptionID']) - 1));
         $select->order($order);
     }
     $paginator = $this->paginate($select);
     $paginator->setCurrentPageNumber(ceil($offset / $limit) + 1);
     //$paginator->setItemCountPerPage(1);
     $products = $paginator->getCurrentItems()->getArrayCopy();
     $pages = $paginator->getPages();
     $productsCount = $paginator->getTotalItemCount();
     foreach ($products as $k => $v) {
         $products[$k]['leftTime'] = Utility::getLeftTime(time(), $v['endTime']);
     }
     return array('products' => $products, 'productsCount' => $productsCount, 'pages' => $pages);
 }

 function fetchAll($beginning, $limit)
 {
     $select = new Select($this->tableGateway->getTable());
     $select->limit($limit)->offset($beginning);
     $rowSet = $this->tableGateway->selectWith($select);
     return $rowSet;
 }

 public function fetchPostsByFeeds($feeds, $offset = null, $limit = null)
 {
     $select = new Select(self::$_tableName);
     if (!is_null($offset)) {
         $select->offset($offset);
     }
     if (!is_null($limit)) {
         $select->limit($limit);
     }
     $select->join('directus_social_feeds', 'directus_social_feeds.id = directus_social_posts.feed', ['feed_type' => 'type'])->order('directus_social_posts.datetime DESC');
     $select->where->equalTo('directus_social_posts.status', 1)->equalTo('directus_social_feeds.status', 1);
     $FeedWhere = new Where();
     $SocialCache = new SocialCache();
     foreach ($feeds as $feed) {
         // Run scrape if due
         $SocialCache->scrapeFeedIfDue($feed['name'], $feed['type']);
         $FeedWhere->or->nest->equalTo('directus_social_feeds.name', $feed['name'])->equalTo('directus_social_feeds.type', $feed['type'])->unnest;
         $select->where($FeedWhere);
     }
     $socialPosts = $this->selectWith($select);
     $socialPosts = $socialPosts->toArray();
     // Unserialize cached feed entry API-responses
     foreach ($socialPosts as &$post) {
         $post['data'] = json_decode($post['data'], true);
     }
     return $socialPosts;
 }

 protected function _applyLimit()
 {
     if ($this->_limit !== false) {
         $this->_select->limit($this->_limit);
     }
     return $this;
 }

 public function getFeedByHandleAndType($handle, $type)
 {
     $select = new Select($this->getTable());
     $select->limit(1);
     $select->where->equalTo('name', $handle)->equalTo('type', $type);
     $rowset = $this->selectWith($select);
     return $rowset->current();
 }

 /**
  * (non-PHPdoc)
  *
  * @see \MwAdmin\Service\DataTableProviderInterface::getDataTableSelect()
  */
 public function getDataTableSelect($page = 1, $perPage = 10)
 {
     $table = $this->getDataTableGateway();
     $sql = new Select($table->getTable());
     $sql->order($this->getDefaultSort());
     $sql->limit($perPage, $page * $perPage - $perPage);
     return $sql;
 }

 public function getListingById($id = 1)
 {
     $select = new Select();
     $select->from($this->tableName);
     $where = new Where();
     $where->equalTo('listings_id', $id);
     $select->where($where);
     $select->limit(1);
     return $this->selectWith($select)->current();
 }

 public function fetchByUserAndId($user_id, $id)
 {
     $select = new Select($this->table);
     $select->limit(1);
     $select->where->equalTo('id', $id)->equalTo('user', $user_id);
     $bookmarks = $this->selectWith($select)->current();
     if ($bookmarks) {
         $bookmarks = $bookmarks->toArray();
     }
     return $bookmarks;
 }

 public function fetchByCollectionAndName($collection, $name)
 {
     $select = new Select($this->table);
     $select->limit(1);
     $select->where->equalTo('collection', $collection)->equalTo('name', $name);
     $rowset = $this->selectWith($select);
     $result = $rowset->current();
     if (false === $result) {
         throw new \Exception('Required `directus_setting` with collection `' . $collection . '` and name `' . $name . '` not found.');
     }
     return $result;
 }

 public function getlistnews($limit)
 {
     $select = new Select('catelog');
     $select->order('id DESC');
     $select->limit($limit);
     $resultSet = $this->tableGateway->selectWith($select);
     $array = array();
     foreach ($resultSet as $value) {
         array_push($array, $value);
     }
     return $array;
 }

 /**
  * Return all values from core_config_data
  *
  * @param string  $locale Locale
  * @param integer $limit  Limit
  *
  * @return array
  */
 public function getValues($locale = null, $limit = null)
 {
     $select = new Select();
     $select->from('core_translate')->columns(array('src_id' => 'id', 'source'))->join('core_translate_locale', 'core_translate.id = core_translate_locale.core_translate_id', array('dst_id' => 'id', 'destination', 'locale'), Select::JOIN_INNER);
     if (!empty($locale)) {
         $select->where->equalTo('core_translate_locale.locale', $locale);
     }
     if (!empty($limit)) {
         $select->limit($limit);
     }
     $select->order('core_translate.source ASC');
     return $this->fetchAll($select);
 }

 public function fetchOneById($id)
 {
     $select = new Select(self::$_tableName);
     $select->limit(1);
     $select->where->equalTo('id', $id);
     $row = $this->selectWith($select)->current();
     if (!$row) {
         return false;
     }
     $row = $row->toArray();
     // The adapter's `params` column is JSON serialized.
     $row['params'] = $this->jsonDecodeIfPossible($row['params']);
     return $row;
 }

 public function getLastPhotoByAlbumId($id)
 {
     $id = (int) $id;
     $select = new Select('photo');
     //CHANGE TABLE_NAME as per needs
     $select->where('album_id = ' . $id);
     $select->order('id DESC');
     $select->limit(1);
     $rowset = $this->tableGateway->selectWith($select);
     //Will get array of rows.
     $row = $rowset->current();
     if (!$row) {
         return [];
     }
     return $row;
 }

 /**
  * @param Zend\Db\Sql\Select $select
  * @return Zend\Db\ResultSet
  */
 public function performSelect(Select $select, $order, $paginated = false, $limit = null)
 {
     if ($order) {
         $select->order($order);
     }
     if ($limit) {
         $select->limit($limit);
     }
     if ($paginated) {
         $adapter = new DbSelect($select, $this->tableGateway->getAdapter(), $this->resultSetPrototype);
         $paginator = new Paginator($adapter);
         return $paginator;
     } else {
         return $this->tableGateway->selectWith($select);
     }
 }