function testSelectWithLimitClause()
{
// numeric limit
$query = new SQLQuery();
$query->from[] = "MyTable";
$query->limit("99");
$this->assertEquals("SELECT * FROM MyTable LIMIT 99", $query->sql());
// array limit
$query = new SQLQuery();
$query->from[] = "MyTable";
$query->limit(array('limit' => 99));
$this->assertEquals("SELECT * FROM MyTable LIMIT 99", $query->sql());
// array limit with start (MySQL specific)
$query = new SQLQuery();
$query->from[] = "MyTable";
$query->limit(array('limit' => 99, 'start' => 97));
$this->assertEquals("SELECT * FROM MyTable LIMIT 97,99", $query->sql());
}
/**
* Build a {@link SQLQuery} object to perform the given query.
*
* @param string $filter A filter to be inserted into the WHERE clause.
* @param string|array $sort A sort expression to be inserted into the ORDER BY clause. If omitted, self::$default_sort will be used.
* @param string|array $limit A limit expression to be inserted into the LIMIT clause.
* @param string $join A single join clause. This can be used for filtering, only 1 instance of each DataObject will be returned.
* @param boolean $restictClasses Restrict results to only objects of either this class of a subclass of this class
* @param string $having A filter to be inserted into the HAVING clause.
*
* @return SQLQuery Query built.
*/
public function buildSQL($filter = "", $sort = "", $limit = "", $join = "", $restrictClasses = true, $having = "") {
// Find a default sort
if(!$sort) {
$sort = $this->stat('default_sort');
}
// Get the tables to join to
$tableClasses = ClassInfo::dataClassesFor($this->class);
if(!$tableClasses) {
if(!ManifestBuilder::has_been_included()) {
user_error("DataObjects have been requested before the manifest is loaded. Please ensure you are not querying the database in _config.php.", E_USER_ERROR);
} else {
user_error("DataObject::buildSQL: Can't find data classes (classes linked to tables) for $this->class. Please ensure you run dev/build after creating a new DataObject.", E_USER_ERROR);
}
}
$baseClass = array_shift($tableClasses);
$select = array("`$baseClass`.*");
// Build our intial query
$query = new SQLQuery($select);
$query->from("`$baseClass`");
$query->where($filter);
$query->orderby($sort);
$query->limit($limit);
// Add SQL for multi-value fields on the base table
$databaseFields = $this->databaseFields();
if($databaseFields) foreach($databaseFields as $k => $v) {
if(!in_array($k, array('ClassName', 'LastEdited', 'Created'))) {
if(ClassInfo::classImplements($v, 'CompositeDBField')) {
$this->dbObject($k)->addToQuery($query);
}
}
}
// Join all the tables
if($tableClasses && self::$subclass_access) {
foreach($tableClasses as $tableClass) {
$query->from[$tableClass] = "LEFT JOIN `$tableClass` ON `$tableClass`.ID = `$baseClass`.ID";
$query->select[] = "`$tableClass`.*";
// Add SQL for multi-value fields
$SNG = singleton($tableClass);
$databaseFields = $SNG->databaseFields();
if($databaseFields) foreach($databaseFields as $k => $v) {
if(!in_array($k, array('ClassName', 'LastEdited', 'Created'))) {
if(ClassInfo::classImplements($v, 'CompositeDBField')) {
$SNG->dbObject($k)->addToQuery($query);
}
}
}
}
}
$query->select[] = "`$baseClass`.ID";
$query->select[] = "if(`$baseClass`.ClassName,`$baseClass`.ClassName,'$baseClass') AS RecordClassName";
// Get the ClassName values to filter to
$classNames = ClassInfo::subclassesFor($this->class);
if(!$classNames) {
user_error("DataObject::get() Can't find data sub-classes for '$callerClass'");
}
// If querying the base class, don't bother filtering on class name
if($restrictClasses && $this->class != $baseClass) {
// Get the ClassName values to filter to
$classNames = ClassInfo::subclassesFor($this->class);
if(!$classNames) {
user_error("DataObject::get() Can't find data sub-classes for '$callerClass'");
}
$query->where[] = "`$baseClass`.ClassName IN ('" . implode("','", $classNames) . "')";
}
if($having) {
$query->having[] = $having;
}
if($join) {
$query->from[] = $join;
$query->groupby[] = reset($query->from) . ".ID";
}
return $query;
}
function testSelectWithLimitClause()
{
// These are MySQL specific :-S
if (DB::getConn() instanceof MySQLDatabase) {
// numeric limit
$query = new SQLQuery();
$query->from[] = "MyTable";
$query->limit("99");
$this->assertEquals("SELECT * FROM MyTable LIMIT 99", $query->sql());
// array limit
$query = new SQLQuery();
$query->from[] = "MyTable";
$query->limit(array('limit' => 99));
$this->assertEquals("SELECT * FROM MyTable LIMIT 99", $query->sql());
// array limit with start (MySQL specific)
$query = new SQLQuery();
$query->from[] = "MyTable";
$query->limit(array('limit' => 99, 'start' => 97));
$this->assertEquals("SELECT * FROM MyTable LIMIT 99 OFFSET 97", $query->sql());
}
}
/**
* 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.');
}
