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