PHP SQLQuery::execute方法代码示例

 /**
  * @return array
  */
 protected function read()
 {
     $data = array();
     foreach ($this->query->execute() as $result) {
         if (isset($result[$this->documentColumn])) {
             $data[] = array('category' => $this->category, 'document' => $result[$this->documentColumn]);
         }
     }
     return $data;
 }

 /**
  * Generate the items in this map. This is used by
  * getItems() if the items have not been generated already.
  */
 protected function genItems()
 {
     if (!isset($this->items)) {
         $this->items = new DataObjectSet();
         $items = $this->query->execute();
         foreach ($items as $item) {
             $className = isset($item['RecordClassName']) ? $item['RecordClassName'] : $item['ClassName'];
             if (!$className) {
                 user_error('SQLMap query could not retrieve className', E_USER_ERROR);
             }
             $this->items->push(new $className($item));
         }
     }
 }

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

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

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

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

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

 /**
  * Get Class Name for Summary Fields 
  * 
  * @return String Tax class name
  */
 public function getTaxClassName()
 {
     if ($this->TaxClass) {
         $sqlQuery = new SQLQuery("Title");
         $sqlQuery->setFrom('TaxClasses')->addWhere('ID=' . $this->TaxClass . '');
         return $sqlQuery->execute()->value();
     }
 }

 public function OrganizationCount()
 {
     $payload = function () {
         $sqlQuery = new SQLQuery("COUNT(DISTINCT(ID))", "Company", "DisplayOnSite = TRUE");
         return $sqlQuery->execute()->value();
     };
     return EntityCounterHelper::getInstance()->EntityCount("Company", $payload);
 }

 /**
  * Assign a sort number when object is written
  * @see DataExtension::onBeforeWrite()
  */
 public function onBeforeWrite()
 {
     if (!$this->owner->ID && !$this->owner->SortOrder || !$this->owner->SortOrder) {
         $classes = ClassInfo::dataClassesFor($this->owner->ClassName);
         $sql = new SQLQuery('count(ID)', array_shift($classes));
         $val = $sql->execute()->value();
         $this->owner->SortOrder = is_numeric($val) ? $val + 1 : 1;
     }
 }

 /**
  * Assign a sort number when object is written
  * @see DataExtension::onBeforeWrite()
  */
 public function onBeforeWrite()
 {
     if (!$this->owner->exists() || !$this->owner->SortOrder) {
         $classes = ClassInfo::dataClassesFor($this->owner->ClassName);
         $sql = new SQLQuery('MAX("SortOrder")', '"' . array_shift($classes) . '"');
         $val = $sql->execute()->value();
         $this->owner->SortOrder = is_numeric($val) ? $val + 1 : 1;
     }
 }

 /**
  * @param Array - $googleMapAddressArray: an array of geographic data provided by google maps
  * @param Int - $maxRadius: maximum number of kilometers (as the bird flies) between search point defined in $googleMapAddressArray and city found.
  * @return Object | false : returns a BrowseCitiesPage or false if nothing was found
  **/
 public static function get_clostest_city_page($googleMapAddressArray, $maxRadius = 500)
 {
     $cityPage = null;
     $suburbPage = null;
     $bt = defined('DB::USE_ANSI_SQL') ? "\"" : "`";
     $existingDistance = $maxRadius + 1;
     $newDistance = $maxRadius + 1;
     $existingPage = null;
     $newPage = null;
     $radiusSelectionSQL = self::radiusDefinitionOtherTable($googleMapAddressArray[0], $googleMapAddressArray[1], "BrowseCitiesPage", "Latitude", "Longitude");
     $sqlQuery = new SQLQuery();
     $sqlQuery->select = array("{$bt}BrowseCitiesPage{$bt}.{$bt}ID{$bt}, " . $radiusSelectionSQL . " as distance");
     $sqlQuery->from[] = "{$bt}BrowseCitiesPage{$bt}";
     $sqlQuery->where[] = $radiusSelectionSQL . " < " . $maxRadius;
     $sqlQuery->orderby = " distance ";
     $sqlQuery->limit = "1";
     $result = $sqlQuery->execute();
     $page = null;
     foreach ($result as $row) {
         $existingDistance = $row["distance"];
         $existingPage = DataObject::get_by_id("BrowseCitiesPage", $row["ID"]);
     }
     $radiusSelectionSQL = self::radiusDefinitionOtherTable($googleMapAddressArray[0], $googleMapAddressArray[1], "cities", "Latitude", "Longitude");
     $sqlQuery = new SQLQuery();
     $sqlQuery->select = array("cities.CityID", $radiusSelectionSQL . " as distance");
     $sqlQuery->from[] = "{$bt}cities{$bt}";
     $sqlQuery->where[] = $radiusSelectionSQL . " < " . $maxRadius;
     $sqlQuery->orderby = " distance ";
     $sqlQuery->limit = "1";
     $result = $sqlQuery->execute();
     foreach ($result as $row) {
         $sameOne = false;
         if ($existingPage) {
             if ($row["CityID"] == $existingPage->HiddenDataID) {
                 $sameOne = true;
             }
         }
         if (!$sameOne) {
             $newPage = self::create_city_and_parents($row["CityID"]);
             $newDistance = $row["distance"];
         }
     }
     if ($newPage && $newDistance < $existingDistance && $newDistance < $maxRadius) {
         $cityPage = $newPage;
     } elseif ($existingPage && $existingDistance < $maxRadius) {
         $cityPage = $existingPage;
     }
     if ($cityPage) {
         if ($cityPage->allowBrowseChildren()) {
             $suburbPage = BrowseSuburbPage::create_suburb($googleMapAddressArray, $cityPage);
         }
     }
     if ($suburbPage) {
         return $suburbPage;
     }
     return $cityPage;
 }

 /**
  * Assign a sort number when object is written
  * @see DataExtension::onBeforeWrite()
  */
 public function onBeforeWrite()
 {
     if (!$this->owner->exists() || !$this->owner->SortOrder) {
         // get the table in the ancestry that has the SortOrder field
         $table = ClassInfo::table_for_object_field($this->owner->class, 'SortOrder');
         $sql = new SQLQuery('MAX("SortOrder")', $table);
         $val = $sql->execute()->value();
         $this->owner->SortOrder = is_numeric($val) ? $val + 1 : 1;
     }
 }

 public function Vendors()
 {
     $query = new SQLQuery();
     $result = new ArrayList();
     $query->setSelect('"AddonVendor"."Name"')->selectField('COUNT("Addon"."ID")' . 'Count')->setFrom('"AddonVendor"')->addLeftJoin('Addon', '"Addon"."VendorID" = "AddonVendor"."ID"')->setGroupBy('"AddonVendor"."ID"')->setOrderBy(array('"Count"' => 'DESC', '"Name"' => 'ASC'));
     foreach ($query->execute() as $row) {
         $link = Controller::join_links(Director::baseURL(), 'add-ons', $row['Name']);
         $result->push(new ArrayData($row + array('Link' => $link)));
     }
     return $result;
 }