PHP SQLQuery::setLimit方法代码示例

 function testSelectWithLimitClause()
 {
     if (!(DB::getConn() instanceof MySQLDatabase || DB::getConn() instanceof SQLite3Database || DB::getConn() instanceof PostgreSQLDatabase)) {
         $this->markTestIncomplete();
     }
     $query = new SQLQuery();
     $query->setFrom("MyTable");
     $query->setLimit(99);
     $this->assertEquals("SELECT * FROM MyTable LIMIT 99", $query->sql());
     // array limit with start (MySQL specific)
     $query = new SQLQuery();
     $query->setFrom("MyTable");
     $query->setLimit(99, 97);
     $this->assertEquals("SELECT * FROM MyTable LIMIT 99 OFFSET 97", $query->sql());
 }

 public function Tags()
 {
     $select = array('Title');
     $query = new SQLQuery($select, 'PostTag');
     $query->selectField('count(PostTag.ID)', 'Number');
     $query->selectField('PostTag.ID');
     $query->addInnerjoin('MicroPost_Tags', 'PostTag.ID = MicroPost_Tags.PostTagID');
     $date = date('Y-m-d H:i:s', strtotime('-1 month'));
     $query->addWhere("MicroPost_Tags.Tagged > '{$date}'");
     $query->addWhere('"PostTag"."Title" NOT LIKE \'SELF_TAG%\'');
     $query->addGroupBy('PostTag.ID');
     $query->setLimit(20);
     $rows = $query->execute();
     $tags = ArrayList::create();
     foreach ($rows as $row) {
         $data = new ArrayData($row);
         $data->Link = Controller::join_links(TimelineController::URL_SEGMENT, '?tags=' . urlencode($data->Title));
         $tags->push($data);
     }
     return $tags;
 }

 /**
  * Set the limit of this query.
  * 
  * @param int $limit
  * @param int $offset
  */
 public function limit($limit, $offset = 0)
 {
     $this->query->setLimit($limit, $offset);
     return $this;
 }

 /**
  * Returns a number of topseller products.
  * 
  * @return ArrayList
  * 
  * @author Sebastian Diel <sdiel@pixeltricks.de>,
  *         Sascha Koehler <skoehler@pixeltricks.de>
  * @since 03.02.2015
  */
 public function Elements()
 {
     if (!$this->numberOfProductsToShow) {
         $this->numberOfProductsToShow = SilvercartTopsellerProductsWidget::$defaults['numberOfProductsToShow'];
     }
     $cachekey = 'TopsellerProducts' . $this->numberOfProductsToShow;
     $cache = SS_Cache::factory($cachekey);
     $cachedResult = $cache->load($cachekey);
     if ($cachedResult) {
         $result = unserialize($result);
     } else {
         $products = array();
         $sqlQuery = new SQLQuery();
         $sqlQuery->selectField('SOP.SilvercartProductID');
         $sqlQuery->selectField('SUM(SOP.Quantity) AS OrderedQuantity');
         $sqlQuery->addFrom('SilvercartOrderPosition SOP');
         $sqlQuery->addLeftJoin('SilvercartProduct', 'SP.ID = SOP.SilvercartProductID', 'SP');
         $sqlQuery->addWhere('SP.isActive = 1');
         $sqlQuery->addGroupBy('SOP.SilvercartProductID');
         $sqlQuery->addOrderBy('OrderedQuantity', 'DESC');
         $sqlQuery->setLimit($this->numberOfProductsToShow);
         $sqlResult = $sqlQuery->execute();
         foreach ($sqlResult as $row) {
             $product = DataObject::get_by_id('SilvercartProduct', $row['SilvercartProductID']);
             $product->addCartFormIdentifier = $this->ID . '_' . $product->ID;
             $products[] = $product;
         }
         $result = new ArrayList($products);
     }
     return $result;
 }

 /**
  * Test passing in a LIMIT with OFFSET clause string.
  */
 public function testLimitSetFromClauseString()
 {
     $query = new SQLQuery();
     $query->setSelect('*');
     $query->setFrom('"SQLQueryTest_DO"');
     $query->setLimit('20 OFFSET 10');
     $limit = $query->getLimit();
     $this->assertEquals(20, $limit['limit']);
     $this->assertEquals(10, $limit['start']);
 }

 /**
  * Retrieves Locations by lat, long, distance, and optionally a limit.
  */
 public function getLocationSQLResultsByLatLong($lat = 37, $long = -122, $distance = 25, $limit = null)
 {
     //$data = DB::query('SELECT "ID" FROM "Marker" LIMIT 0 , '.$limit.';')->value();
     //$query = 'SELECT "ID", ( 3959 * acos( cos( radians('.$lat.') ) * cos( radians( Latitude ) ) * cos( radians( Longitude ) - radians('.$long.') ) + sin( radians('.$lat.') ) * sin( radians( Latitude ) ) ) ) AS "Distance" FROM "Marker" HAVING "Distance" < '.$distance.' ORDER BY "Distance" LIMIT 0 , '.$limit.';';
     $markerClass = StoreFinder::$MarkerClass;
     $sqlQuery = new SQLQuery();
     $sqlQuery->setFrom($markerClass);
     $sqlQuery->selectField('*');
     $sqlQuery->selectField('( 3959 * acos( cos( radians(' . $lat . ') ) * cos( radians( Latitude ) ) * cos( radians( Longitude ) - radians(' . $long . ') ) + sin( radians(' . $lat . ') ) * sin( radians( Latitude ) ) ) )', 'Distance');
     $sqlQuery->setHaving("Distance < " . $distance);
     $sqlQuery->setOrderBy('Distance');
     $sqlQuery->setLimit($limit);
     if ($markerClass != 'Marker') {
         $sqlQuery->addLeftJoin("Marker", 'Marker.ID = ' . $markerClass . '.ID');
     }
     $this->extraSQL($sqlQuery);
     // Execute and return a Query object
     $result = $sqlQuery->execute();
     return $result;
 }

 /**
  * Perform a search against the data table.
  *
  * @param array $where Array of strings to add into the WHERE clause
  * @param array $orderby Array of column as key, to direction as value to add into the ORDER BY clause
  * @param string|int $start Record to start at (for paging)
  * @param string|int $pageLength Number of results per page (for paging)
  * @param boolean $paged Paged results or not?
  * @return ArrayList|PaginatedList
  */
 protected function queryList($where = array(), $orderby = array(), $start, $pageLength, $paged = true)
 {
     $dataClass = $this->dataRecord->getDataClass();
     if (!$dataClass) {
         return new PaginatedList(new ArrayList());
     }
     $resultColumns = $this->dataRecord->getDataSingleton()->summaryFields();
     $resultColumns['ID'] = 'ID';
     $results = new ArrayList();
     $query = new SQLQuery();
     $query->setSelect($this->escapeSelect(array_keys($resultColumns)))->setFrom("\"{$dataClass}\"");
     $query->addWhere($where);
     $query->addOrderBy($orderby);
     $query->setConnective('AND');
     if ($paged) {
         $query->setLimit($pageLength, $start);
     }
     foreach ($query->execute() as $record) {
         $result = new $dataClass($record);
         $result->Columns = $this->Columns($result);
         // we attach Columns here so the template can loop through them on each result
         $results->push($result);
     }
     if ($paged) {
         $list = new PaginatedList($results);
         $list->setPageStart($start);
         $list->setPageLength($pageLength);
         $list->setTotalItems($query->unlimitedRowCount());
         $list->setLimitItems(false);
     } else {
         $list = $results;
     }
     return $list;
 }

 /**
  * @expectedException InvalidArgumentException
  */
 public function testNegativeOffsetAndLimit()
 {
     $query = new SQLQuery();
     $query->setLimit(-10, -10);
 }

 /**
  * @param \SQLQuery $query
  * @param array $data
  * @return \SQLQuery
  */
 public function modify(\SQLQuery $query, array $data, QueryBuilderInterface $queryBuilder)
 {
     $query->setLimit($this->getLimit());
     return $query;
 }