PHP SQLQuery::addFrom方法代码示例

 function testSelectFromBasicTable()
 {
     $query = new SQLQuery();
     $query->setFrom('MyTable');
     $this->assertEquals("SELECT * FROM MyTable", $query->sql());
     $query->addFrom('MyJoin');
     $this->assertEquals("SELECT * FROM MyTable MyJoin", $query->sql());
 }

 /**
  * Add a join clause to this query
  * @deprecated 3.0 Use innerJoin() or leftJoin() instead.
  */
 public function join($join)
 {
     Deprecation::notice('3.0', 'Use innerJoin() or leftJoin() instead.');
     if ($join) {
         $this->query->addFrom($join);
         // TODO: This needs to be resolved for all databases
         if (DB::getConn() instanceof MySQLDatabase) {
             $from = $this->query->getFrom();
             $this->query->setGroupBy(reset($from) . ".\"ID\"");
         }
     }
     return $this;
 }

 function getAdminPermissionSet(array &$res)
 {
     $companyId = $_REQUEST["CompanyId"];
     if (isset($companyId) && is_numeric($companyId) && $companyId > 0) {
         // user could be ccla admin of only one company and company must have at least one team set
         $ccla_group = Group::get()->filter('Code', ICLAMember::CCLAGroupSlug)->first();
         if (!$ccla_group) {
             return;
         }
         $query_groups = new SQLQuery();
         $query_groups->addSelect("GroupID");
         $query_groups->addFrom("Company_Administrators");
         $query_groups->addWhere("MemberID = {$this->owner->ID} AND CompanyID <> {$companyId} AND GroupID =  {$ccla_group->ID} ");
         $groups = $query_groups->execute()->keyedColumn();
         $company = Company::get()->byID($companyId);
         if (count($groups) === 0 && $company->isICLASigned()) {
             array_push($res, 'CCLA_ADMIN');
         }
     }
 }

 /**
  * Returns a number of topseller products.
  * 
  * @return ArrayList
  * 
  * @author Sebastian Diel <sdiel@pixeltricks.de>,
  *         Sascha Koehler <skoehler@pixeltricks.de>
  * @since 03.02.2015
  */
 public function Elements()
 {
     if (!$this->numberOfProductsToShow) {
         $this->numberOfProductsToShow = SilvercartTopsellerProductsWidget::$defaults['numberOfProductsToShow'];
     }
     $cachekey = 'TopsellerProducts' . $this->numberOfProductsToShow;
     $cache = SS_Cache::factory($cachekey);
     $cachedResult = $cache->load($cachekey);
     if ($cachedResult) {
         $result = unserialize($result);
     } else {
         $products = array();
         $sqlQuery = new SQLQuery();
         $sqlQuery->selectField('SOP.SilvercartProductID');
         $sqlQuery->selectField('SUM(SOP.Quantity) AS OrderedQuantity');
         $sqlQuery->addFrom('SilvercartOrderPosition SOP');
         $sqlQuery->addLeftJoin('SilvercartProduct', 'SP.ID = SOP.SilvercartProductID', 'SP');
         $sqlQuery->addWhere('SP.isActive = 1');
         $sqlQuery->addGroupBy('SOP.SilvercartProductID');
         $sqlQuery->addOrderBy('OrderedQuantity', 'DESC');
         $sqlQuery->setLimit($this->numberOfProductsToShow);
         $sqlResult = $sqlQuery->execute();
         foreach ($sqlResult as $row) {
             $product = DataObject::get_by_id('SilvercartProduct', $row['SilvercartProductID']);
             $product->addCartFormIdentifier = $this->ID . '_' . $product->ID;
             $products[] = $product;
         }
         $result = new ArrayList($products);
     }
     return $result;
 }

 public function testSelectWithOrderbyClause()
 {
     $query = new SQLQuery();
     $query->setFrom("MyTable");
     $query->setOrderBy('MyName');
     $this->assertEquals('SELECT * FROM MyTable ORDER BY MyName ASC', $query->sql());
     $query = new SQLQuery();
     $query->setFrom("MyTable");
     $query->setOrderBy('MyName desc');
     $this->assertEquals('SELECT * FROM MyTable ORDER BY MyName DESC', $query->sql());
     $query = new SQLQuery();
     $query->setFrom("MyTable");
     $query->setOrderBy('MyName ASC, Color DESC');
     $this->assertEquals('SELECT * FROM MyTable ORDER BY MyName ASC, Color DESC', $query->sql());
     $query = new SQLQuery();
     $query->setFrom("MyTable");
     $query->setOrderBy('MyName ASC, Color');
     $this->assertEquals('SELECT * FROM MyTable ORDER BY MyName ASC, Color ASC', $query->sql());
     $query = new SQLQuery();
     $query->setFrom("MyTable");
     $query->setOrderBy(array('MyName' => 'desc'));
     $this->assertEquals('SELECT * FROM MyTable ORDER BY MyName DESC', $query->sql());
     $query = new SQLQuery();
     $query->setFrom("MyTable");
     $query->setOrderBy(array('MyName' => 'desc', 'Color'));
     $this->assertEquals('SELECT * FROM MyTable ORDER BY MyName DESC, Color ASC', $query->sql());
     $query = new SQLQuery();
     $query->setFrom("MyTable");
     $query->setOrderBy('implode("MyName","Color")');
     $this->assertEquals('SELECT *, implode("MyName","Color") AS "_SortColumn0" FROM MyTable ORDER BY "_SortColumn0" ASC', $query->sql());
     $query = new SQLQuery();
     $query->setFrom("MyTable");
     $query->setOrderBy('implode("MyName","Color") DESC');
     $this->assertEquals('SELECT *, implode("MyName","Color") AS "_SortColumn0" FROM MyTable ORDER BY "_SortColumn0" DESC', $query->sql());
     $query = new SQLQuery();
     $query->setFrom("MyTable");
     $query->setOrderBy('RAND()');
     $this->assertEquals('SELECT *, RAND() AS "_SortColumn0" FROM MyTable ORDER BY "_SortColumn0" ASC', $query->sql());
     $query = new SQLQuery();
     $query->setFrom("MyTable");
     $query->addFrom('INNER JOIN SecondTable USING (ID)');
     $query->addFrom('INNER JOIN ThirdTable USING (ID)');
     $query->setOrderBy('MyName');
     $this->assertEquals('SELECT * FROM MyTable ' . 'INNER JOIN SecondTable USING (ID) ' . 'INNER JOIN ThirdTable USING (ID) ' . 'ORDER BY MyName ASC', $query->sql());
 }

 /**
  * Returns all SilvercartProductIDs that have this group set as mirror
  * group.
  *
  * @return array
  */
 public function getMirroredProductIDs()
 {
     $mirroredProductIDs = array();
     $translations = $this->getTranslations();
     $translationProductGroupIDs = array($this->ID);
     if ($translations && $translations->count() > 0) {
         foreach ($translations as $translation) {
             $translationProductGroupIDs[] = $translation->ID;
         }
     }
     $translationProductGroupIDList = implode(',', $translationProductGroupIDs);
     $sqlQuery = new SQLQuery();
     $sqlQuery->setSelect('SP_SPGMP.SilvercartProductID');
     $sqlQuery->addFrom('SilvercartProduct_SilvercartProductGroupMirrorPages SP_SPGMP');
     $sqlQuery->addWhere(array(sprintf("SP_SPGMP.SilvercartProductGroupPageID IN (%s)", $translationProductGroupIDList)));
     $result = $sqlQuery->execute();
     foreach ($result as $row) {
         $mirroredProductIDs[] = $row['SilvercartProductID'];
     }
     return $mirroredProductIDs;
 }

 /**
  * Augment the the SQLQuery that is created by the DataQuery
  * @todo Should this all go into VersionedDataQuery?
  */
 function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null)
 {
     $baseTable = ClassInfo::baseDataClass($dataQuery->dataClass());
     switch ($dataQuery->getQueryParam('Versioned.mode')) {
         // Noop
         case '':
             break;
             // Reading a specific data from the archive
         // Reading a specific data from the archive
         case 'archive':
             $date = $dataQuery->getQueryParam('Versioned.date');
             foreach ($query->getFrom() as $table => $dummy) {
                 $query->renameTable($table, $table . '_versions');
                 $query->replaceText("\"{$table}\".\"ID\"", "\"{$table}\".\"RecordID\"");
                 // Add all <basetable>_versions columns
                 foreach (self::$db_for_versions_table as $name => $type) {
                     $query->selectField(sprintf('"%s_versions"."%s"', $baseTable, $name), $name);
                 }
                 $query->selectField(sprintf('"%s_versions"."%s"', $baseTable, 'RecordID'), "ID");
                 if ($table != $baseTable) {
                     $query->addFrom(array($table => " AND \"{$table}_versions\".\"Version\" = \"{$baseTable}_versions\".\"Version\""));
                 }
             }
             // Link to the version archived on that date
             $archiveTable = $this->requireArchiveTempTable($baseTable, $date);
             $query->addFrom(array($archiveTable => "INNER JOIN \"{$archiveTable}\"\n\t\t\t\tON \"{$archiveTable}\".\"ID\" = \"{$baseTable}_versions\".\"RecordID\" \n\t\t\t\tAND \"{$archiveTable}\".\"Version\" = \"{$baseTable}_versions\".\"Version\""));
             break;
             // Reading a specific stage (Stage or Live)
         // Reading a specific stage (Stage or Live)
         case 'stage':
             $stage = $dataQuery->getQueryParam('Versioned.stage');
             if ($stage && $stage != $this->defaultStage) {
                 foreach ($query->getFrom() as $table => $dummy) {
                     // Only rewrite table names that are actually part of the subclass tree
                     // This helps prevent rewriting of other tables that get joined in, in
                     // particular, many_many tables
                     if (class_exists($table) && ($table == $this->owner->class || is_subclass_of($table, $this->owner->class) || is_subclass_of($this->owner->class, $table))) {
                         $query->renameTable($table, $table . '_' . $stage);
                     }
                 }
             }
             break;
             // Return all version instances
         // Return all version instances
         case 'all_versions':
         case 'latest_versions':
             foreach ($query->getFrom() as $alias => $join) {
                 if ($alias != $baseTable) {
                     $query->setJoinFilter($alias, "\"{$alias}\".\"RecordID\" = \"{$baseTable}_versions\".\"RecordID\" AND \"{$alias}\".\"Version\" = \"{$baseTable}_versions\".\"Version\"");
                 }
                 $query->renameTable($alias, $alias . '_versions');
             }
             // Add all <basetable>_versions columns
             foreach (self::$db_for_versions_table as $name => $type) {
                 $query->selectField(sprintf('"%s_versions"."%s"', $baseTable, $name), $name);
             }
             $query->selectField(sprintf('"%s_versions"."%s"', $baseTable, 'RecordID'), "ID");
             // latest_version has one more step
             // Return latest version instances, regardless of whether they are on a particular stage
             // This provides "show all, including deleted" functonality
             if ($dataQuery->getQueryParam('Versioned.mode') == 'latest_versions') {
                 $archiveTable = self::requireArchiveTempTable($baseTable);
                 $query->addInnerJoin($archiveTable, "\"{$archiveTable}\".\"ID\" = \"{$baseTable}_versions\".\"RecordID\" AND \"{$archiveTable}\".\"Version\" = \"{$baseTable}_versions\".\"Version\"");
             }
             break;
         default:
             throw new InvalidArgumentException("Bad value for query parameter Versioned.mode: " . $dataQuery->getQueryParam('Versioned.mode'));
     }
 }