/** * 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; }