/** * Join the given has_many relation to this query. * * Doesn't work with polymorphic relationships * * @param string $localClass Name of class that has the has_many to the joined class * @param string $localField Name of the has_many relationship to join * @param string $foreignClass Class to join */ protected function joinHasManyRelation($localClass, $localField, $foreignClass) { if (!$foreignClass || $foreignClass === 'SilverStripe\\ORM\\DataObject') { throw new InvalidArgumentException("Could not find a has_many relationship {$localField} on {$localClass}"); } $schema = DataObject::getSchema(); // Skip if already joined $foreignTable = $schema->tableName($foreignClass); if ($this->query->isJoinedTo($foreignTable)) { return; } // Join table with associated has_one /** @var DataObject $model */ $foreignKey = $schema->getRemoteJoinField($localClass, $localField, 'has_many', $polymorphic); $localIDColumn = $schema->sqlColumnForField($localClass, 'ID'); if ($polymorphic) { $foreignKeyIDColumn = $schema->sqlColumnForField($foreignClass, "{$foreignKey}ID"); $foreignKeyClassColumn = $schema->sqlColumnForField($foreignClass, "{$foreignKey}Class"); $localClassColumn = $schema->sqlColumnForField($localClass, 'ClassName'); $this->query->addLeftJoin($foreignTable, "{$foreignKeyIDColumn} = {$localIDColumn} AND {$foreignKeyClassColumn} = {$localClassColumn}"); } else { $foreignKeyIDColumn = $schema->sqlColumnForField($foreignClass, $foreignKey); $this->query->addLeftJoin($foreignTable, "{$foreignKeyIDColumn} = {$localIDColumn}"); } // Add join clause to the component's ancestry classes so that the search filter could search on // its ancestor fields. $ancestry = ClassInfo::ancestry($foreignClass, true); $ancestry = array_reverse($ancestry); foreach ($ancestry as $ancestor) { $ancestorTable = $schema->tableName($ancestor); if ($ancestorTable !== $foreignTable) { $this->query->addInnerJoin($ancestorTable, "\"{$foreignTable}\".\"ID\" = \"{$ancestorTable}\".\"ID\""); } } }
public function testParameterisedInnerJoins() { $query = new SQLSelect(); $query->setSelect(array('"SQLSelectTest_DO"."Name"', '"SubSelect"."Count"')); $query->setFrom('"SQLSelectTest_DO"'); $query->addInnerJoin('(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" INNER 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(); }
/** * Invoked prior to getFinalisedQuery() * * @param DataQuery $dataQuery * @param array $queriedColumns * @param SQLSelect $sqlSelect */ public function beforeGetFinalisedQuery(DataQuery $dataQuery, $queriedColumns = [], SQLSelect $sqlSelect) { // Get metadata and SQL from join table $hasManyRelation = $this->getParentRelationship($dataQuery); $joinTableSQLSelect = $hasManyRelation->dataQuery()->query(); $joinTableSQL = $joinTableSQLSelect->sql($joinTableParameters); $joinTableColumns = array_keys($joinTableSQLSelect->getSelect()); // Get aliases (keys) only $joinTableAlias = $this->getJoinAlias(); // Get fields to join on $localKey = $this->getLocalKey(); $schema = DataObject::getSchema(); $baseTable = $schema->baseDataClass($dataQuery->dataClass()); $childField = $schema->sqlColumnForField($baseTable, 'ID'); // Add select fields foreach ($joinTableColumns as $joinTableColumn) { $sqlSelect->selectField("\"{$joinTableAlias}\".\"{$joinTableColumn}\"", "{$joinTableAlias}_{$joinTableColumn}"); } // Apply join and record sql for later insertion (at end of replacements) $sqlSelect->addInnerJoin('(SELECT $$_SUBQUERY_$$)', "\"{$joinTableAlias}\".\"{$localKey}\" = {$childField}", $joinTableAlias, 20, $joinTableParameters); $dataQuery->setQueryParam('Foreign.JoinTableSQL', $joinTableSQL); // After this join, and prior to afterGetFinalisedQuery, $sqlSelect will be populated with the // necessary sql rewrites (versioned, etc) that effect the base table. // By using a placeholder for the subquery we can protect the subquery (already rewritten) // from being re-written a second time. However we DO want the join predicate (above) to be rewritten. // See http://php.net/manual/en/function.str-replace.php#refsect1-function.str-replace-notes // for the reason we only add the final substitution at the end of getFinalisedQuery() }