/**
* 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)
{
$schema = DataObject::getSchema();
// Join on parent table
$parentIDColumn = $schema->sqlColumnForField($parentClass, 'ID');
$this->query->addLeftJoin($relationTable, "\"{$relationTable}\".\"{$parentField}\" = {$parentIDColumn}");
// Join on base table of component class
$componentBaseClass = $schema->baseDataClass($componentClass);
$componentBaseTable = $schema->tableName($componentBaseClass);
$componentIDColumn = $schema->sqlColumnForField($componentBaseClass, 'ID');
if (!$this->query->isJoinedTo($componentBaseTable)) {
$this->query->addLeftJoin($componentBaseTable, "\"{$relationTable}\".\"{$componentField}\" = {$componentIDColumn}");
}
/**
* 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) {
$ancestorTable = $schema->tableName($ancestor);
if ($ancestorTable != $componentBaseTable && !$this->query->isJoinedTo($ancestorTable)) {
$this->query->addLeftJoin($ancestorTable, "{$componentIDColumn} = \"{$ancestorTable}\".\"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();
}
