public function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null) { // Actives locales defined on a SiteConfig are there as a global setting if ($this->owner instanceof SiteConfig) { return; } // In admin, show everthing anyway if ($this->isAdminBackend()) { return; } // Find in set is only compatible with MySql $c = DB::getConn(); if (!$c instanceof MySQLDatabase) { return; } $locale = $dataQuery->getQueryParam('Fluent.Locale') ?: Fluent::current_locale(); $from = $query->getFrom(); $where = $query->getWhere(); $column = 'ActiveLocales'; $table = null; // Check on which table is the ActiveLocales field foreach ($from as $fromTable => $conditions) { if ($table === null) { $table = $fromTable; } $db = DataObject::custom_database_fields($fromTable); if ($db && isset($db[$column])) { $table = $fromTable; break; } } $identifier = "\"{$table}\".\"{$column}\""; $where[] = "{$identifier} IS NULL OR FIND_IN_SET ('{$locale}', {$identifier}) > 0"; $query->setWhere($where); }
/** * Update any requests to limit the results to the current site */ public function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null) { $ctrl = null; if (Controller::has_curr()) { $ctrl = Controller::curr(); } if (Subsite::$disable_subsite_filter) { return; } if ($dataQuery->getQueryParam('Subsite.filter') === false) { return; } if ($ctrl && get_class(Controller::curr()) == 'Security') { return; } // Don't run on delete queries, since they are always tied to // a specific ID. if ($query->getDelete()) { 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()) { if (Subsite::$force_subsite) { $subsiteID = Subsite::$force_subsite; } else { $subsiteID = (int) Subsite::currentSubsiteID(); } $froms = $query->getFrom(); $froms = array_keys($froms); $tableName = array_shift($froms); $query->addWhere("\"{$tableName}\".\"SubsiteID\" IN ({$subsiteID})"); } }
/** * 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; } }
/** * Update any requests to limit the results to the current site */ function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null) { if (Subsite::$disable_subsite_filter) { return; } if ($dataQuery->getQueryParam('Subsite.filter') === false) { return; } // Don't run on delete queries, since they are always tied to // a specific ID. if ($query->getDelete()) { 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->where || !preg_match('/\\.(\'|"|`|)ID(\'|"|`|)( ?)=/', $query->where[0])) { 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, $this->owner->ClassName) === false) { break; } $query->addWhere("\"{$tableName}\".\"SubsiteID\" IN ({$subsiteID})"); break; } } }
/** * Update any requests to limit the results to the current site */ public function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null) { // Filters are disabled globally if (self::$disable) { return; } // Filters are disabled for this query if ($dataQuery->getQueryParam('SoftDeletable.filter') === false) { return; } // Don't run on delete queries, since they are always tied to a specific ID. if ($query->getDelete()) { return; } // Don't run if querying by ID if ($query->filtersOnID()) { return; } $froms = $query->getFrom(); $froms = array_keys($froms); $tableName = array_shift($froms); $query->addWhere("\"{$tableName}\".\"Deleted\" IS NULL"); }
public function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null) { if (!FluentOldPageRedirectFix::$disableSkipIDFilter) { // Skip ID based filters if ($query->filtersOnID()) { return; } } // Skip filter in the CMS, unless filtering is explicitly turned on $filterAdmin = $dataQuery->getQueryParam('Fluent.FilterAdmin'); if (!$filterAdmin) { $isFrontend = $dataQuery->getQueryParam('Fluent.IsFrontend'); if ($isFrontend === null) { $isFrontend = Fluent::is_frontend(); } if (!$isFrontend) { return; } } // Add filter for locale $locale = $dataQuery->getQueryParam('Fluent.Locale') ?: Fluent::current_locale(); $query->addWhere("\"{$this->ownerBaseClass}\".\"LocaleFilter_{$locale}\" = 1"); }
public function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null) { // Get locale and translation zone to use $locale = $dataQuery->getQueryParam('Fluent.Locale') ?: Fluent::current_locale(); // Get all tables to translate fields for, and their respective field names $includedTables = $this->getTranslatedTables(); // Iterate through each select clause, replacing each with the translated version foreach ($query->getSelect() as $alias => $select) { // Skip fields without table context if (!preg_match('/^"(?<class>\\w+)"\\."(?<field>\\w+)"$/i', $select, $matches)) { continue; } $class = $matches['class']; $field = $matches['field']; // If this table doesn't have translated fields then skip if (empty($includedTables[$class])) { continue; } // If this field shouldn't be translated, skip if (!in_array($field, $includedTables[$class])) { continue; } // Select visible field from translated fields (Title_fr_FR || Title => Title) $translatedField = Fluent::db_field_for_locale($field, $locale); $expression = $this->localiseSelect($class, $translatedField, $field); $query->selectField($expression, $alias); } // Rewrite where conditions $where = $query->getWhere(); foreach ($where as $index => $condition) { // determine the table/column this condition is against $filterColumn = $this->detectFilterColumn($condition, $includedTables, $locale); if (empty($filterColumn)) { continue; } // Duplicate the condition with all localisable fields replaced $localisedCondition = $this->localiseFilterCondition($condition, $includedTables, $locale); if ($localisedCondition === $condition) { continue; } // Generate new condition that conditionally executes one of the two conditions // depending on field nullability. // If the filterColumn is null or empty, then it's considered untranslated, and // thus the query should continue running on the default column unimpeded. $where[$index] = "\n\t\t\t\t({$filterColumn} IS NOT NULL AND {$filterColumn} != '' AND ({$localisedCondition}))\n\t\t\t\tOR (\n\t\t\t\t\t({$filterColumn} IS NULL OR {$filterColumn} = '') AND ({$condition})\n\t\t\t\t)"; } $query->setWhere($where); // Augment search if applicable if ($adapter = Fluent::search_adapter()) { $adapter->augmentSearch($query, $dataQuery); } }
public function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null) { // Get locale and translation zone to use $default = Fluent::default_locale(); $locale = $dataQuery->getQueryParam('Fluent.Locale') ?: Fluent::current_locale(); // Get all tables to translate fields for, and their respective field names $includedTables = $this->getTranslatedTables(); // Iterate through each select clause, replacing each with the translated version foreach ($query->getSelect() as $alias => $select) { // Skip fields without table context if (!preg_match('/^"(?<class>\\w+)"\\."(?<field>\\w+)"$/i', $select, $matches)) { continue; } $class = $matches['class']; $field = $matches['field']; // If this table doesn't have translated fields then skip if (empty($includedTables[$class])) { continue; } // If this field shouldn't be translated, skip if (!in_array($field, $includedTables[$class])) { continue; } // Select visible field from translated fields (Title_fr_FR || Title => Title) $translatedField = Fluent::db_field_for_locale($field, $locale); $expression = $this->localiseSelect($class, $translatedField, $field); $query->selectField($expression, $alias); // At the same time, rewrite the selector for the default field to make sure that // (in the case it is blank, which happens if installing fluent for the first time) // that it also populated from the root field. $defaultField = Fluent::db_field_for_locale($field, $default); $defaultExpression = $this->localiseSelect($class, $defaultField, $field); $query->selectField($defaultExpression, $defaultField); } // Rewrite where conditions with parameterised query (3.2 +) $where = $query->toAppropriateExpression()->getWhere(); foreach ($where as $index => $condition) { // Extract parameters from condition if ($condition instanceof SQLConditionGroup) { $parameters = array(); $predicate = $condition->conditionSQL($parameters); } else { $parameters = array_values(reset($condition)); $predicate = key($condition); } // determine the table/column this condition is against $filterColumn = $this->detectFilterColumn($predicate, $includedTables, $locale); if (empty($filterColumn)) { continue; } // Duplicate the condition with all localisable fields replaced $localisedPredicate = $this->localiseFilterCondition($predicate, $includedTables, $locale); if ($localisedPredicate === $predicate) { continue; } // Generate new condition that conditionally executes one of the two conditions // depending on field nullability. // If the filterColumn is null or empty, then it's considered untranslated, and // thus the query should continue running on the default column unimpeded. $castColumn = "COALESCE(CAST({$filterColumn} AS CHAR), '')"; $newPredicate = "\n\t\t\t\t({$castColumn} != '' AND {$castColumn} != '0' AND ({$localisedPredicate}))\n\t\t\t\tOR (\n\t\t\t\t\t({$castColumn} = '' OR {$castColumn} = '0') AND ({$predicate})\n\t\t\t\t)"; // Duplicate this condition with parameters duplicated $where[$index] = array($newPredicate => array_merge($parameters, $parameters)); } $query->setWhere($where); // Augment search if applicable if ($adapter = Fluent::search_adapter()) { $adapter->augmentSearch($query, $dataQuery); } }
/** * Augment the the SQLQuery that is created by the DataQuery * @todo Should this all go into VersionedDataQuery? */ function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery) { $baseTable = ClassInfo::baseDataClass($dataQuery->dataClass()); switch ($dataQuery->getQueryParam('Versioned.mode')) { // Noop case '': break; // Reading a specific data from the archive // Reading a specific data from the archive case 'archive': $date = $dataQuery->getQueryParam('Versioned.date'); foreach ($query->from as $table => $dummy) { $query->renameTable($table, $table . '_versions'); $query->replaceText("\"{$table}\".\"ID\"", "\"{$table}\".\"RecordID\""); // Add all <basetable>_versions columns foreach (self::$db_for_versions_table as $name => $type) { $query->select[] = sprintf('"%s_versions"."%s"', $baseTable, $name); } $query->select[] = sprintf('"%s_versions"."%s" AS "ID"', $baseTable, 'RecordID'); if ($table != $baseTable) { $query->from[$table] .= " AND \"{$table}_versions\".\"Version\" = \"{$baseTable}_versions\".\"Version\""; } } // Link to the version archived on that date $archiveTable = $this->requireArchiveTempTable($baseTable, $date); $query->from[$archiveTable] = "INNER JOIN \"{$archiveTable}\"\n\t\t\t\tON \"{$archiveTable}\".\"ID\" = \"{$baseTable}_versions\".\"RecordID\" \n\t\t\t\tAND \"{$archiveTable}\".\"Version\" = \"{$baseTable}_versions\".\"Version\""; 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->from 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; // Return all version instances // Return all version instances case 'all_versions': case 'latest_versions': foreach ($query->from 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 (self::$db_for_versions_table as $name => $type) { $query->selectMore(sprintf('"%s_versions"."%s"', $baseTable, $name)); } $query->selectMore(sprintf('"%s_versions"."%s" AS "ID"', $baseTable, 'RecordID')); // 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') { $archiveTable = self::requireArchiveTempTable($baseTable); $query->innerJoin($archiveTable, "\"{$archiveTable}\".\"ID\" = \"{$baseTable}_versions\".\"RecordID\" AND \"{$archiveTable}\".\"Version\" = \"{$baseTable}_versions\".\"Version\""); } break; default: throw new InvalidArgumentException("Bad value for query parameter Versioned.mode: " . $dataQuery->getQueryParam('Versioned.mode')); } }
/** * 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')); } }
/** * 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); } } }
public function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null) { // Skip ID based filters if ($query->filtersOnID()) { return; } // Skip filter in the CMS $isFrontend = $dataQuery->getQueryParam('Fluent.IsFrontend'); if ($isFrontend === null) { $isFrontend = Fluent::is_frontend(); } if (!$isFrontend) { return; } // Add filter for locale $locale = $dataQuery->getQueryParam('Fluent.Locale') ?: Fluent::current_locale(); $query->addWhere("\"{$this->ownerBaseClass}\".\"LocaleFilter_{$locale}\" = 1"); }
/** * 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". */ function augmentSQL(SQLQuery &$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 && !preg_match('/("|\'|`)Locale("|\'|`)/', implode(' ', $query->getWhere()))) { $qry = sprintf('"%s"."Locale" = \'%s\'', $baseTable, Convert::raw2sql($locale)); $query->addWhere($qry); } }