/** * 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; }