PHP SQLQuery::addWhere方法代码示例

 /**
  * Return the {@link SQLQuery} that provides your report data.
  */
 function sourceQuery($params)
 {
     $sqlQuery = new SQLQuery();
     $sqlQuery->setFrom('CalendarEvent');
     $sqlQuery->selectField('Date');
     $sqlQuery->selectField('CalendarEvent.Title', 'Event');
     $sqlQuery->selectField('StartTime');
     $sqlQuery->selectField('EndTime');
     $sqlQuery->addInnerJoin('CalendarEventDate', '"CalendarEventDate"."CalendarEventID" = "CalendarEvent"."ID"');
     if (isset($params['DateFrom'])) {
         $fromDate = new SS_DateTime('FromDate');
         $fromDate->setValue($params['DateFrom']);
         $sqlQuery->addWhere(array('Date >= ?' => $fromDate->Format("Y-m-d")));
     }
     if (isset($params['DateTo'])) {
         $toDate = new SS_DateTime('ToDate');
         $toDate->setValue($params['DateTo']);
         $sqlQuery->addWhere(array('Date <= ?' => $toDate->Format("Y-m-d")));
     }
     if (isset($params['PrivateBookings'])) {
         if ($params['PrivateBookings'] == 'Private') {
             $sqlQuery->addWhere('Private = 1');
         } elseif ($params['PrivateBookings'] == 'Public') {
             $sqlQuery->addWhere('Private = 0');
         }
     }
     $sqlQuery->addOrderBy('Date');
     $sqlQuery->addOrderBy('Event');
     $sqlQuery->addOrderBy('StartTime');
     $sqlQuery->addOrderBy('EndTime');
     return $sqlQuery;
 }

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

 private function getCSVColumns($flexi)
 {
     $columns = array('SubmittedBy' => 'Submitted By', 'IPAddress' => 'IP Address', 'Created' => 'Created');
     $sql = new SQLQuery();
     $sql->setFrom('FlexiFormSubmissionValue');
     $sql->setSelect('"FlexiFormSubmissionValue"."Name"');
     $sql->addLeftJoin('FlexiFormSubmission', '"FlexiFormSubmissionValue"."SubmissionID" = "FlexiFormSubmission"."ID"');
     $sql->addWhere('"FlexiFormSubmission"."FlexiFormID" = ' . $flexi->ID);
     $sql->addWhere('"FlexiFormSubmission"."FlexiFormClass" = \'' . $flexi->class . '\'');
     $sql->setDistinct(true);
     foreach ($sql->execute() as $row) {
         $columns['Values.' . $row['Name']] = $row['Name'];
     }
     return $columns;
 }

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

 /**
  * Update any requests to limit the results to the current site
  */
 public function augmentSQL(SQLQuery &$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 custommenu...
         if (strpos($tableName, 'CustomMenu') === false) {
             break;
         }
         $query->addWhere("\"{$tableName}\".\"SubsiteID\" IN ({$subsiteID})");
         break;
     }
 }

 public function augmentSQL(SQLQuery &$query)
 {
     if (static::enabled()) {
         $query->addWhere(self::VerificationDateName . ' is not null');
     }
     parent::augmentSQL($query);
 }

 public function ChartData()
 {
     $chartData = array();
     $list = ArrayList::create(array());
     $sqlQuery = new SQLQuery();
     $sqlQuery->setFrom('Addon');
     $sqlQuery->setSelect('Created');
     $sqlQuery->selectField('COUNT(*)', 'CountInOneDay');
     $sqlQuery->addWhere('"Created" >= DATE_SUB(NOW(), INTERVAL 30 DAY)');
     $sqlQuery->addGroupBy('DATE(Created)');
     $result = $sqlQuery->execute();
     if (count($result)) {
         foreach ($result as $row) {
             $date = date('j M Y', strtotime($row['Created']));
             if (!isset($chartData[$date])) {
                 $chartData[$date] = $row['CountInOneDay'];
             }
         }
     }
     if (count($chartData)) {
         foreach ($chartData as $x => $y) {
             $list->push(ArrayData::create(array('XValue' => $x, 'YValue' => $y)));
         }
     }
     return $list;
 }

 /**
  * Augment queries so that we don't fetch unpublished articles.
  **/
 public function augmentSQL(SQLQuery &$query)
 {
     $stage = Versioned::current_stage();
     if ($stage == 'Live' || !Permission::check("VIEW_DRAFT_CONTENT")) {
         $query->addWhere("PublishDate < '" . Convert::raw2sql(SS_Datetime::now()) . "'");
     }
 }

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

 /**
  * Append a WHERE clause to this query.
  * There are two different ways of doing this:
  *
  * <code>
  *  // the entire predicate as a single string
  *  $query->where("\"Column\" = 'Value'");
  *
  *  // multiple predicates as an array
  *  $query->where(array("\"Column\" = 'Value'", "\"Column\" != 'Value'"));
  * </code>
  *
  * @param string|array $where Predicate(s) to set, as escaped SQL statements.
  */
 public function where($filter)
 {
     if ($filter) {
         $this->query->addWhere($filter);
     }
     return $this;
 }

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

 /**
  * @param SQLQuery $query
  * @param DataQuery $dataQuery
  */
 function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null)
 {
     $baseTable = ClassInfo::baseDataClass($dataQuery->dataClass());
     if (class_exists('Subsite')) {
         $currentSubsiteID = Subsite::currentSubsiteID();
         $query->addWhere("\"{$baseTable}\".\"SubsiteID\" = '{$currentSubsiteID}'");
     }
 }

 /**
  * Manipulates the SQL query
  *
  * @param SQLQuery &$query Query to manipulate
  * 
  * @return void
  *
  * @author Sebastian Diel <sdiel@pixeltricks.de>
  * @since 04.05.2012
  */
 public function augmentSQL(SQLQuery &$query)
 {
     if (!$query->isJoinedTo($this->getLanguageClassName()) && !$query->getDelete()) {
         $silvercartDefaultLocale = SilvercartConfig::Locale();
         $query->addLeftJoin($this->getLanguageClassName(), sprintf("(\"%s\".\"ID\" = \"%s\".\"%s\")", $this->getBaseClassName(), $this->getLanguageClassName(), $this->getRelationFieldName()));
         $addToWhere = '';
         if ($this->getBaseLanguageClassName() != $this->getLanguageClassName()) {
             $query->addLeftJoin($this->getBaseLanguageClassName(), sprintf("(\"%s\".\"ID\" = \"%s\".\"ID\")", $this->getLanguageClassName(), $this->getBaseLanguageClassName()));
             $addToWhere = sprintf("AND \"%s\".\"ID\" = \"%s\".\"ID\"", $this->getBaseLanguageClassName(), $this->getLanguageClassName());
         }
         if (SilvercartConfig::useDefaultLanguageAsFallback() && Translatable::get_current_locale() != $silvercartDefaultLocale && !empty($silvercartDefaultLocale)) {
             $query->addWhere(sprintf("\"%s\".\"Locale\" = IFNULL((%s), (%s)) %s", $this->getBaseLanguageClassName(), $this->getLocaleDependantSelect(Translatable::get_current_locale()), $this->getLocaleDependantSelect($silvercartDefaultLocale), $addToWhere));
         } elseif (!empty($silvercartDefaultLocale)) {
             $query->addWhere(sprintf("\"%s\".\"Locale\" = '%s' %s", $this->getBaseLanguageClassName(), Translatable::get_current_locale(), $addToWhere));
         }
     }
 }

 public function testSelectWithWhereClauseFilter()
 {
     $query = new SQLQuery();
     $query->setSelect(array("Name", "Meta"));
     $query->setFrom("MyTable");
     $query->setWhere("Name = 'Name'");
     $query->addWhere("Meta = 'Test'");
     $this->assertEquals("SELECT Name, Meta FROM MyTable WHERE (Name = 'Name') AND (Meta = 'Test')", $query->sql());
 }

 /**
  * Augment queries so that we don't fetch unpublished articles.
  *
  * @param SQLQuery $query
  */
 public function augmentSQL(SQLQuery &$query)
 {
     $stage = Versioned::current_stage();
     if (Controller::curr() instanceof LeftAndMain) {
         return;
     }
     if ($stage == 'Live' || !Permission::check('VIEW_DRAFT_CONTENT')) {
         $query->addWhere(sprintf('"PublishDate" < \'%s\'', Convert::raw2sql(SS_Datetime::now())));
     }
 }