/**
* 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;
}
/**
* 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 = "")
{
// Cache the big hairy part of buildSQL
if (!isset(self::$cache_buildSQL_query[$this->class])) {
// 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);
// $collidingFields will keep a list fields that appear in mulitple places in the class
// heirarchy for this table. They will be dealt with more explicitly in the SQL query
// to ensure that junk data from other tables doesn't corrupt data objects
$collidingFields = array();
// Build our intial query
$query = new SQLQuery(array());
$query->from("\"{$baseClass}\"");
// Add SQL for multi-value fields on the base table
$databaseFields = self::database_fields($baseClass);
if ($databaseFields) {
foreach ($databaseFields as $k => $v) {
if (!in_array($k, array('ClassName', 'LastEdited', 'Created')) && ClassInfo::classImplements($v, 'CompositeDBField')) {
$this->dbObject($k)->addToQuery($query);
} else {
$query->select[$k] = "\"{$baseClass}\".\"{$k}\"";
}
}
}
// Join all the tables
if ($tableClasses && self::$subclass_access) {
foreach ($tableClasses as $tableClass) {
$query->from[$tableClass] = "LEFT JOIN \"{$tableClass}\" ON \"{$tableClass}\".\"ID\" = \"{$baseClass}\".\"ID\"";
// Add SQL for multi-value fields
$databaseFields = self::database_fields($tableClass);
$compositeFields = self::composite_fields($tableClass, false);
if ($databaseFields) {
foreach ($databaseFields as $k => $v) {
if (!isset($compositeFields[$k])) {
// Update $collidingFields if necessary
if (isset($query->select[$k])) {
if (!isset($collidingFields[$k])) {
$collidingFields[$k] = array($query->select[$k]);
}
$collidingFields[$k][] = "\"{$tableClass}\".\"{$k}\"";
} else {
$query->select[$k] = "\"{$tableClass}\".\"{$k}\"";
}
}
}
}
if ($compositeFields) {
foreach ($compositeFields as $k => $v) {
$dbO = $this->dbObject($k);
if ($dbO) {
$dbO->addToQuery($query);
}
}
}
}
}
// Resolve colliding fields
if ($collidingFields) {
foreach ($collidingFields as $k => $collisions) {
$caseClauses = array();
foreach ($collisions as $collision) {
if (preg_match('/^"([^"]+)"/', $collision, $matches)) {
$collisionBase = $matches[1];
$collisionClasses = ClassInfo::subclassesFor($collisionBase);
$caseClauses[] = "WHEN \"{$baseClass}\".\"ClassName\" IN ('" . implode("', '", $collisionClasses) . "') THEN {$collision}";
} else {
user_error("Bad collision item '{$collision}'", E_USER_WARNING);
}
}
$query->select[$k] = "CASE " . implode(" ", $caseClauses) . " ELSE NULL END" . " AS \"{$k}\"";
}
}
$query->select[] = "\"{$baseClass}\".\"ID\"";
$query->select[] = "CASE WHEN \"{$baseClass}\".\"ClassName\" IS NOT NULL THEN \"{$baseClass}\".\"ClassName\" ELSE '{$baseClass}' END 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) . "')";
}
self::$cache_buildSQL_query[$this->class] = clone $query;
} else {
$query = clone self::$cache_buildSQL_query[$this->class];
}
// Find a default sort
if (!$sort) {
$sort = $this->stat('default_sort');
}
// Add quoting to sort expression if it's a simple column name
if (preg_match('/^[A-Z][A-Z0-9_]*$/i', $sort)) {
$sort = "\"{$sort}\"";
}
$query->where($filter);
$query->orderby($sort);
$query->limit($limit);
if ($having) {
$query->having[] = $having;
}
if ($join) {
$query->from[] = $join;
// In order to group by unique columns we have to group by everything listed in the select
foreach ($query->select as $field) {
// Skip the _SortColumns; these are only going to be aggregate functions
if (preg_match('/AS\\s+\\"?_SortColumn/', $field, $matches)) {
// Identify columns with aliases, and ignore the alias. Making use of the alias in
// group by was causing problems when those queries were subsequently passed into
// SQLQuery::unlimitedRowCount.
} else {
if (preg_match('/^(.*)\\s+AS\\s+(\\"[^"]+\\")\\s*$/', $field, $matches)) {
$query->groupby[] = $matches[1];
// Otherwise just use the field as is
} else {
$query->groupby[] = $field;
}
}
}
}
return $query;
}
