PHP SQLQuery::setFrom方法代码示例

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

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

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

 /**
  * Count the total number of items in this order and multiple
  * them by the item flat rate as defined in this couriers
  * settings.
  */
 public static function calculate_shipping_total($order_no)
 {
     //This method will be called statically and is not the Controller ($this) so store the database fields in $conf.
     $conf = DataObject::get_one(get_class());
     //Count the total number of items in this order.
     $product = new SQLQuery();
     $product->setFrom("Order_Items")->addWhere("(`OrderID`=" . $order_no . ")");
     $result = $product->execute();
     $total_items = 0;
     foreach ($result as $row) {
         $total_items = $total_items + $row["Quantity"];
     }
     //Return the shipping cost.
     return $total_items * $conf->FlatRate;
 }

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

 /**
  * If the total order spend exceeds the defined minimum spend value, make courier available. 
  */
 public static function check_criteria_met($order_no)
 {
     //This method will be called statically and is not the Controller ($this) so store the database fields in $conf.
     $conf = DataObject::get_one(get_class());
     //Fetch the total price for all products in the given order
     $product = new SQLQuery();
     $product->setFrom("Order_Items")->addWhere("(`OrderID`=" . $order_no . ")");
     $result = $product->execute();
     $total_spend = 0;
     foreach ($result as $row) {
         $total_spend = $total_spend + $row["Price"] * $row["Quantity"];
     }
     //If the total spend exceeds the defined minimum spend value, make courier available.
     return $total_spend >= $conf->MinSpend ? true : false;
 }

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

 private function checkBlogEntryPermissions()
 {
     $authorsId = array();
     $sqlQuery = new SQLQuery();
     $sqlQuery->setFrom('SiteTree_versions');
     $sqlQuery->selectField('AuthorID');
     $sqlQuery->addWhere('RecordID = ' . $this->ID);
     $sqlQuery->setOrderBy('ID DESC');
     $rawSQL = $sqlQuery->sql();
     $result = $sqlQuery->execute();
     foreach ($result as $row) {
         $authorsId[] = $row['AuthorID'];
     }
     $sqlQuery->setDelete(true);
     if (in_array(Member::currentUser()->ID, $authorsId) || $this->parent->OwnerID == Member::currentUser()->ID || Permission::check('ADMIN')) {
         return true;
     } else {
         return false;
     }
 }

 /**
  * COPIED FROM SITETREE
  *
  * Generate a URL segment based on the title provided.
  * 
  * @param string $title Product title
  * @return string Generated url segment
  */
 public function generateURLSegment($title)
 {
     $filter = URLSegmentFilter::create();
     $t = $filter->filter($title);
     // Fallback to generic page name if path is empty (= no valid, convertable characters)
     if (!$t || $t == '-' || $t == '-1') {
         $t = "page-{$this->ID}";
     }
     // Hook for extensions
     $this->extend('updateURLSegment', $t, $title);
     // Check to see if URLSegment exists already, if it does, append -* where * is COUNT()+1
     $seg = new SQLQuery('COUNT(*)');
     $seg->setFrom(get_class($this))->addWhere("`URLSegment` LIKE '%{$t}%'");
     $count = $seg->execute()->value();
     if ($count > 0) {
         $count++;
         return $t . "-" . $count;
     } else {
         return $t;
     }
 }

 /**
  * Set up the simplest initial query
  */
 public function initialiseQuery()
 {
     // Get the tables to join to.
     // Don't get any subclass tables - let lazy loading do that.
     $tableClasses = ClassInfo::ancestry($this->dataClass, true);
     // Error checking
     if (!$tableClasses) {
         if (!SS_ClassLoader::instance()->hasManifest()) {
             user_error("DataObjects have been requested before the manifest is loaded. Please ensure you are not" . " querying the database in _config.php.", E_USER_ERROR);
         } else {
             user_error("DataList::create Can't find data classes (classes linked to tables) for" . " {$this->dataClass}. Please ensure you run dev/build after creating a new DataObject.", E_USER_ERROR);
         }
     }
     $baseClass = array_shift($tableClasses);
     // Build our intial query
     $this->query = new SQLQuery(array());
     $this->query->setDistinct(true);
     if ($sort = singleton($this->dataClass)->stat('default_sort')) {
         $this->sort($sort);
     }
     $this->query->setFrom("\"{$baseClass}\"");
     $obj = Injector::inst()->get($baseClass);
     $obj->extend('augmentDataQueryCreation', $this->query, $this);
 }

 /**
  * calculatePaymentTotal
  * Calculate Payment Total 
  *
  * @return float
  */
 public function calculatePaymentTotal()
 {
     //Sum the total payments against this order.
     $query = new SQLQuery("SUM(Amount)");
     $query->setFrom("`Order_Payment`")->addWhere("`OrderId`='" . $this->ID . "' AND `Status`='Completed'");
     return StoreCurrency::convertToCurrency($query->execute()->value());
 }

 /**
  * Remove all items from this many-many join.  To remove a subset of items,
  * filter it first.
  *
  * @return void
  */
 public function removeAll()
 {
     $base = ClassInfo::baseDataClass($this->dataClass());
     // Remove the join to the join table to avoid MySQL row locking issues.
     $query = $this->dataQuery();
     $query->removeFilterOn($query->getQueryParam('Foreign.Filter'));
     $query = $query->query();
     $query->setSelect("\"{$base}\".\"ID\"");
     $from = $query->getFrom();
     unset($from[$this->joinTable]);
     $query->setFrom($from);
     $query->setDistinct(false);
     // ensure any default sorting is removed, ORDER BY can break DELETE clauses
     $query->setOrderBy(null, null);
     // Use a sub-query as SQLite does not support setting delete targets in
     // joined queries.
     $delete = new SQLQuery();
     $delete->setDelete(true);
     $delete->setFrom("\"{$this->joinTable}\"");
     $delete->addWhere($this->foreignIDFilter());
     $delete->addWhere("\"{$this->joinTable}\".\"{$this->localKey}\" IN ({$query->sql()})");
     $delete->execute();
 }