PHP SQLQuery::getWhere方法代码示例

 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()))");
         }
     }
 }

 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)
 {
     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\"");
         }
     }
 }

 /**
  * Remove a filter from the query
  */
 public function removeFilterOn($fieldExpression)
 {
     $matched = false;
     $where = $this->query->getWhere();
     foreach ($where as $i => $clause) {
         if (strpos($clause, $fieldExpression) !== false) {
             unset($where[$i]);
             $matched = true;
         }
     }
     // set the entire where clause back, but clear the original one first
     if ($matched) {
         $this->query->setWhere($where);
     } else {
         throw new InvalidArgumentException("Couldn't find {$fieldExpression} in the query filter.");
     }
     return $this;
 }

 /**
  * Determines if the current query is supposed
  * to be exempt from the automatic filtering out
  * of temporary records.
  *
  * @param SQLQuery $query
  * @return boolean
  */
 protected function wantsTemporary($query)
 {
     foreach ($query->getWhere() as $whereClause) {
         $from = array_values($query->getFrom());
         // SQLQuery will automatically add double quotes and single quotes to values, so check against that.
         if ($whereClause == "{$from[0]}.\"MultiFormIsTemporary\" = '1'") {
             return true;
         }
     }
     return false;
 }

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

 /**
  * Rewrites the WHERE fragment of a query
  *
  * @param string $class
  * @param array $translatedColumns Translated columns
  * @param SQLQUery $query
  * @param string $keywords SQL escaped keywords
  * @param string $keywordsHTML HTML escaped keywords
  */
 public function augmentFilter($class, $translatedColumns, SQLQuery $query, $keywords, $keywordsHTML, $booleanMode)
 {
     // Augment the search section
     $locale = Fluent::current_locale();
     $wherePattern = self::$where_replacements[$class];
     $translatedPattern = $wherePattern;
     $searchColumns = self::$search_columns[$class];
     foreach (array_intersect($searchColumns, $translatedColumns) as $column) {
         $replacement = Fluent::db_field_for_locale($column, $locale);
         $translatedPattern = preg_replace('/\\b' . preg_quote($column) . '\\b/', $replacement, $translatedPattern);
     }
     // If no fields were translated, then don't modify
     if ($translatedPattern === $wherePattern) {
         return;
     }
     // Inject keywords into patterns
     $search = array('/\\$keywords/i', '/\\$htmlEntityKeywords/i', '/\\$boolean/i');
     $replace = array($keywords, $keywordsHTML, $booleanMode ? 'IN BOOLEAN MODE' : '');
     $whereOriginal = preg_replace($search, $replace, $wherePattern);
     $whereTranslated = preg_replace($search, $replace, $translatedPattern);
     $where = $query->getWhere();
     $newWhere = array();
     foreach ($query->getWhere() as $where) {
         // Remove excessive whitespace which breaks string replacement
         $where = preg_replace('/\\s+/im', ' ', $where);
         $whereOriginal = preg_replace('/\\s+/im', ' ', $whereOriginal);
         $newWhere[] = str_replace($whereOriginal, "{$whereOriginal} + {$whereTranslated}", $where);
     }
     $query->setWhere($newWhere);
 }

    /**
     * Test deprecation of SQLQuery::getWhere working appropriately
     */
    public function testDeprecatedGetWhere()
    {
        // Temporarily disable deprecation
        Deprecation::notification_version(null);
        $query = new SQLQuery();
        $query->setSelect(array('"SQLQueryTest_DO"."Name"'));
        $query->setFrom('"SQLQueryTest_DO"');
        $query->addWhere(array('"SQLQueryTest_DO"."Date" > ?' => '2012-08-08 12:00'));
        $query->addWhere('"SQLQueryTest_DO"."Name" = \'Richard\'');
        $query->addWhere(array('"SQLQueryTest_DO"."Meta" IN (?, \'Who?\', ?)' => array('Left', 'Right')));
        $expectedSQL = <<<EOS
SELECT "SQLQueryTest_DO"."Name"
 FROM "SQLQueryTest_DO"
 WHERE ("SQLQueryTest_DO"."Date" > ?)
 AND ("SQLQueryTest_DO"."Name" = 'Richard')
 AND ("SQLQueryTest_DO"."Meta" IN (?, 'Who?', ?))
EOS;
        $expectedParameters = array('2012-08-08 12:00', 'Left', 'Right');
        // Check sql evaluation of this query maintains the parameters
        $sql = $query->sql($parameters);
        $this->assertSQLEquals($expectedSQL, $sql);
        $this->assertEquals($expectedParameters, $parameters);
        // Check that ->toAppropriateExpression()->setWhere doesn't modify the query
        $query->setWhere($query->toAppropriateExpression()->getWhere());
        $sql = $query->sql($parameters);
        $this->assertSQLEquals($expectedSQL, $sql);
        $this->assertEquals($expectedParameters, $parameters);
        // Check that getWhere are all flattened queries
        $expectedFlattened = array('"SQLQueryTest_DO"."Date" > \'2012-08-08 12:00\'', '"SQLQueryTest_DO"."Name" = \'Richard\'', '"SQLQueryTest_DO"."Meta" IN (\'Left\', \'Who?\', \'Right\')');
        $this->assertEquals($expectedFlattened, $query->getWhere());
    }

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

 /**
  * 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)
 {
     // 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
     $locale = $this->owner->ID && $this->owner->Locale ? $this->owner->Locale : Translatable::get_current_locale();
     $baseTable = ClassInfo::baseDataClass($this->owner->class);
     if ($locale && self::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);
     }
 }

 /**
  * Check if a given SQLQuery filters on the Locale field
  *
  * @param SQLQuery $query
  * @return boolean
  */
 protected function filtersOnLocale($query)
 {
     foreach ($query->getWhere() as $condition) {
         // Compat for 3.1/3.2 where syntax
         if (is_array($condition)) {
             // In >=3.2 each $condition is a single length array('condition' => array('params'))
             reset($condition);
             $condition = key($condition);
         }
         // >=3.2 allows conditions to be expressed as evaluatable objects
         if (interface_exists('SQLConditionGroup') && $condition instanceof SQLConditionGroup) {
             $condition = $condition->conditionSQL($params);
         }
         if (preg_match('/("|\'|`)Locale("|\'|`)/', $condition)) {
             return true;
         }
     }
 }

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