/**
* Update any requests to limit the results to the current site
*/
public function augmentSQL(SQLSelect $query, DataQuery $dataQuery = NULL)
{
if (Subsite::$disable_subsite_filter) {
return;
}
if ($dataQuery->getQueryParam('Subsite.filter') === false) {
return;
}
// If you're querying by ID, ignore the sub-site - this is a bit ugly...
// if(!$query->where || (strpos($query->where[0], ".\"ID\" = ") === false && strpos($query->where[0], ".`ID` = ") === false && strpos($query->where[0], ".ID = ") === false && strpos($query->where[0], "ID = ") !== 0)) {
if ($query->filtersOnID()) {
return;
}
if (Subsite::$force_subsite) {
$subsiteID = Subsite::$force_subsite;
} else {
/*if($context = DataObject::context_obj()) $subsiteID = (int)$context->SubsiteID;
else */
$subsiteID = (int) Subsite::currentSubsiteID();
}
// The foreach is an ugly way of getting the first key :-)
foreach ($query->getFrom() as $tableName => $info) {
// The tableName should be SiteTree or SiteTree_Live...
if (strpos($tableName, 'SiteTree') === false) {
break;
}
$query->addWhere("\"{$tableName}\".\"SubsiteID\" IN ({$subsiteID})");
break;
}
}
/**
* Adds a WHERE clause.
*
* @see SQLSelect::addWhere() for syntax examples, although DataQuery
* won't expand multiple arguments as SQLSelect does.
*
* @param string|array|SQLConditionGroup $filter Predicate(s) to set, as escaped SQL statements or
* paramaterised queries
* @return DataQuery
*/
public function where($filter)
{
if ($filter) {
$this->query->addWhere($filter);
}
return $this;
}
/**
* Update any requests to limit the results to the current site
*/
public function augmentSQL(SQLSelect $query, DataQuery $dataQuery = NULL)
{
if (Subsite::$disable_subsite_filter) {
return;
}
// If you're querying by ID, ignore the sub-site - this is a bit ugly...
if ($query->filtersOnID()) {
return;
}
$regexp = '/^(.*\\.)?("|`)?SubsiteID("|`)?\\s?=/';
foreach ($query->getWhereParameterised($parameters) as $predicate) {
if (preg_match($regexp, $predicate)) {
return;
}
}
try {
$subsiteID = (int) Subsite::currentSubsiteID();
$froms = $query->getFrom();
$froms = array_keys($froms);
$tableName = array_shift($froms);
if ($tableName != 'SiteConfig') {
return;
}
$query->addWhere("\"{$tableName}\".\"SubsiteID\" IN ({$subsiteID})");
} catch (UnexpectedValueException $e) {
// No subsites exist yet
}
}
/**
* Update any requests to limit the results to the current site
*/
public function augmentSQL(SQLSelect $query)
{
if (Subsite::$disable_subsite_filter) {
return;
}
// If you're querying by ID, ignore the sub-site - this is a bit ugly...
if ($query->filtersOnID()) {
return;
}
$regexp = '/^(.*\\.)?("|`)?SubsiteID("|`)?\\s?=/';
foreach ($query->getWhereParameterised($parameters) as $predicate) {
if (preg_match($regexp, $predicate)) {
return;
}
}
/*if($context = DataObject::context_obj()) $subsiteID = (int)$context->SubsiteID;
else */
$subsiteID = (int) Subsite::currentSubsiteID();
$froms = $query->getFrom();
$froms = array_keys($froms);
$tableName = array_shift($froms);
if ($tableName != 'SiteConfig') {
return;
}
$query->addWhere("\"{$tableName}\".\"SubsiteID\" IN ({$subsiteID})");
}
/**
* Update any requests to limit the results to the current site
*/
public function augmentSQL(SQLSelect $query)
{
if (Subsite::$disable_subsite_filter) {
return;
}
// If you're querying by ID, ignore the sub-site - this is a bit ugly... (but it was WAYYYYYYYYY worse)
//@TODO I don't think excluding if SiteTree_ImageTracking is a good idea however because of the SS 3.0 api and ManyManyList::removeAll() changing the from table after this function is called there isn't much of a choice
$from = $query->getFrom();
if (isset($from['SiteTree_ImageTracking']) || $query->filtersOnID()) {
return;
}
$subsiteID = (int) Subsite::currentSubsiteID();
// The foreach is an ugly way of getting the first key :-)
foreach ($query->getFrom() as $tableName => $info) {
$where = "\"{$tableName}\".\"SubsiteID\" IN (0, {$subsiteID})";
$query->addWhere($where);
break;
}
$sect = array_values($query->getSelect());
$isCounting = strpos($sect[0], 'COUNT') !== false;
// Ordering when deleting or counting doesn't apply
if (!$isCounting) {
$query->addOrderBy("\"SubsiteID\"");
}
}
public function testSelectWithWhereClauseFilter()
{
$query = new SQLSelect();
$query->setSelect(array("Name", "Meta"));
$query->setFrom("MyTable");
$query->setWhere("Name = 'Name'");
$query->addWhere("Meta = 'Test'");
$this->assertSQLEquals("SELECT Name, Meta FROM MyTable WHERE (Name = 'Name') AND (Meta = 'Test')", $query->sql($parameters));
}
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();
}
/**
* Update any requests to limit the results to the current site
*/
public function augmentSQL(SQLSelect $query, DataQuery $dataQuery = null)
{
if (Subsite::$disable_subsite_filter) {
return;
}
if (Cookie::get('noSubsiteFilter') == 'true') {
return;
}
// If you're querying by ID, ignore the sub-site - this is a bit ugly...
if (!$query->filtersOnID()) {
/*if($context = DataObject::context_obj()) $subsiteID = (int)$context->SubsiteID;
else */
$subsiteID = (int) Subsite::currentSubsiteID();
// Don't filter by Group_Subsites if we've already done that
$hasGroupSubsites = false;
foreach ($query->getFrom() as $item) {
if (is_array($item) && strpos($item['table'], 'Group_Subsites') !== false || !is_array($item) && strpos($item, 'Group_Subsites') !== false) {
$hasGroupSubsites = true;
break;
}
}
if (!$hasGroupSubsites) {
if ($subsiteID) {
$query->addLeftJoin("Group_Subsites", "\"Group_Subsites\".\"GroupID\" \n\t\t\t\t\t\t= \"Group\".\"ID\" AND \"Group_Subsites\".\"SubsiteID\" = {$subsiteID}");
$query->addWhere("(\"Group_Subsites\".\"SubsiteID\" IS NOT NULL OR\n\t\t\t\t\t\t\"Group\".\"AccessAllSubsites\" = 1)");
} else {
$query->addWhere("\"Group\".\"AccessAllSubsites\" = 1");
}
}
// WORKAROUND for databases that complain about an ORDER BY when the column wasn't selected (e.g. SQL Server)
$select = $query->getSelect();
if (isset($select[0]) && !$select[0] == 'COUNT(*)') {
$query->orderby = "\"AccessAllSubsites\" DESC" . ($query->orderby ? ', ' : '') . $query->orderby;
}
}
}
/**
* Augment the the SQLSelect that is created by the DataQuery
* @todo Should this all go into VersionedDataQuery?
*/
public function augmentSQL(SQLSelect $query, DataQuery $dataQuery = null)
{
if (!$dataQuery || !$dataQuery->getQueryParam('Versioned.mode')) {
return;
}
$baseTable = ClassInfo::baseDataClass($dataQuery->dataClass());
switch ($dataQuery->getQueryParam('Versioned.mode')) {
// Reading a specific data from the archive
case 'archive':
$date = $dataQuery->getQueryParam('Versioned.date');
foreach ($query->getFrom() as $table => $dummy) {
if (!DB::get_schema()->hasTable($table . '_versions')) {
continue;
}
$query->renameTable($table, $table . '_versions');
$query->replaceText("\"{$table}_versions\".\"ID\"", "\"{$table}_versions\".\"RecordID\"");
$query->replaceText("`{$table}_versions`.`ID`", "`{$table}_versions`.`RecordID`");
// Add all <basetable>_versions columns
foreach (Config::inst()->get('Versioned', 'db_for_versions_table') as $name => $type) {
$query->selectField(sprintf('"%s_versions"."%s"', $baseTable, $name), $name);
}
$query->selectField(sprintf('"%s_versions"."%s"', $baseTable, 'RecordID'), "ID");
if ($table != $baseTable) {
$query->addWhere("\"{$table}_versions\".\"Version\" = \"{$baseTable}_versions\".\"Version\"");
}
}
// Link to the version archived on that date
$query->addWhere(array("\"{$baseTable}_versions\".\"Version\" IN\n\t\t\t\t(SELECT LatestVersion FROM\n\t\t\t\t\t(SELECT\n\t\t\t\t\t\t\"{$baseTable}_versions\".\"RecordID\",\n\t\t\t\t\t\tMAX(\"{$baseTable}_versions\".\"Version\") AS LatestVersion\n\t\t\t\t\t\tFROM \"{$baseTable}_versions\"\n\t\t\t\t\t\tWHERE \"{$baseTable}_versions\".\"LastEdited\" <= ?\n\t\t\t\t\t\tGROUP BY \"{$baseTable}_versions\".\"RecordID\"\n\t\t\t\t\t) AS \"{$baseTable}_versions_latest\"\n\t\t\t\t\tWHERE \"{$baseTable}_versions_latest\".\"RecordID\" = \"{$baseTable}_versions\".\"RecordID\"\n\t\t\t\t)" => $date));
break;
// Reading a specific stage (Stage or Live)
// Reading a specific stage (Stage or Live)
case 'stage':
$stage = $dataQuery->getQueryParam('Versioned.stage');
if ($stage && $stage != $this->defaultStage) {
foreach ($query->getFrom() as $table => $dummy) {
// Only rewrite table names that are actually part of the subclass tree
// This helps prevent rewriting of other tables that get joined in, in
// particular, many_many tables
if (class_exists($table) && ($table == $this->owner->class || is_subclass_of($table, $this->owner->class) || is_subclass_of($this->owner->class, $table))) {
$query->renameTable($table, $table . '_' . $stage);
}
}
}
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':
$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 ($this->stages as $excluding) {
if ($excluding == $stage) {
continue;
}
$tempName = 'ExclusionarySource_' . $excluding;
$excludingTable = $baseTable . ($excluding && $excluding != $this->defaultStage ? "_{$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 'all_versions':
case 'latest_versions':
foreach ($query->getFrom() as $alias => $join) {
if ($alias != $baseTable) {
$query->setJoinFilter($alias, "\"{$alias}\".\"RecordID\" = \"{$baseTable}_versions\".\"RecordID\"" . " AND \"{$alias}\".\"Version\" = \"{$baseTable}_versions\".\"Version\"");
}
$query->renameTable($alias, $alias . '_versions');
}
// Add all <basetable>_versions columns
foreach (Config::inst()->get('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
$query->selectField(sprintf('"%s_versions"."%s"', $baseTable, 'RecordID'), "ID");
// Ensure that any sort order referring to this ID is correctly aliased
$orders = $query->getOrderBy();
foreach ($orders as $order => $dir) {
if ($order === "\"{$baseTable}\".\"ID\"") {
unset($orders[$order]);
$orders["\"{$baseTable}_versions\".\"RecordID\""] = $dir;
}
}
$query->setOrderBy($orders);
// latest_version has one more step
// Return latest version instances, regardless of whether they are on a particular stage
// This provides "show all, including deleted" functonality
if ($dataQuery->getQueryParam('Versioned.mode') == 'latest_versions') {
$query->addWhere("\"{$alias}_versions\".\"Version\" IN\n\t\t\t\t\t(SELECT LatestVersion FROM\n\t\t\t\t\t\t(SELECT\n\t\t\t\t\t\t\t\"{$alias}_versions\".\"RecordID\",\n\t\t\t\t\t\t\tMAX(\"{$alias}_versions\".\"Version\") AS LatestVersion\n\t\t\t\t\t\t\tFROM \"{$alias}_versions\"\n\t\t\t\t\t\t\tGROUP BY \"{$alias}_versions\".\"RecordID\"\n\t\t\t\t\t\t) AS \"{$alias}_versions_latest\"\n\t\t\t\t\t\tWHERE \"{$alias}_versions_latest\".\"RecordID\" = \"{$alias}_versions\".\"RecordID\"\n\t\t\t\t\t)");
} else {
// If all versions are requested, ensure that records are sorted by this field
$query->addOrderBy(sprintf('"%s_versions"."%s"', $baseTable, 'Version'));
}
break;
default:
throw new InvalidArgumentException("Bad value for query parameter Versioned.mode: " . $dataQuery->getQueryParam('Versioned.mode'));
}
}
/**
* 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->localKey}\"" => $itemID));
$queryResult = $query->execute()->current();
if ($queryResult) {
foreach ($queryResult as $fieldName => $value) {
$result[$fieldName] = $value;
}
}
return $result;
}
/**
* Add an item to this many_many relationship
* Does so by adding an entry to the joinTable.
*
* @param mixed $item
* @param array $extraFields A map of additional columns to insert into the joinTable.
* Column names should be ANSI quoted.
*/
public function add($item, $extraFields = array())
{
// Ensure nulls or empty strings are correctly treated as empty arrays
if (empty($extraFields)) {
$extraFields = array();
}
// Determine ID of new record
if (is_numeric($item)) {
$itemID = $item;
} elseif ($item instanceof $this->dataClass) {
$itemID = $item->ID;
} else {
throw new InvalidArgumentException("ManyManyList::add() expecting a {$this->dataClass} object, or ID value", E_USER_ERROR);
}
// Validate foreignID
$foreignIDs = $this->getForeignID();
if (empty($foreignIDs)) {
throw new Exception("ManyManyList::add() can't be called until a foreign ID is set", E_USER_WARNING);
}
// Apply this item to each given foreign ID record
if (!is_array($foreignIDs)) {
$foreignIDs = array($foreignIDs);
}
foreach ($foreignIDs as $foreignID) {
// Check for existing records for this item
if ($foreignFilter = $this->foreignIDWriteFilter($foreignID)) {
// With the current query, simply add the foreign and local conditions
// The query can be a bit odd, especially if custom relation classes
// don't join expected tables (@see Member_GroupSet for example).
$query = new SQLSelect("*", "\"{$this->joinTable}\"");
$query->addWhere($foreignFilter);
$query->addWhere(array("\"{$this->joinTable}\".\"{$this->localKey}\"" => $itemID));
$hasExisting = $query->count() > 0;
} else {
$hasExisting = false;
}
// Blank manipulation
$manipulation = array($this->joinTable => array('command' => $hasExisting ? 'update' : 'insert', 'fields' => array()));
if ($hasExisting) {
$manipulation[$this->joinTable]['where'] = array("\"{$this->joinTable}\".\"{$this->foreignKey}\"" => $foreignID, "\"{$this->joinTable}\".\"{$this->localKey}\"" => $itemID);
}
if ($extraFields && $this->extraFields) {
// Write extra field to manipluation in the same way
// that DataObject::prepareManipulationTable writes fields
foreach ($this->extraFields as $fieldName => $fieldSpec) {
// Skip fields without an assignment
if (array_key_exists($fieldName, $extraFields)) {
$fieldObject = Object::create_from_string($fieldSpec, $fieldName);
$fieldObject->setValue($extraFields[$fieldName]);
$fieldObject->writeToManipulation($manipulation[$this->joinTable]);
}
}
}
$manipulation[$this->joinTable]['fields'][$this->localKey] = $itemID;
$manipulation[$this->joinTable]['fields'][$this->foreignKey] = $foreignID;
DB::manipulate($manipulation);
}
}
/**
* Changes any SELECT query thats not filtering on an ID
* to limit by the current language defined in {@link get_current_locale()}.
* It falls back to "Locale='' OR Lang IS NULL" and assumes that
* this implies querying for the default language.
*
* Use {@link disable_locale_filter()} to temporarily disable this "auto-filtering".
*/
public function augmentSQL(SQLSelect $query, DataQuery $dataQuery = null)
{
// If the record is saved (and not a singleton), and has a locale,
// limit the current call to its locale. This fixes a lot of problems
// with other extensions like Versioned
if ($this->owner->ID && !empty($this->owner->Locale)) {
$locale = $this->owner->Locale;
} else {
$locale = Translatable::get_current_locale();
}
$baseTable = ClassInfo::baseDataClass($this->owner->class);
if ($locale && self::locale_filter_enabled() && $dataQuery->getQueryParam(self::QUERY_LOCALE_FILTER_ENABLED) && !$query->filtersOnID() && array_search($baseTable, array_keys($query->getFrom())) !== false) {
// Or we're already filtering by Lang (either from an earlier augmentSQL()
// call or through custom SQL filters)
$filtersOnLocale = array_filter($query->getWhere(), function ($predicates) {
foreach ($predicates as $predicate => $params) {
if (preg_match('/("|\'|`)Locale("|\'|`)/', $predicate)) {
return true;
}
}
});
if (!$filtersOnLocale) {
$qry = sprintf('"%s"."Locale" = \'%s\'', $baseTable, Convert::raw2sql($locale));
$query->addWhere($qry);
}
}
}
/**
* 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();
if (!is_numeric($itemID)) {
user_error('ComponentSet::getExtraData() passed a non-numeric child ID', E_USER_ERROR);
}
// @todo Optimize into a single query instead of one per extra field
if ($this->extraFields) {
foreach ($this->extraFields as $fieldName => $dbFieldSpec) {
$query = new SQLSelect("\"{$fieldName}\"", "\"{$this->joinTable}\"");
if ($filter = $this->foreignIDWriteFilter($this->getForeignID())) {
$query->setWhere($filter);
} else {
user_error("Can't call ManyManyList::getExtraData() until a foreign ID is set", E_USER_WARNING);
}
$query->addWhere("\"{$this->localKey}\" = {$itemID}");
$result[$fieldName] = $query->execute()->value();
}
}
return $result;
}
/**
* Are new posts available?
*
* @param int $id
* @param array $data Optional: If an array is passed, the timestamp of
* the last created post and it's ID will be stored in
* it (keys: 'last_id', 'last_created')
* @param int $lastVisit Unix timestamp of the last visit (GMT)
* @param int $lastPostID ID of the last read post
* @param int $thread ID of the relevant topic (set to NULL for all
* topics)
* @return bool Returns TRUE if there are new posts available, otherwise
* FALSE.
*/
public static function new_posts_available($id, &$data = array(), $lastVisit = null, $lastPostID = null, $forumID = null, $threadID = null)
{
// last post viewed
$query = new SQLSelect(array('LastID' => 'MAX("Post"."ID")', 'LastCreated' => 'MAX("Post"."Created")'), '"Post"', array('"ForumPage"."ParentID" = ?' => $id));
$query->addInnerJoin(ForumHolder::baseForumTable(), '"Post"."ForumID" = "ForumPage"."ID"', 'ForumPage');
// Filter by parameters specified
if ($lastPostID) {
$query->addWhere(array('"Post"."ID" > ?' => $lastPostID));
}
if ($lastVisit) {
$query->addWhere(array('"Post"."Created" > ?' => $lastVisit));
}
if ($forumID) {
$query->addWhere(array('"Post"."ForumID" = ?' => $forumID));
}
if ($threadID) {
$query->addWhere(array('"Post"."ThreadID" = ?' => $threadID));
}
// Run
$version = $query->execute()->first();
if (!$version) {
return false;
}
if ($data) {
$data['last_id'] = (int) $version['LastID'];
$data['last_created'] = strtotime($version['LastCreated']);
}
$lastVisit = (int) $lastVisit;
if ($lastVisit <= 0) {
$lastVisit = false;
}
$lastPostID = (int) $lastPostID;
if ($lastPostID <= 0) {
$lastPostID = false;
}
if (!$lastVisit && !$lastPostID) {
return true;
}
if ($lastVisit && strtotime($version['LastCreated']) > $lastVisit) {
return true;
}
if ($lastPostID && (int) $version['LastID'] > $lastPostID) {
return true;
}
return false;
}
