/**
* Set up the simplest initial query
*/
public function initialiseQuery()
{
// Get the tables to join to.
// Don't get any subclass tables - let lazy loading do that.
$tableClasses = ClassInfo::ancestry($this->dataClass, true);
// Error checking
if (!$tableClasses) {
if (!SS_ClassLoader::instance()->hasManifest()) {
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("DataList::create Can't find data classes (classes linked to tables) for" . " {$this->dataClass}. Please ensure you run dev/build after creating a new DataObject.", E_USER_ERROR);
}
}
$baseClass = array_shift($tableClasses);
// Build our intial query
$this->query = new SQLSelect(array());
$this->query->setDistinct(true);
if ($sort = singleton($this->dataClass)->stat('default_sort')) {
$this->sort($sort);
}
$this->query->setFrom("\"{$baseClass}\"");
$obj = Injector::inst()->get($baseClass);
$obj->extend('augmentDataQueryCreation', $this->query, $this);
}
public function testParameterisedLeftJoins()
{
$query = new SQLSelect();
$query->setSelect(array('"SQLSelectTest_DO"."Name"', '"SubSelect"."Count"'));
$query->setFrom('"SQLSelectTest_DO"');
$query->addLeftJoin('(SELECT "Title", COUNT(*) AS "Count" FROM "SQLSelectTestBase" GROUP BY "Title" HAVING "Title" NOT LIKE ?)', '"SQLSelectTest_DO"."Name" = "SubSelect"."Title"', 'SubSelect', 20, array('%MyName%'));
$query->addWhere(array('"SQLSelectTest_DO"."Date" > ?' => '2012-08-08 12:00'));
$this->assertSQLEquals('SELECT "SQLSelectTest_DO"."Name", "SubSelect"."Count"
FROM "SQLSelectTest_DO" LEFT JOIN (SELECT "Title", COUNT(*) AS "Count" FROM "SQLSelectTestBase"
GROUP BY "Title" HAVING "Title" NOT LIKE ?) AS "SubSelect" ON "SQLSelectTest_DO"."Name" =
"SubSelect"."Title"
WHERE ("SQLSelectTest_DO"."Date" > ?)', $query->sql($parameters));
$this->assertEquals(array('%MyName%', '2012-08-08 12:00'), $parameters);
$query->execute();
}
/**
* Test passing in a LIMIT with OFFSET clause string.
*/
public function testLimitSetFromClauseString()
{
$query = new SQLSelect();
$query->setSelect('*');
$query->setFrom('"SQLQueryTest_DO"');
$query->setLimit('20 OFFSET 10');
$limit = $query->getLimit();
$this->assertEquals(20, $limit['limit']);
$this->assertEquals(10, $limit['start']);
}
/**
* Return the number of rows in this query if the limit were removed. Useful in paged data sets.
*
* @param string $column
* @return int
*/
public function unlimitedRowCount($column = null)
{
// we can't clear the select if we're relying on its output by a HAVING clause
if (count($this->having)) {
$records = $this->execute();
return $records->numRecords();
}
$clone = clone $this;
$clone->limit = null;
$clone->orderby = null;
// Choose a default column
if ($column == null) {
if ($this->groupby) {
// @todo Test case required here
$countQuery = new SQLSelect();
$countQuery->setSelect("count(*)");
$countQuery->setFrom(array('(' . $clone->sql($innerParameters) . ') all_distinct'));
$sql = $countQuery->sql($parameters);
// $parameters should be empty
$result = DB::prepared_query($sql, $innerParameters);
return $result->value();
} else {
$clone->setSelect(array("count(*)"));
}
} else {
$clone->setSelect(array("count({$column})"));
}
$clone->setGroupBy(array());
return $clone->execute()->value();
}
/**
* Gets the values for multiple rows on a database table by the ID column.
* Useful when fields have been removed from the class' `$db` property,
* and therefore are no longer accessible through the ORM.
* Returns an empty array if the table, any of the columns or the row do not exist.
*
* @param string $table
* @param array $fields
* @param string|int $id
* @return array array('FieldName' => value)
*/
public static function getRowValuesFromTable($table, array $fields, $id)
{
$values = array();
if (self::tableColumnsExist($table, $fields)) {
$id = (int) $id;
$query = new SQLSelect();
$query->setFrom($table)->setSelect($fields)->setWhere("ID = {$id}");
$results = $query->execute();
if ($results) {
foreach ($results as $result) {
foreach ($fields as $field) {
$values[$field] = $result[$field];
}
break;
}
}
}
return $values;
}
