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 <*****@*****.**>,
* Sascha Koehler <*****@*****.**>
* @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;
}