/** * Augment the the SQLSelect that is created by the DataQuery * * See {@see augmentLazyLoadFields} for lazy-loading applied prior to this. * * @param SQLSelect $query * @param DataQuery $dataQuery * @throws InvalidArgumentException */ public function augmentSQL(SQLSelect $query, DataQuery $dataQuery = null) { if (!$dataQuery || !$dataQuery->getQueryParam('Versioned.mode')) { return; } $baseTable = $this->baseTable(); $versionedMode = $dataQuery->getQueryParam('Versioned.mode'); switch ($versionedMode) { // Reading a specific stage (Stage or Live) case 'stage': // Check if we need to rewrite this table $stage = $dataQuery->getQueryParam('Versioned.stage'); if (!$this->hasStages() || $stage === static::DRAFT) { break; } // Rewrite all tables to select from the live version foreach ($query->getFrom() as $table => $dummy) { if (!$this->isTableVersioned($table)) { continue; } $stageTable = $this->stageTable($table, $stage); $query->renameTable($table, $stageTable); } break; // Reading a specific stage, but only return items that aren't in any other stage // Reading a specific stage, but only return items that aren't in any other stage case 'stage_unique': if (!$this->hasStages()) { break; } $stage = $dataQuery->getQueryParam('Versioned.stage'); // Recurse to do the default stage behavior (must be first, we rely on stage renaming happening before // below) $dataQuery->setQueryParam('Versioned.mode', 'stage'); $this->augmentSQL($query, $dataQuery); $dataQuery->setQueryParam('Versioned.mode', 'stage_unique'); // Now exclude any ID from any other stage. Note that we double rename to avoid the regular stage rename // renaming all subquery references to be Versioned.stage foreach ([static::DRAFT, static::LIVE] as $excluding) { if ($excluding == $stage) { continue; } $tempName = 'ExclusionarySource_' . $excluding; $excludingTable = $this->baseTable($excluding); $query->addWhere('"' . $baseTable . '"."ID" NOT IN (SELECT "ID" FROM "' . $tempName . '")'); $query->renameTable($tempName, $excludingTable); } break; // Return all version instances // Return all version instances case 'archive': case 'all_versions': case 'latest_versions': case 'version': foreach ($query->getFrom() as $alias => $join) { if (!$this->isTableVersioned($alias)) { continue; } if ($alias != $baseTable) { // Make sure join includes version as well $query->setJoinFilter($alias, "\"{$alias}_versions\".\"RecordID\" = \"{$baseTable}_versions\".\"RecordID\"" . " AND \"{$alias}_versions\".\"Version\" = \"{$baseTable}_versions\".\"Version\""); } $query->renameTable($alias, $alias . '_versions'); } // Add all <basetable>_versions columns foreach (Config::inst()->get('SilverStripe\\ORM\\Versioning\\Versioned', 'db_for_versions_table') as $name => $type) { $query->selectField(sprintf('"%s_versions"."%s"', $baseTable, $name), $name); } // Alias the record ID as the row ID, and ensure ID filters are aliased correctly $query->selectField("\"{$baseTable}_versions\".\"RecordID\"", "ID"); $query->replaceText("\"{$baseTable}_versions\".\"ID\"", "\"{$baseTable}_versions\".\"RecordID\""); // However, if doing count, undo rewrite of "ID" column $query->replaceText("count(DISTINCT \"{$baseTable}_versions\".\"RecordID\")", "count(DISTINCT \"{$baseTable}_versions\".\"ID\")"); // Add additional versioning filters switch ($versionedMode) { case 'archive': $date = $dataQuery->getQueryParam('Versioned.date'); if (!$date) { throw new InvalidArgumentException("Invalid archive date"); } // Link to the version archived on that date $query->addWhere(["\"{$baseTable}_versions\".\"Version\" IN\n\t\t\t\t\t\t(SELECT LatestVersion FROM\n\t\t\t\t\t\t\t(SELECT\n\t\t\t\t\t\t\t\t\"{$baseTable}_versions\".\"RecordID\",\n\t\t\t\t\t\t\t\tMAX(\"{$baseTable}_versions\".\"Version\") AS LatestVersion\n\t\t\t\t\t\t\t\tFROM \"{$baseTable}_versions\"\n\t\t\t\t\t\t\t\tWHERE \"{$baseTable}_versions\".\"LastEdited\" <= ?\n\t\t\t\t\t\t\t\tGROUP BY \"{$baseTable}_versions\".\"RecordID\"\n\t\t\t\t\t\t\t) AS \"{$baseTable}_versions_latest\"\n\t\t\t\t\t\t\tWHERE \"{$baseTable}_versions_latest\".\"RecordID\" = \"{$baseTable}_versions\".\"RecordID\"\n\t\t\t\t\t\t)" => $date]); break; case 'latest_versions': // Return latest version instances, regardless of whether they are on a particular stage // This provides "show all, including deleted" functonality $query->addWhere("\"{$baseTable}_versions\".\"Version\" IN\n\t\t\t\t\t\t(SELECT LatestVersion FROM\n\t\t\t\t\t\t\t(SELECT\n\t\t\t\t\t\t\t\t\"{$baseTable}_versions\".\"RecordID\",\n\t\t\t\t\t\t\t\tMAX(\"{$baseTable}_versions\".\"Version\") AS LatestVersion\n\t\t\t\t\t\t\t\tFROM \"{$baseTable}_versions\"\n\t\t\t\t\t\t\t\tGROUP BY \"{$baseTable}_versions\".\"RecordID\"\n\t\t\t\t\t\t\t) AS \"{$baseTable}_versions_latest\"\n\t\t\t\t\t\t\tWHERE \"{$baseTable}_versions_latest\".\"RecordID\" = \"{$baseTable}_versions\".\"RecordID\"\n\t\t\t\t\t\t)"); break; case 'version': // If selecting a specific version, filter it here $version = $dataQuery->getQueryParam('Versioned.version'); if (!$version) { throw new InvalidArgumentException("Invalid version"); } $query->addWhere(["\"{$baseTable}_versions\".\"Version\"" => $version]); break; case 'all_versions': default: // If all versions are requested, ensure that records are sorted by this field $query->addOrderBy(sprintf('"%s_versions"."%s"', $baseTable, 'Version')); break; } break; default: throw new InvalidArgumentException("Bad value for query parameter Versioned.mode: " . $dataQuery->getQueryParam('Versioned.mode')); } }