public function where($filter) { if ($filter) { $this->whereQuery->addWhere($filter); } return $this; }
/** * Find the extra field data for a single row of the relationship join * table, given the known child ID. * * @param string $componentName The name of the component * @param int $itemID The ID of the child for the relationship * * @return array Map of fieldName => fieldValue */ public function getExtraData($componentName, $itemID) { $result = array(); // Skip if no extrafields or unsaved record if (empty($this->extraFields) || empty($itemID)) { return $result; } if (!is_numeric($itemID)) { user_error('ComponentSet::getExtraData() passed a non-numeric child ID', E_USER_ERROR); } $cleanExtraFields = array(); foreach ($this->extraFields as $fieldName => $dbFieldSpec) { $cleanExtraFields[] = "\"{$fieldName}\""; } $query = new SQLSelect($cleanExtraFields, "\"{$this->joinTable}\""); $filter = $this->foreignIDWriteFilter($this->getForeignID()); if ($filter) { $query->setWhere($filter); } else { user_error("Can't call ManyManyList::getExtraData() until a foreign ID is set", E_USER_WARNING); } $query->addWhere(array("\"{$this->joinTable}\".\"{$this->localKey}\"" => $itemID)); $queryResult = $query->execute()->current(); if ($queryResult) { foreach ($queryResult as $fieldName => $value) { $result[$fieldName] = $value; } } return $result; }
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(); }
/** * Set foreign keys of has_many objects to 0 where those objects were * disowned as a result of a partial publish / unpublish. * I.e. this object and its owned objects were recently written to $targetStage, * but deleted objects were not. * * Note that this operation does not create any new Versions * * @param string $sourceStage Objects in this stage will not be unlinked. * @param string $targetStage Objects which exist in this stage but not $sourceStage * will be unlinked. */ public function unlinkDisownedObjects($sourceStage, $targetStage) { $owner = $this->owner; // after publishing, objects which used to be owned need to be // dis-connected from this object (set ForeignKeyID = 0) $owns = $owner->config()->owns; $hasMany = $owner->config()->has_many; if (empty($owns) || empty($hasMany)) { return; } $ownedHasMany = array_intersect($owns, array_keys($hasMany)); foreach ($ownedHasMany as $relationship) { // Find metadata on relationship $joinClass = $owner->hasManyComponent($relationship); $joinField = $owner->getRemoteJoinField($relationship, 'has_many', $polymorphic); $idField = $polymorphic ? "{$joinField}ID" : $joinField; $joinTable = DataObject::getSchema()->tableForField($joinClass, $idField); // Generate update query which will unlink disowned objects $targetTable = $this->stageTable($joinTable, $targetStage); $disowned = new SQLUpdate("\"{$targetTable}\""); $disowned->assign("\"{$idField}\"", 0); $disowned->addWhere(array("\"{$targetTable}\".\"{$idField}\"" => $owner->ID)); // Build exclusion list (items to owned objects we need to keep) $sourceTable = $this->stageTable($joinTable, $sourceStage); $owned = new SQLSelect("\"{$sourceTable}\".\"ID\"", "\"{$sourceTable}\""); $owned->addWhere(array("\"{$sourceTable}\".\"{$idField}\"" => $owner->ID)); // Apply class condition if querying on polymorphic has_one if ($polymorphic) { $disowned->assign("\"{$joinField}Class\"", null); $disowned->addWhere(array("\"{$targetTable}\".\"{$joinField}Class\"" => get_class($owner))); $owned->addWhere(array("\"{$sourceTable}\".\"{$joinField}Class\"" => get_class($owner))); } // Merge queries and perform unlink $ownedSQL = $owned->sql($ownedParams); $disowned->addWhere(array("\"{$targetTable}\".\"ID\" NOT IN ({$ownedSQL})" => $ownedParams)); $owner->extend('updateDisownershipQuery', $disowned, $sourceStage, $targetStage, $relationship); $disowned->execute(); } }