/**
* 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()
}
