PHP SQLQuery::setWhere方法代码示例

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

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

 public function DDLValues()
 {
     $selected_values = array();
     $owner = $_REQUEST["SurveyQuestionTemplateID"];
     if (isset($owner)) {
         $sqlQuery = new SQLQuery();
         $sqlQuery->setSelect("ValueID");
         $sqlQuery->setFrom("SurveyQuestionTemplate_DependsOn");
         $sqlQuery->setWhere("SurveyQuestionTemplateID = {$owner} AND ChildID = {$this->ID}");
         $selected_values = $sqlQuery->execute()->keyedColumn();
     }
     return new MultiDropdownField("Values_{$this->ID}", "Values_{$this->ID}", $this->Rows()->map("ID", "Value"), $selected_values);
 }

 protected function getRemoteObjectsQuery()
 {
     // Do something really lazy here; Join on all tables to do the mapping really sneakily
     $query = new SQLQuery('"' . $this->tableName . '"."ID"');
     $query->setFrom('"' . $this->tableName . '"');
     // relations are add-only, so just get unimported relations
     $query->setWhere('"' . $this->tableName . '"."_ImportedID" = 0');
     foreach ($this->fields as $field => $class) {
         // Join table
         $query->addInnerJoin($class, "\"{$class}\".\"ID\" = \"{$this->tableName}\".\"{$field}\"");
         // Remove unmapped related tables
         $query->addWhere("\"{$class}\".\"_ImportedID\" > 0");
         // Substitute imported ID from related class for that ID
         $query->selectField("\"{$class}\".\"_ImportedID\"", $field);
     }
     return $query;
 }

 /**
  * Remove a filter from the query
  *
  * @param string|array $fieldExpression The predicate of the condition to remove
  * (ignoring parameters). The expression will be considered a match if it's
  * contained within any other predicate.
  * @return DataQuery Self reference
  */
 public function removeFilterOn($fieldExpression)
 {
     $matched = false;
     // If given a parameterised condition extract only the condition
     if (is_array($fieldExpression)) {
         reset($fieldExpression);
         $fieldExpression = key($fieldExpression);
     }
     $where = $this->query->toAppropriateExpression()->getWhere();
     // Iterate through each condition
     foreach ($where as $i => $condition) {
         // Rewrite condition groups as plain conditions before comparison
         if ($condition instanceof SQLConditionGroup) {
             $predicate = $condition->conditionSQL($parameters);
             $condition = array($predicate => $parameters);
         }
         // As each condition is a single length array, do a single
         // iteration to extract the predicate and parameters
         foreach ($condition as $predicate => $parameters) {
             // @see SQLQuery::addWhere for why this is required here
             if (strpos($predicate, $fieldExpression) !== false) {
                 unset($where[$i]);
                 $matched = true;
             }
             // Enforce single-item condition predicate => parameters structure
             break;
         }
     }
     // 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;
 }

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

 public function getDDLLogoSize()
 {
     $size = null;
     $pageId = Convert::raw2sql($_REQUEST["PageId"]);
     if (isset($pageId)) {
         $sqlQuery = new SQLQuery();
         $sqlQuery->setSelect("LogoSize");
         $sqlQuery->setFrom("SummitSponsorPage_Companies");
         $sqlQuery->setWhere("CompanyID={$this->ID} AND SummitSponsorPageID={$pageId}");
         $size = $sqlQuery->execute()->value();
         if (is_null($size)) {
             $size = 'None';
         }
     }
     $sizes = array('Small' => 'Small', 'Medium' => 'Medium', 'Large' => 'Large', 'Big' => 'Big', 'None' => '--NONE--');
     return new DropdownField("LogoSize_{$this->ID}", "LogoSize_{$this->ID}", $sizes, $size);
 }

 public function testSelectLast()
 {
     // Test last in sequence
     $query = new SQLQuery();
     $query->setFrom('"SQLQueryTest_DO"');
     $query->setOrderBy('"Name"');
     $result = $query->lastRow()->execute();
     $records = array();
     foreach ($result as $row) {
         $records[] = $row;
     }
     $this->assertCount(1, $records);
     $this->assertEquals('Object 2', $records[0]['Name']);
     // Test last from empty sequence
     $query = new SQLQuery();
     $query->setFrom('"SQLQueryTest_DO"');
     $query->setOrderBy('"Name"');
     $query->setWhere(array("\"Name\" = 'Nonexistent Object'"));
     $result = $query->lastRow()->execute();
     $records = array();
     foreach ($result as $row) {
         $records[] = $row;
     }
     $this->assertCount(0, $records);
     // Test that given the first item, the 'last' in this list matches the first
     $query = new SQLQuery();
     $query->setFrom('"SQLQueryTest_DO"');
     $query->setOrderBy('"Name"');
     $query->setLimit(1);
     $result = $query->lastRow()->execute();
     $records = array();
     foreach ($result as $row) {
         $records[] = $row;
     }
     $this->assertCount(1, $records);
     $this->assertEquals('Object 1', $records[0]['Name']);
 }

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

 public function getDDLAdminSecurityGroup()
 {
     $groups = array();
     $companyId = $_REQUEST["CompanyId"];
     if (isset($companyId)) {
         $sqlQuery = new SQLQuery();
         $sqlQuery->setSelect("GroupID");
         $sqlQuery->setFrom("Company_Administrators");
         $sqlQuery->setWhere("MemberID={$this->owner->ID} AND CompanyID={$companyId}");
         $groups = $sqlQuery->execute()->keyedColumn();
     }
     $sql_query_groups = new SQLQuery();
     $permissions = "'" . implode("', '", $this->getAdminPermissionSet()) . "'";
     $sql_query_groups->setSelect(array('G.ID', 'G.Title'));
     $sql_query_groups->setFrom("`Group` G INNER JOIN  ( SELECT DISTINCT(GroupID) FROM `Permission` WHERE `Code` IN ({$permissions})) PG ON PG.GroupID=G.ID");
     $company_security_groups = $sql_query_groups->execute()->map();
     return new MultiDropdownField("AdminSecurityGroup_{$this->owner->ID}", "AdminSecurityGroup_{$this->owner->ID}", $company_security_groups, $groups);
 }

 /**
  * Remove the given item from this list.
  * Note that for a ManyManyList, the item is never actually deleted, only the join table is affected
  * @param $itemID The item it
  */
 function removeByID($itemID)
 {
     if (!is_numeric($itemID)) {
         throw new InvalidArgumentException("ManyManyList::removeById() expecting an ID");
     }
     $query = new SQLQuery("*", array("\"{$this->joinTable}\""));
     $query->setDelete(true);
     if ($filter = $this->foreignIDFilter()) {
         $query->setWhere($filter);
     } else {
         user_error("Can't call ManyManyList::remove() until a foreign ID is set", E_USER_WARNING);
     }
     $query->addWhere("\"{$this->localKey}\" = {$itemID}");
     $query->execute();
 }

    /**
     * Test deprecation of SQLQuery::setDelete/getDelete
     */
    public function testDeprecatedSetDelete()
    {
        // Temporarily disable deprecation
        Deprecation::notification_version(null);
        $query = new SQLQuery();
        $query->setSelect(array('"SQLQueryTest_DO"."Name"'));
        $query->setFrom('"SQLQueryTest_DO"');
        $query->setWhere(array('"SQLQueryTest_DO"."Name"' => 'Andrew'));
        // Check SQL for select
        $this->assertSQLEquals(<<<EOS
SELECT "SQLQueryTest_DO"."Name" FROM "SQLQueryTest_DO"
WHERE ("SQLQueryTest_DO"."Name" = ?)
EOS
, $query->sql($parameters));
        $this->assertEquals(array('Andrew'), $parameters);
        // Check setDelete works
        $query->setDelete(true);
        $this->assertSQLEquals(<<<EOS
DELETE FROM "SQLQueryTest_DO"
WHERE ("SQLQueryTest_DO"."Name" = ?)
EOS
, $query->sql($parameters));
        $this->assertEquals(array('Andrew'), $parameters);
        // Check that setDelete back to false restores the state
        $query->setDelete(false);
        $this->assertSQLEquals(<<<EOS
SELECT "SQLQueryTest_DO"."Name" FROM "SQLQueryTest_DO"
WHERE ("SQLQueryTest_DO"."Name" = ?)
EOS
, $query->sql($parameters));
        $this->assertEquals(array('Andrew'), $parameters);
    }

 function exportUsersPerRegion()
 {
     $params = $this->owner->getRequest()->getVars();
     if (!isset($params['countries']) || empty($params['countries'])) {
         return $this->owner->httpError('412', 'missing required param countries');
     }
     if (!isset($params['members']) || empty($params['members'])) {
         return $this->owner->httpError('412', 'missing required param members');
     }
     $countries = $params['countries'];
     $members = $params['members'];
     $join_members = '';
     $join_countries = '';
     if (!count($countries)) {
         return $this->owner->httpError('412', 'missing required param countries');
     } else {
         foreach ($countries as $country) {
             $join_countries .= "'" . $country . "',";
         }
     }
     $join_countries = rtrim($join_countries, ",");
     if (!count($members)) {
         return $this->owner->httpError('412', 'missing required param members');
     } else {
         foreach ($members as $member) {
             $join_members .= "'" . $member . "',";
         }
     }
     $join_members = rtrim($join_members, ",");
     $query = new SQLQuery();
     $select_fields = array('Member.FirstName', 'Member.Surname', 'Member.Email', 'Member.City', 'Member.State', 'Member.Country');
     $query->setFrom('Member');
     $query->setSelect($select_fields);
     $query->addInnerJoin('Group_Members', 'Group_Members.MemberID = Member.ID');
     $query->addInnerJoin('Group', "Group.ID = Group_Members.GroupID AND Group.Code IN (" . $join_members . ")");
     $query->setWhere("Member.Country IN (" . $join_countries . ")");
     $query->setOrderBy('SurName,FirstName');
     $result = $query->execute();
     $data = array();
     foreach ($result as $row) {
         $member = array('FirstName' => $row['FirstName'], 'Surname' => $row['Surname'], 'Email' => $row['Email'], 'City' => $row['City'], 'State' => $row['State'], 'Country' => CountryCodes::$iso_3166_countryCodes[$row['Country']]);
         array_push($data, $member);
     }
     $filename = "UsersPerCountry" . date('Ymd') . ".csv";
     return CSVExporter::getInstance()->export($filename, $data, ',');
 }

 /**
  * Retun an array of maps containing the keys, 'ID' and 'ParentID' for each page to be displayed
  * in the search.
  * 
  * @return Array
  */
 function pagesIncluded()
 {
     $ids = array();
     $q = new SQLQuery();
     $q->setSelect(array('"ID"', '"ParentID"'))->setFrom('"SiteTree"');
     $where = array();
     $SQL_params = Convert::raw2sql($this->params);
     foreach ($SQL_params as $name => $val) {
         switch ($name) {
             // Match against URLSegment, Title, MenuTitle & Content
             case 'Term':
                 if ($val) {
                     $where[] = "\"URLSegment\" LIKE '%{$val}%' OR \"Title\" LIKE '%{$val}%' OR \"MenuTitle\" LIKE '%{$val}%' OR \"Content\" LIKE '%{$val}%'";
                 }
                 break;
                 // Match against date
             // Match against date
             case 'LastEditedFrom':
                 if ($val) {
                     $where[] = "\"LastEdited\" >= '{$val}'";
                 }
                 break;
             case 'LastEditedTo':
                 if ($val) {
                     $where[] = "\"LastEdited\" <= '{$val}'";
                 }
                 break;
                 // Match against exact ClassName
             // Match against exact ClassName
             case 'ClassName':
                 if ($val && $val != 'All') {
                     $where[] = "\"ClassName\" = '{$val}'";
                 }
                 break;
             default:
                 // Partial string match against a variety of fields
                 if (!empty($val) && singleton("SiteTree")->hasDatabaseField($name)) {
                     $where[] = "\"{$name}\" LIKE '%{$val}%'";
                 }
         }
     }
     $q->setWhere(empty($where) ? '' : '(' . implode(') AND (', $where) . ')');
     foreach ($q->execute() as $row) {
         $ids[] = array('ID' => $row['ID'], 'ParentID' => $row['ParentID']);
     }
     return $ids;
 }

 /**
  * @return DataList
  */
 private function getAllowedDependants()
 {
     $steps_query = new SQLQuery();
     $steps_query->setSelect("ID");
     $steps_query->setFrom("SurveyStepTemplate");
     $high_order = $this->Step()->order();
     $current_survey_id = $this->Step()->SurveyTemplateID;
     $steps_query->setWhere("SurveyTemplateID = {$current_survey_id} AND `Order` <= {$high_order} ");
     $steps_query->setOrderBy('`Order`', 'ASC');
     $current_step_ids = $steps_query->execute()->keyedColumn();
     return SurveyQuestionTemplate::get()->filter(array('StepID' => $current_step_ids));
 }