public function augmentSQL(SQLQuery &$query) { if (empty($query->getSelect()) || $query->getDelete() || in_array("COUNT(*)", $query->getSelect()) || in_array("count(*)", $query->getSelect())) { return; } if (!$query->getWhere() || !preg_match('/\\.(\'|"|`|)ID(\'|"|`|)( ?)=/', $query->getWhere()[0])) { $c = Controller::curr(); if (!startsWith($c->class, "CMS") && !startsWith($c->class, "Model") && $c->getAction() != "update") { $className = $this->owner->ClassName; $query->addWhere("{$className}.Published=1"); // $query->addWhere( "DATE({$className}.PublicationDate)<=DATE(NOW())" ); $query->addWhere("({$className}.ExpirationDate IS NULL) OR ({$className}.ExpirationDate IS NOT NULL AND DATE({$className}.ExpirationDate)>=DATE(NOW()))"); } } }
/** * @see DataExtension::augmentSQL() */ public function augmentSQL(SQLQuery &$query) { $select = $query->getSelect(); if (empty($select) || $query->getDelete() || in_array("COUNT(*)", $select) || in_array("count(*)", $select)) { return; } if (!isset(self::$sortTables[$this->owner->class])) { $classes = array_reverse(ClassInfo::dataClassesFor($this->owner->class)); $class = null; foreach ($classes as $cls) { if (DataObject::has_own_table($cls) && ($fields = DataObject::database_fields($cls)) && isset($fields['SortOrder'])) { $class = $cls; break; } } self::$sortTables[$this->owner->class] = $class; } else { $class = self::$sortTables[$this->owner->class]; } if ($class) { $query->addOrderBy("\"{$class}\".\"SortOrder\" " . self::$sort_dir); } else { $query->addOrderBy("\"SortOrder\" " . self::$sort_dir); } }
/** * @see DataExtension::augmentSQL() */ public function augmentSQL(SQLQuery &$query) { $select = $query->getSelect(); if (empty($select) || $query->getDelete() || in_array("COUNT(*)", $select) || in_array("count(*)", $select)) { return; } if (!isset(self::$sortTables[$this->owner->class])) { // look up the table that has the SortOrder field $class = ClassInfo::table_for_object_field($this->owner->class, 'SortOrder'); self::$sortTables[$this->owner->class] = $class; } else { $class = self::$sortTables[$this->owner->class]; } if ($class) { $query->addOrderBy("\"{$class}\".\"SortOrder\" " . self::$sort_dir); } else { $query->addOrderBy("\"SortOrder\" " . self::$sort_dir); } }
/** * Ensure that if a query has an order by clause, those columns are present in the select. * * @param SQLQuery $query * @return null */ protected function ensureSelectContainsOrderbyColumns($query, $originalSelect = array()) { $tableClasses = ClassInfo::dataClassesFor($this->dataClass); $baseClass = array_shift($tableClasses); if ($orderby = $query->getOrderBy()) { $newOrderby = array(); $i = 0; foreach ($orderby as $k => $dir) { $newOrderby[$k] = $dir; // don't touch functions in the ORDER BY or public function calls // selected as fields if (strpos($k, '(') !== false) { continue; } $col = str_replace('"', '', trim($k)); $parts = explode('.', $col); // Pull through SortColumn references from the originalSelect variables if (preg_match('/_SortColumn/', $col)) { if (isset($originalSelect[$col])) { $query->selectField($originalSelect[$col], $col); } continue; } if (count($parts) == 1) { $databaseFields = DataObject::database_fields($baseClass); // database_fields() doesn't return ID, so we need to // manually add it here $databaseFields['ID'] = true; if (isset($databaseFields[$parts[0]])) { $qualCol = "\"{$baseClass}\".\"{$parts[0]}\""; } else { $qualCol = "\"{$parts['0']}\""; } // remove original sort unset($newOrderby[$k]); // add new columns sort $newOrderby[$qualCol] = $dir; // To-do: Remove this if block once SQLQuery::$select has been refactored to store getSelect() // format internally; then this check can be part of selectField() $selects = $query->getSelect(); if (!isset($selects[$col]) && !in_array($qualCol, $selects)) { $query->selectField($qualCol); } } else { $qualCol = '"' . implode('"."', $parts) . '"'; if (!in_array($qualCol, $query->getSelect())) { unset($newOrderby[$k]); $newOrderby["\"_SortColumn{$i}\""] = $dir; $query->selectField($qualCol, "_SortColumn{$i}"); $i++; } } } $query->setOrderBy($newOrderby); } }
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); } }
/** * Update any requests to limit the results to the current site */ public function augmentSQL(SQLQuery &$query) { 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)"); } } // WORKAROUND for databases that complain about an ORDER BY when the column wasn't selected (e.g. SQL Server) $select = $query->getSelect(); if ($hasGroupSubsites && $subsiteID && isset($select[0]) && !$select[0] == 'COUNT(*)') { $query->orderby = "\"AccessAllSubsites\" DESC" . ($query->orderby ? ', ' : '') . $query->orderby; } } }
/** * Convert a SQLQuery object into a SQL statement * @param $query SQLQuery */ public function sqlQueryToString(SQLQuery $query) { if ($query->getDelete()) { $text = 'DELETE '; } else { $text = $this->sqlSelectToString($query->getSelect(), $query->getDistinct()); } if ($query->getFrom()) { $text .= $this->sqlFromToString($query->getFrom()); } if ($query->getWhere()) { $text .= $this->sqlWhereToString($query->getWhere(), $query->getConnective()); } // these clauses only make sense in SELECT queries, not DELETE if (!$query->getDelete()) { if ($query->getGroupBy()) { $text .= $this->sqlGroupByToString($query->getGroupBy()); } if ($query->getHaving()) { $text .= $this->sqlHavingToString($query->getHaving()); } if ($query->getOrderBy()) { $text .= $this->sqlOrderByToString($query->getOrderBy()); } if ($query->getLimit()) { $text .= $this->sqlLimitToString($query->getLimit()); } } return $text; }
/** * Update any requests to limit the results to the current site */ public function augmentSQL(SQLQuery &$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(); $where = $query->getWhere(); if (!isset($from['SiteTree_ImageTracking']) && !($where && preg_match('/\\.(\'|"|`|)ID(\'|"|`|)/', $where[0]))) { $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 (!$query->getDelete() && !$isCounting) { $query->addOrderBy("\"SubsiteID\""); } } }
/** * @param SQLQuery $query * @param DataQuery $dataQuery */ public function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null) { $controller = Controller::curr(); if (!is_subclass_of($controller, 'LeftAndMain')) { $includedTables = ContinentalContent::getAffectedTables(); $strContinent = ContinentalContent::CurrentContinent(); if ($strContinent != CONTINENTAL_DEFAULT) { foreach ($query->getSelect() as $alias => $select) { if (!preg_match('/^"(?<class>\\w+)"\\."(?<field>\\w+)"$/i', $select, $matches)) { continue; } $class = $matches['class']; $field = $matches['field']; if (!in_array($class, $includedTables)) { continue; } $strNewField = $field . '_' . $strContinent; $arrFields = ContinentalContent::make_continental_fields($class); if (isset($arrFields['db']) && isset($arrFields['db'][$strNewField])) { $expression = $this->localiseSelect($class, $strNewField, $field); $query->selectField($expression, $alias); } } // TODO: update where clues too } } }
/** * Convert a SQLQuery object into a SQL statement * Caution: Expects correctly quoted and escaped SQL fragments. * * @param $query SQLQuery */ public function sqlQueryToString(SQLQuery $query) { if ($query->getDelete()) { //Appended space at the end of string causing an issue but this might not be the best solution //@see sqlSelectToString() sqlFromToString $text = 'DELETE'; } else { if ($traverse = $query->getTraverse()) { //Build the traverse string here $text = $this->sqlTraverseToString($traverse); } else { $text = $this->sqlSelectToString($query->getSelect(), $query->getDistinct()); } } if ($query->getFrom()) { $text .= $this->sqlFromToString($query->getFrom()); } if ($query->getWhere()) { $text .= $this->sqlWhereToString($query->getWhere(), $query->getConnective()); } // these clauses only make sense in SELECT queries, not DELETE if (!$query->getDelete()) { if ($query->getGroupBy()) { $text .= $this->sqlGroupByToString($query->getGroupBy()); } if ($query->getHaving()) { $text .= $this->sqlHavingToString($query->getHaving()); } if ($query->getOrderBy()) { $text .= $this->sqlOrderByToString($query->getOrderBy()); } if ($query->getLimit()) { $text .= $this->sqlLimitToString($query->getLimit()); } } // SS_Log::log(new Exception(print_r($text, true)), SS_Log::NOTICE); return $text; }