PHP SQLQuery::getSelect方法代码示例

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