/**
* Join table via many_many relationship
*
* @param string $parentClass
* @param string $componentClass
* @param string $parentField
* @param string $componentField
* @param string $relationTable Name of relation table
*/
protected function joinManyManyRelationship($parentClass, $componentClass, $parentField, $componentField, $relationTable)
{
$parentBaseClass = ClassInfo::baseDataClass($parentClass);
$componentBaseClass = ClassInfo::baseDataClass($componentClass);
$this->query->addLeftJoin($relationTable, "\"{$relationTable}\".\"{$parentField}\" = \"{$parentBaseClass}\".\"ID\"");
$this->query->addLeftJoin($componentBaseClass, "\"{$relationTable}\".\"{$componentField}\" = \"{$componentBaseClass}\".\"ID\"");
/**
* add join clause to the component's ancestry classes so that the search filter could search on
* its ancestor fields.
*/
$ancestry = ClassInfo::ancestry($componentClass, true);
$ancestry = array_reverse($ancestry);
foreach ($ancestry as $ancestor) {
if ($ancestor != $componentBaseClass) {
$this->query->addInnerJoin($ancestor, "\"{$componentBaseClass}\".\"ID\" = \"{$ancestor}\".\"ID\"");
}
}
}
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();
}
/**
* Traverse the relationship fields, and add the table
* mappings to the query object state. This has to be called
* in any overloaded {@link SearchFilter->apply()} methods manually.
*
* @param String|array $relation The array/dot-syntax relation to follow
* @return The model class of the related item
*/
public function applyRelation($relation)
{
// NO-OP
if (!$relation) {
return $this->dataClass;
}
if (is_string($relation)) {
$relation = explode(".", $relation);
}
$modelClass = $this->dataClass;
foreach ($relation as $rel) {
$model = singleton($modelClass);
if ($component = $model->has_one($rel)) {
if (!$this->query->isJoinedTo($component)) {
$foreignKey = $model->getReverseAssociation($component);
$this->query->addLeftJoin($component, "\"{$component}\".\"ID\" = \"{$modelClass}\".\"{$foreignKey}ID\"");
/**
* add join clause to the component's ancestry classes so that the search filter could search on
* its ancestor fields.
*/
$ancestry = ClassInfo::ancestry($component, true);
if (!empty($ancestry)) {
$ancestry = array_reverse($ancestry);
foreach ($ancestry as $ancestor) {
if ($ancestor != $component) {
$this->query->addInnerJoin($ancestor, "\"{$component}\".\"ID\" = \"{$ancestor}\".\"ID\"");
}
}
}
}
$modelClass = $component;
} elseif ($component = $model->has_many($rel)) {
if (!$this->query->isJoinedTo($component)) {
$ancestry = $model->getClassAncestry();
$foreignKey = $model->getRemoteJoinField($rel);
$this->query->addLeftJoin($component, "\"{$component}\".\"{$foreignKey}\" = \"{$ancestry[0]}\".\"ID\"");
/**
* add join clause to the component's ancestry classes so that the search filter could search on
* its ancestor fields.
*/
$ancestry = ClassInfo::ancestry($component, true);
if (!empty($ancestry)) {
$ancestry = array_reverse($ancestry);
foreach ($ancestry as $ancestor) {
if ($ancestor != $component) {
$this->query->addInnerJoin($ancestor, "\"{$component}\".\"ID\" = \"{$ancestor}\".\"ID\"");
}
}
}
}
$modelClass = $component;
} elseif ($component = $model->many_many($rel)) {
list($parentClass, $componentClass, $parentField, $componentField, $relationTable) = $component;
$parentBaseClass = ClassInfo::baseDataClass($parentClass);
$componentBaseClass = ClassInfo::baseDataClass($componentClass);
$this->query->addInnerJoin($relationTable, "\"{$relationTable}\".\"{$parentField}\" = \"{$parentBaseClass}\".\"ID\"");
$this->query->addLeftJoin($componentBaseClass, "\"{$relationTable}\".\"{$componentField}\" = \"{$componentBaseClass}\".\"ID\"");
if (ClassInfo::hasTable($componentClass)) {
$this->query->addLeftJoin($componentClass, "\"{$relationTable}\".\"{$componentField}\" = \"{$componentClass}\".\"ID\"");
}
$modelClass = $componentClass;
}
}
return $modelClass;
}
public function testInnerJoin()
{
$query = new SQLSelect();
$query->setFrom('MyTable');
$query->addInnerJoin('MyOtherTable', 'MyOtherTable.ID = 2');
$query->addLeftJoin('MyLastTable', 'MyOtherTable.ID = MyLastTable.ID');
$this->assertSQLEquals('SELECT * FROM MyTable ' . 'INNER JOIN "MyOtherTable" ON MyOtherTable.ID = 2 ' . 'LEFT JOIN "MyLastTable" ON MyOtherTable.ID = MyLastTable.ID', $query->sql($parameters));
$query = new SQLSelect();
$query->setFrom('MyTable');
$query->addInnerJoin('MyOtherTable', 'MyOtherTable.ID = 2', 'table1');
$query->addLeftJoin('MyLastTable', 'MyOtherTable.ID = MyLastTable.ID', 'table2');
$this->assertSQLEquals('SELECT * FROM MyTable ' . 'INNER JOIN "MyOtherTable" AS "table1" ON MyOtherTable.ID = 2 ' . 'LEFT JOIN "MyLastTable" AS "table2" ON MyOtherTable.ID = MyLastTable.ID', $query->sql($parameters));
}
/**
* Update any requests to limit the results to the current site
*/
public function augmentSQL(SQLSelect $query, DataQuery $dataQuery = null)
{
if (Subsite::$disable_subsite_filter) {
return;
}
if (Cookie::get('noSubsiteFilter') == 'true') {
return;
}
// If you're querying by ID, ignore the sub-site - this is a bit ugly...
if (!$query->filtersOnID()) {
/*if($context = DataObject::context_obj()) $subsiteID = (int)$context->SubsiteID;
else */
$subsiteID = (int) Subsite::currentSubsiteID();
// Don't filter by Group_Subsites if we've already done that
$hasGroupSubsites = false;
foreach ($query->getFrom() as $item) {
if (is_array($item) && strpos($item['table'], 'Group_Subsites') !== false || !is_array($item) && strpos($item, 'Group_Subsites') !== false) {
$hasGroupSubsites = true;
break;
}
}
if (!$hasGroupSubsites) {
if ($subsiteID) {
$query->addLeftJoin("Group_Subsites", "\"Group_Subsites\".\"GroupID\" \n\t\t\t\t\t\t= \"Group\".\"ID\" AND \"Group_Subsites\".\"SubsiteID\" = {$subsiteID}");
$query->addWhere("(\"Group_Subsites\".\"SubsiteID\" IS NOT NULL OR\n\t\t\t\t\t\t\"Group\".\"AccessAllSubsites\" = 1)");
} else {
$query->addWhere("\"Group\".\"AccessAllSubsites\" = 1");
}
}
// WORKAROUND for databases that complain about an ORDER BY when the column wasn't selected (e.g. SQL Server)
$select = $query->getSelect();
if (isset($select[0]) && !$select[0] == 'COUNT(*)') {
$query->orderby = "\"AccessAllSubsites\" DESC" . ($query->orderby ? ', ' : '') . $query->orderby;
}
}
}
/**
* Return all data object visible attributes of the specified type, with optional filters.
*
* @parameter <{DATA_OBJECT_NAME}> string
* @parameter <{LIMIT}> integer
* @parameter <{SORT}> array(string, string)
* @parameter <{FILTERS}> array
* @return array
*/
public function retrieveValidated($class, $limit = null, $sort = null, $filters = null)
{
// Validate the data object class.
$class = strtolower($class);
if (in_array($class, array_map('strtolower', ClassInfo::subclassesFor('DataObject'))) && ($configuration = DataObjectOutputConfiguration::get_one('DataObjectOutputConfiguration', array('LOWER(IsFor) = ?' => $class))) && ($temporaryClass = DataObject::get_one($class))) {
$class = ClassInfo::baseDataClass($temporaryClass->ClassName);
$visibility = $configuration->APIwesomeVisibility ? explode(',', $configuration->APIwesomeVisibility) : null;
// Validate the sort and filters.
$where = array();
$sortValid = is_array($sort) && count($sort) === 2 && ($order = strtoupper($sort[1])) && ($order === 'ASC' || $order === 'DESC');
$filterValid = is_array($filters) && count($filters);
$sorting = array();
$filtering = array();
// Grab the appropriate attributes for this data object.
$columns = array();
$from = array();
foreach (ClassInfo::subclassesFor($class) as $subclass) {
// Determine the tables to join.
$subclassFields = DataObject::database_fields($subclass);
if (ClassInfo::hasTable($subclass)) {
// Determine the versioned table.
$same = $subclass === $class;
if ($subclass::has_extension('Versioned')) {
$subclass = "{$subclass}_Live";
}
if (!$same) {
$from[] = $subclass;
}
}
// Prepend the table names.
$subclassColumns = array();
foreach ($subclassFields as $column => $type) {
$subclassColumn = "{$subclass}.{$column}";
$subclassColumns[$subclassColumn] = $type;
// Determine the tables to sort and filter on.
if ($sortValid && $sort[0] === $column) {
$sorting[] = "{$subclassColumn} {$order}";
}
if ($filterValid && isset($filters[$column])) {
$filtering[$subclassColumn] = $filters[$column];
}
}
$columns = array_merge($columns, $subclassColumns);
}
array_shift($columns);
// Determine the versioned table.
if ($class::has_extension('Versioned')) {
$class = "{$class}_Live";
}
// Determine ID based sorting and filtering, as these aren't considered database fields.
if ($sortValid && $sort[0] === 'ID') {
$sorting[] = "{$class}.ID {$order}";
}
if ($filterValid && isset($filters['ID'])) {
$where["{$class}.ID = ?"] = $filters['ID'];
}
// Make sure this data object type has visibility customisation.
if ($visibility && count($visibility) === count($columns) && in_array('1', $visibility)) {
// Apply any visibility customisation.
$select = ' ';
$iteration = 0;
foreach ($columns as $attribute => $type) {
if (isset($visibility[$iteration]) && $visibility[$iteration]) {
$select .= $attribute . ', ';
if (isset($filtering[$attribute])) {
// Apply the filter if the matching attribute is visible.
$column = is_numeric($filtering[$attribute]) ? $attribute : "LOWER({$attribute})";
$where["{$column} = ?"] = strtolower($filtering[$attribute]);
}
}
$iteration++;
}
if (isset($filtering["{$class}.ClassName"])) {
$where["LOWER({$class}.ClassName) = ?"] = strtolower($filtering["{$class}.ClassName"]);
}
// Grab all data object visible attributes.
$query = new SQLSelect("{$class}.ClassName,{$select}{$class}.ID", $class, $where, $sorting, array(), array(), is_numeric($limit) ? $limit : array());
// Determine the tables with visible attributes to join.
foreach ($from as $join) {
if (strpos($select, " {$join}.") !== false) {
$query->addLeftJoin($join, "{$class}.ID = {$join}.ID");
}
}
$objects = array();
foreach ($query->execute() as $temporary) {
// Return an array of data object maps.
$object = array();
foreach ($temporary as $attribute => $value) {
if ($value) {
$object[$attribute] = $value;
}
}
$objects[] = $object;
}
return $objects;
}
}
// The specified data object type had no visibility customisation.
return null;
}
