/**
* Use the limit from the given query to add prev/next buttons to this DataObjectSet.
* @param SQLQuery $query The query used to generate this DataObjectSet
*/
public function parseQueryLimit(SQLQuery $query) {
if($query->limit) {
if(stripos($query->limit, 'OFFSET')) {
list($length, $start) = split(" +OFFSET +", trim($query->limit));
} else {
$result = split(" *, *", trim($query->limit));
$start = $result[0];
$length = isset($result[1]) ? $result[1] : null;
}
if(!$length) {
$length = $start;
$start = 0;
}
$this->setPageLimits($start, $length, $query->unlimitedRowCount());
}
}
/**
* Use the limit from the given query to add prev/next buttons to this DataObjectSet.
* @param SQLQuery $query The query used to generate this DataObjectSet
*/
public function parseQueryLimit(SQLQuery $query)
{
if ($query->limit) {
if (is_array($query->limit)) {
$length = $query->limit['limit'];
$start = $query->limit['start'];
} else {
if (stripos($query->limit, 'OFFSET')) {
list($length, $start) = preg_split("/ +OFFSET +/i", trim($query->limit));
} else {
$result = preg_split("/ *, */", trim($query->limit));
$start = $result[0];
$length = isset($result[1]) ? $result[1] : null;
}
}
if (!$length) {
$length = $start;
$start = 0;
}
$this->setPageLimits($start, $length, $query->unlimitedRowCount());
}
}
/**
* 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;
}
/**
* Sets the page length, page start and total items from a query object's
* limit, offset and unlimited count. The query MUST have a limit clause.
*
* @param SQLQuery $query
*/
public function setPaginationFromQuery(SQLQuery $query)
{
if ($limit = $query->getLimit()) {
$this->setPageLength($limit['limit']);
$this->setPageStart($limit['start']);
$this->setTotalItems($query->unlimitedRowCount());
}
return $this;
}
/**
* Test {@link DataObjectSet->parseQueryLimit()}
*/
function testParseQueryLimit()
{
// Create empty objects, because they don't need to have contents
$sql = new SQLQuery('*', '"Member"');
$max = $sql->unlimitedRowCount();
$set = new DataObjectSet();
// Test handling an array
$set->parseQueryLimit($sql->limit(array('limit' => 5, 'start' => 2)));
$expected = array('pageStart' => 2, 'pageLength' => 5, 'totalSize' => $max);
$this->assertEquals($expected, $set->getPageLimits(), 'The page limits match expected values.');
// Test handling OFFSET string
// uppercase
$set->parseQueryLimit($sql->limit('3 OFFSET 1'));
$expected = array('pageStart' => 1, 'pageLength' => 3, 'totalSize' => $max);
$this->assertEquals($expected, $set->getPageLimits(), 'The page limits match expected values.');
// and lowercase
$set->parseQueryLimit($sql->limit('32 offset 3'));
$expected = array('pageStart' => 3, 'pageLength' => 32, 'totalSize' => $max);
$this->assertEquals($expected, $set->getPageLimits(), 'The page limits match expected values.');
// Finally check MySQL LIMIT syntax
$set->parseQueryLimit($sql->limit('7, 7'));
$expected = array('pageStart' => 7, 'pageLength' => 7, 'totalSize' => $max);
$this->assertEquals($expected, $set->getPageLimits(), 'The page limits match expected values.');
}
