PHP SQLQuery类代码示例

 public function __construct($controller, $name = "VariationForm")
 {
     parent::__construct($controller, $name);
     $product = $controller->data();
     $farray = array();
     $requiredfields = array();
     $attributes = $product->VariationAttributeTypes();
     foreach ($attributes as $attribute) {
         $attributeDropdown = $attribute->getDropDownField(_t('VariationForm.ChooseAttribute', "Choose {attribute} …", '', array('attribute' => $attribute->Label)), $product->possibleValuesForAttributeType($attribute));
         if ($attributeDropdown) {
             $farray[] = $attributeDropdown;
             $requiredfields[] = "ProductAttributes[{$attribute->ID}]";
         }
     }
     $fields = FieldList::create($farray);
     if (self::$include_json) {
         $vararray = array();
         $query = $query2 = new SQLQuery();
         $query->setSelect('ID')->setFrom('ProductVariation')->addWhere(array('ProductID' => $product->ID));
         if (!Product::config()->allow_zero_price) {
             $query->addWhere('"Price" > 0');
         }
         foreach ($query->execute()->column('ID') as $variationID) {
             $query2->setSelect('ProductAttributeValueID')->setFrom('ProductVariation_AttributeValues')->setWhere(array('ProductVariationID' => $variationID));
             $vararray[$variationID] = $query2->execute()->keyedColumn();
         }
         $fields->push(HiddenField::create('VariationOptions', 'VariationOptions', json_encode($vararray)));
     }
     $fields->merge($this->Fields());
     $this->setFields($fields);
     $requiredfields[] = 'Quantity';
     $this->setValidator(VariationFormValidator::create($requiredfields));
     $this->extend('updateVariationForm');
 }

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

 /**
  * Amend the query to select from a future date if necessary.
  */
 function augmentSQL(SQLQuery &$query)
 {
     if ($datetime = self::get_future_datetime()) {
         foreach ($query->from as $table => $dummy) {
             if (!isset($baseTable)) {
                 $baseTable = $table;
             }
             $query->renameTable($table, $table . '_versions');
             $query->replaceText("\"{$table}\".\"ID\"", "\"{$table}\".\"RecordID\"");
             $query->replaceText("\"{$table}_versions\".\"ID\"", "\"{$table}_versions\".\"RecordID\"");
             if ($table == $baseTable) {
                 // Add all <basetable>_versions columns
                 foreach (Versioned::$db_for_versions_table as $name => $type) {
                     $query->select[] = sprintf('"%s_versions"."%s"', $baseTable, $name);
                 }
                 $query->select[] = sprintf('"%s_versions"."%s" AS "ID"', $baseTable, 'RecordID');
             }
             if ($table != $baseTable) {
                 $query->from[$table] .= " AND \"{$table}_versions\".\"Version\" = \"{$baseTable}_versions\".\"Version\"";
             }
         }
         // Link to the version archived on that date
         $tempTable = $this->requireFutureStateTempTable($baseTable, $datetime);
         $query->from[$tempTable] = "INNER JOIN \"{$tempTable}\"\n\t\t\t\tON \"{$tempTable}\".\"ID\" = \"{$baseTable}_versions\".\"RecordID\" \n\t\t\t\tAND \"{$tempTable}\".\"Version\" = \"{$baseTable}_versions\".\"Version\"";
     }
 }

 public function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null)
 {
     // When filtering my menu, swap out condition for locale specific condition
     $locale = Fluent::current_locale();
     $field = Fluent::db_field_for_locale("ShowInMenus", $locale);
     $query->replaceText("\"{$this->ownerBaseClass}\".\"ShowInMenus\"", "\"{$this->ownerBaseClass}\".\"{$field}\"");
 }

 /**
  * getGateways
  * Create Gateways List - To do this we are going to fetch all
  * gateways in the Gateway DataObject and display them as
  * valid options where:
  * 
  *	a) The gateways' Enabled field is set to 1
  *	b) The gateways' checkCriteriaMet() method returns true.
  *
  * @param Object $order Order to use.
  * @param Boolean $admin If true, replace Gateway_ with Order_Payment_ for use in the CMS.
  * @return Array
  */
 public function getGateways($order, $admin = null)
 {
     if ($order) {
         //Variable to hold available gateways
         $gateways = array();
         //Fetch all Enabled Couriers
         $query = new SQLQuery();
         $query->setFrom('Gateway')->addWhere("(`Enabled`='1')");
         //Result of query
         $result = $query->execute();
         //Iterate over each courier...
         foreach ($result as $row) {
             //Class of the Gateway
             $ClassName = $row["ClassName"];
             //If the criteria is met for this gateway, add it to the gateways array.
             if ($ClassName::check_criteria_met($order->ID)) {
                 if ($admin) {
                     $ClassName = str_replace("Gateway_", "Order_Payment_", $ClassName);
                 }
                 //Set the ClassName as the key and the Friendly Name as the value.
                 $gateways[$ClassName] = $row["Title"];
             }
         }
         return $gateways;
     }
 }

 function augmentSQL(SQLQuery &$query)
 {
     // Get the content at a specific date
     if ($date = Versioned::$reading_archived_date) {
         foreach ($query->from as $table => $dummy) {
             if (!isset($baseTable)) {
                 $baseTable = $table;
             }
             $query->renameTable($table, $table . '_versions');
             $query->replaceText(".ID", ".RecordID");
             $query->select[] = "`{$baseTable}_versions`.RecordID AS ID";
             if ($table != $baseTable) {
                 $query->from[$table] .= " AND `{$table}_versions`.Version = `{$baseTable}_versions`.Version";
             }
         }
         // Link to the version archived on that date
         $this->requireArchiveTempTable($baseTable, $date);
         $query->from["_Archive{$baseTable}"] = "INNER JOIN `_Archive{$baseTable}`\n\t\t\t\tON `_Archive{$baseTable}`.RecordID = `{$baseTable}_versions`.RecordID \n\t\t\t\tAND `_Archive{$baseTable}`.Version = `{$baseTable}_versions`.Version";
         // Get a specific stage
     } else {
         if (Versioned::$reading_stage && Versioned::$reading_stage != $this->defaultStage && array_search(Versioned::$reading_stage, $this->stages) !== false) {
             foreach ($query->from as $table => $dummy) {
                 $query->renameTable($table, $table . '_' . Versioned::$reading_stage);
             }
         }
     }
 }

 public function run($request)
 {
     $confirm = $request->getVar('run') ? true : false;
     $force = $request->getVar('force') ? true : false;
     $since = $request->getVar('older');
     if (!$since) {
         echo "Please specify an 'older' param with a date older than which to prune (in strtotime friendly format)<br/>\n";
         return;
     }
     $since = strtotime($since);
     if (!$since) {
         echo "Please specify an 'older' param with a date older than which to prune (in strtotime friendly format)<br/>\n";
         return;
     }
     if ($since > strtotime('-3 months') && !$force) {
         echo "To cleanup data more recent than 3 months, please supply the 'force' parameter as well as the run parameter, swapping to dry run <br/>\n";
         $confirm = false;
     }
     $since = date('Y-m-d H:i:s', $since);
     $items = DataChangeRecord::get()->filter('Created:LessThan', $since);
     $max = $items->max('ID');
     echo "Pruning records older than {$since} (ID {$max})<br/>\n";
     if ($confirm && $max) {
         $query = new SQLQuery('*', 'DataChangeRecord', '"ID" < \'' . $max . '\'');
         $query->setDelete(true);
         $query->execute();
     } else {
         echo "Dry run performed, please supply the run=1 parameter to actually execute the deletion!<br/>\n";
     }
 }

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

 /**
  * 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 getPopularInteractions($interactionType, $itemClass, $days, $number = 10)
 {
     $since = date('Y-m-d H:i:s', strtotime("-{$days} days"));
     // Execute an SQL query so we can group by and count.
     $interactions = UserInteraction::get()->filter(array('Type' => $interactionType, 'ItemClass' => $itemClass, 'Created:GreaterThan' => $since));
     $interactionType = Convert::raw2sql($interactionType);
     $itemClass = Convert::raw2sql($itemClass);
     $subs = ClassInfo::subclassesFor($itemClass);
     $subs[] = $itemClass;
     if ($i = array_search('ErrorPage', $subs)) {
         unset($subs[$i]);
     }
     $in = "'" . implode("','", $subs) . "'";
     $query = new SQLQuery('*', 'UserInteraction', "Type = '{$interactionType}' AND ItemClass IN ({$in}) AND DATEDIFF(NOW(), Created) <= {$days}", 'Views DESC, Title ASC', 'Title', '', $number);
     $query->selectField('COUNT(Title)', 'Views');
     $results = $query->execute();
     $container = ArrayList::create();
     // The array list will need to be populated with objects so the template accepts it.
     for ($i = 0; $i < $results->numRecords(); $i++) {
         $object = UserInteraction::create($results->record());
         if ($object->canView()) {
             $container->add($object);
         }
     }
     return $container;
 }

	static function all_votes_count() {
		$query = new SQLQuery(
			"COUNT(Choice)",
			"Vote",
			"Choice BETWEEN 1 AND 5");
		return $query->execute()->value();
	}

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

	public function apply(SQLQuery $query) {
		return $query->where(sprintf(
			"%s != '%s'",
			$this->getDbName(),
			Convert::raw2sql($this->getValue())
		));
	}

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

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