PHP SQLQuery::setSelect方法代码示例

 /**
  * Select the given fields from the given table.
  * 
  * @param String $table Unquoted table name (will be escaped automatically)
  * @param Array $fields Database column names (will be escaped automatically)
  */
 public function selectFromTable($table, $fields)
 {
     $table = Convert::raw2sql($table);
     $fieldExpressions = array_map(create_function('$item', "return '\"{$table}\".\"' . Convert::raw2sql(\$item) . '\"';"), $fields);
     $this->query->setSelect($fieldExpressions);
     return $this;
 }

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

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

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

 /**
  * Select the given fields from the given table.
  *
  * @param String $table Unquoted table name (will be escaped automatically)
  * @param Array $fields Database column names (will be escaped automatically)
  */
 public function selectFromTable($table, $fields)
 {
     $fieldExpressions = array_map(function ($item) use($table) {
         return "\"{$table}\".\"{$item}\"";
     }, $fields);
     $this->query->setSelect($fieldExpressions);
     return $this;
 }

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

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

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

 /**
  * @return Array Map of Snippet Folder IDs
  */
 public function snippetFoldersIncluded()
 {
     $ids = array();
     $q = new SQLQuery();
     $q->setSelect(array('"Snippet"."ID"', '"Snippet"."FolderID"'))->setFrom('"Snippet"');
     if (!empty($this->creator)) {
         $q->setWhereAny(array("\"CreatorID\"=" . intval($this->creator)));
     }
     foreach ($q->execute() as $row) {
         $ids[] = array('ID' => $row['FolderID']);
     }
     return $ids;
 }

 /**
  * @return Array Map of Snippet Folder IDs
  */
 public function snippetFoldersIncluded()
 {
     $ids = array();
     $q = new SQLQuery();
     $q->setSelect(array('"Snippet"."ID"', '"Snippet"."FolderID"'))->setFrom('"Snippet"');
     if (!empty($this->tag)) {
         $SQL_val = Convert::raw2sql($this->tag);
         $q->setWhereAny(array("\"Tags\" LIKE '{$SQL_val},%' OR \"Tags\" LIKE '%,{$SQL_val}' OR \"Tags\" LIKE '%,{$SQL_val},%' OR \"Tags\" LIKE '{$SQL_val}'"));
     }
     foreach ($q->execute() as $row) {
         $ids[] = array('ID' => $row['FolderID']);
     }
     return $ids;
 }

 public function Articles()
 {
     $filterID = $this->ExcludeTag()->ID;
     $sqlQuery = new SQLQuery();
     $sqlQuery->setFrom('HailTag_Articles');
     $sqlQuery->setSelect('HailTag_Articles.HailArticleID');
     //$sqlQuery->addLeftJoin('HailTag_Articles','HailTag_Articles.HailArticleID = HailArticleID');
     //$sqlQuery->addWhere('HailTag_Articles.HailTagID = ' . intval($this->Tag()->ID));
     $sqlQuery->addWhere('HailTagID = ' . intval($filterID));
     $map = $sqlQuery->execute()->map();
     $articles_ids = array_keys($map);
     $List = parent::Articles()->exclude('ID', $articles_ids);
     return $List;
 }

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

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

 /**
  * Test passing in a LIMIT with OFFSET clause string.
  */
 public function testLimitSetFromClauseString()
 {
     $query = new SQLQuery();
     $query->setSelect('*');
     $query->setFrom('"SQLQueryTest_DO"');
     $query->setLimit('20 OFFSET 10');
     $limit = $query->getLimit();
     $this->assertEquals(20, $limit['limit']);
     $this->assertEquals(10, $limit['start']);
 }

 /**
  * Return the number of rows in this query if the limit were removed.  Useful in paged data sets. 
  * @return int 
  */
 public function unlimitedRowCount($column = null)
 {
     // we can't clear the select if we're relying on its output by a HAVING clause
     if (count($this->having)) {
         $records = $this->execute();
         return $records->numRecords();
     }
     $clone = clone $this;
     $clone->limit = null;
     $clone->orderby = null;
     // Choose a default column
     if ($column == null) {
         if ($this->groupby) {
             $countQuery = new SQLQuery();
             $countQuery->setSelect("count(*)");
             $countQuery->setFrom('(' . $clone->sql() . ') all_distinct');
             return $countQuery->execute()->value();
         } else {
             $clone->setSelect(array("count(*)"));
         }
     } else {
         $clone->setSelect(array("count({$column})"));
     }
     $clone->setGroupBy(array());
     return $clone->execute()->value();
 }