PHP SQLQuery::selectField方法代码示例

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

 /**
  * Add in rating information
  * 
  * @param SQLQuery $query
  * @param DataQuery $dataQuery 
  */
 public function augmentSQL(SQLQuery &$query)
 {
     $base = ClassInfo::baseDataClass($this->owner);
     $simple = DB::getConn() instanceof SQLite3Database ? true : false;
     if ($simple) {
         $bound = '((Up / Down) / (Up + Down))';
     } else {
         $bound = '((Up + 1.9208) / (Up + Down) - ' . '1.96 * SQRT((Up * Down) / (Up + Down) + 0.9604) / ' . '(Up + Down)) / (1 + 3.8416 / (Up + Down))  / SQRT(HOUR(TIMEDIFF(NOW(), ' . $base . '.Created)) + 1)';
     }
     $query->selectField($bound, 'WilsonRating');
     $query->selectField('(Up + Down)', 'ActiveRating');
     $query->selectField('(Up - Down)', 'PositiveRating');
 }

 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 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 Tags()
 {
     $select = array('Title');
     $query = new SQLQuery($select, 'PostTag');
     $query->selectField('count(PostTag.ID)', 'Number');
     $query->selectField('PostTag.ID');
     $query->addInnerjoin('MicroPost_Tags', 'PostTag.ID = MicroPost_Tags.PostTagID');
     $date = date('Y-m-d H:i:s', strtotime('-1 month'));
     $query->addWhere("MicroPost_Tags.Tagged > '{$date}'");
     $query->addWhere('"PostTag"."Title" NOT LIKE \'SELF_TAG%\'');
     $query->addGroupBy('PostTag.ID');
     $query->setLimit(20);
     $rows = $query->execute();
     $tags = ArrayList::create();
     foreach ($rows as $row) {
         $data = new ArrayData($row);
         $data->Link = Controller::join_links(TimelineController::URL_SEGMENT, '?tags=' . urlencode($data->Title));
         $tags->push($data);
     }
     return $tags;
 }

 public function ArchiveDates()
 {
     $list = ArrayList::create();
     $stage = Versioned::current_stage();
     $query = new SQLQuery(array());
     $query->selectField("DATE_FORMAT(`Date`,'%Y_%M_%m')", "DateString")->setFrom("ArticlePage_{$stage}")->setOrderBy("Date", "ASC")->setDistinct(true);
     $result = $query->execute();
     if ($result) {
         while ($record = $result->nextRecord()) {
             list($year, $monthName, $monthNumber) = explode('_', $record['DateString']);
             $list->push(ArrayData::create(array('Year' => $year, 'MonthName' => $monthName, 'MonthNumber' => $monthNumber, 'Link' => $this->Link("date/{$year}/{$monthNumber}"), 'ArticleCount' => ArticlePage::get()->where("\n\t\t\t\t\t\t\tDATE_FORMAT(`Date`,'%Y%m') = '{$year}{$monthNumber}'\n\t\t\t\t\t\t\tAND ParentID = {$this->ID}\n\t\t\t\t\t\t")->count())));
         }
     }
     return $list;
 }

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

 public function testDbDatetimeDifference()
 {
     $offset = $this->checkPreconditions();
     $clause = $this->adapter->datetimeDifferenceClause('1974-10-14 10:30:00', '1973-10-14 10:30:00');
     $result = DB::query('SELECT ' . $clause)->value();
     $this->matchesRoughly($result / 86400, 365, '1974 - 1973 = 365 * 86400 sec', $offset);
     $clause = $this->adapter->datetimeDifferenceClause(date('Y-m-d H:i:s', strtotime('-15 seconds')), 'now');
     $result = DB::query('SELECT ' . $clause)->value();
     $this->matchesRoughly($result, -15, '15 seconds ago - now', $offset);
     $clause = $this->adapter->datetimeDifferenceClause('now', $this->adapter->datetimeIntervalClause('now', '+45 Minutes'));
     $result = DB::query('SELECT ' . $clause)->value();
     $this->matchesRoughly($result, -45 * 60, 'now - 45 minutes ahead', $offset);
     $query = new SQLQuery();
     $query->setSelect(array());
     $query->selectField($this->adapter->datetimeDifferenceClause('"LastEdited"', '"Created"'), 'test')->setFrom('"DbDateTimeTest_Team"')->setLimit(1);
     $result = $query->execute()->value();
     $lastedited = Dataobject::get_one('DbDateTimeTest_Team')->LastEdited;
     $created = Dataobject::get_one('DbDateTimeTest_Team')->Created;
     $this->matchesRoughly($result, strtotime($lastedited) - strtotime($created), 'age of HomePage record in seconds since unix epoc', $offset);
 }

 /**
  * Select the given field expressions.
  * 
  * @param $fieldExpression String The field to select (escaped SQL statement)
  * @param $alias String The alias of that field (escaped SQL statement)
  */
 protected function selectField($fieldExpression, $alias = null)
 {
     $this->query->selectField($fieldExpression, $alias);
 }

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

 /**
  * @param SQLQuery $query
  * @param DataQuery $dataQuery
  */
 public function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null)
 {
     $controller = Controller::curr();
     if (!is_subclass_of($controller, 'LeftAndMain')) {
         $includedTables = ContinentalContent::getAffectedTables();
         $strContinent = ContinentalContent::CurrentContinent();
         if ($strContinent != CONTINENTAL_DEFAULT) {
             foreach ($query->getSelect() as $alias => $select) {
                 if (!preg_match('/^"(?<class>\\w+)"\\."(?<field>\\w+)"$/i', $select, $matches)) {
                     continue;
                 }
                 $class = $matches['class'];
                 $field = $matches['field'];
                 if (!in_array($class, $includedTables)) {
                     continue;
                 }
                 $strNewField = $field . '_' . $strContinent;
                 $arrFields = ContinentalContent::make_continental_fields($class);
                 if (isset($arrFields['db']) && isset($arrFields['db'][$strNewField])) {
                     $expression = $this->localiseSelect($class, $strNewField, $field);
                     $query->selectField($expression, $alias);
                 }
             }
             // TODO: update where clues too
         }
     }
 }

 /**
  * @param string $location
  * @return string
  */
 function getContinentFromLocation($location)
 {
     $loc_array = explode(',', $location);
     $country_code = count($loc_array) ? trim(end($loc_array)) : '';
     if ($country_code) {
         $sqlQuery = new SQLQuery();
         $sqlQuery->setFrom("Continent");
         $sqlQuery->selectField("Name");
         $sqlQuery->addLeftJoin("Continent_Countries", "Continent_Countries.ContinentID = Continent.ID");
         $sqlQuery->addWhere("Continent_Countries.CountryCode = '{$country_code}'");
         $continent = $sqlQuery->execute()->first();
         if ($continent) {
             return $continent['Name'];
         }
     }
     return '';
 }

 public function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null)
 {
     // Get locale and translation zone to use
     $default = Fluent::default_locale();
     $locale = $dataQuery->getQueryParam('Fluent.Locale') ?: Fluent::current_locale();
     // Get all tables to translate fields for, and their respective field names
     $includedTables = $this->getTranslatedTables();
     // Iterate through each select clause, replacing each with the translated version
     foreach ($query->getSelect() as $alias => $select) {
         // Skip fields without table context
         if (!preg_match('/^"(?<class>\\w+)"\\."(?<field>\\w+)"$/i', $select, $matches)) {
             continue;
         }
         $class = $matches['class'];
         $field = $matches['field'];
         // If this table doesn't have translated fields then skip
         if (empty($includedTables[$class])) {
             continue;
         }
         // If this field shouldn't be translated, skip
         if (!in_array($field, $includedTables[$class])) {
             continue;
         }
         // Select visible field from translated fields (Title_fr_FR || Title => Title)
         $translatedField = Fluent::db_field_for_locale($field, $locale);
         $expression = $this->localiseSelect($class, $translatedField, $field);
         $query->selectField($expression, $alias);
         // At the same time, rewrite the selector for the default field to make sure that
         // (in the case it is blank, which happens if installing fluent for the first time)
         // that it also populated from the root field.
         $defaultField = Fluent::db_field_for_locale($field, $default);
         $defaultExpression = $this->localiseSelect($class, $defaultField, $field);
         $query->selectField($defaultExpression, $defaultField);
     }
     // Rewrite where conditions with parameterised query (3.2 +)
     $where = $query->toAppropriateExpression()->getWhere();
     foreach ($where as $index => $condition) {
         // Extract parameters from condition
         if ($condition instanceof SQLConditionGroup) {
             $parameters = array();
             $predicate = $condition->conditionSQL($parameters);
         } else {
             $parameters = array_values(reset($condition));
             $predicate = key($condition);
         }
         // determine the table/column this condition is against
         $filterColumn = $this->detectFilterColumn($predicate, $includedTables, $locale);
         if (empty($filterColumn)) {
             continue;
         }
         // Duplicate the condition with all localisable fields replaced
         $localisedPredicate = $this->localiseFilterCondition($predicate, $includedTables, $locale);
         if ($localisedPredicate === $predicate) {
             continue;
         }
         // Generate new condition that conditionally executes one of the two conditions
         // depending on field nullability.
         // If the filterColumn is null or empty, then it's considered untranslated, and
         // thus the query should continue running on the default column unimpeded.
         $castColumn = "COALESCE(CAST({$filterColumn} AS CHAR), '')";
         $newPredicate = "\n\t\t\t\t({$castColumn} != '' AND {$castColumn} != '0' AND ({$localisedPredicate}))\n\t\t\t\tOR (\n\t\t\t\t\t({$castColumn} = '' OR {$castColumn} = '0') AND ({$predicate})\n\t\t\t\t)";
         // Duplicate this condition with parameters duplicated
         $where[$index] = array($newPredicate => array_merge($parameters, $parameters));
     }
     $query->setWhere($where);
     // Augment search if applicable
     if ($adapter = Fluent::search_adapter()) {
         $adapter->augmentSearch($query, $dataQuery);
     }
 }

 /**
  * Retrieves Locations by lat, long, distance, and optionally a limit.
  */
 public function getLocationSQLResultsByLatLong($lat = 37, $long = -122, $distance = 25, $limit = null)
 {
     //$data = DB::query('SELECT "ID" FROM "Marker" LIMIT 0 , '.$limit.';')->value();
     //$query = 'SELECT "ID", ( 3959 * acos( cos( radians('.$lat.') ) * cos( radians( Latitude ) ) * cos( radians( Longitude ) - radians('.$long.') ) + sin( radians('.$lat.') ) * sin( radians( Latitude ) ) ) ) AS "Distance" FROM "Marker" HAVING "Distance" < '.$distance.' ORDER BY "Distance" LIMIT 0 , '.$limit.';';
     $markerClass = StoreFinder::$MarkerClass;
     $sqlQuery = new SQLQuery();
     $sqlQuery->setFrom($markerClass);
     $sqlQuery->selectField('*');
     $sqlQuery->selectField('( 3959 * acos( cos( radians(' . $lat . ') ) * cos( radians( Latitude ) ) * cos( radians( Longitude ) - radians(' . $long . ') ) + sin( radians(' . $lat . ') ) * sin( radians( Latitude ) ) ) )', 'Distance');
     $sqlQuery->setHaving("Distance < " . $distance);
     $sqlQuery->setOrderBy('Distance');
     $sqlQuery->setLimit($limit);
     if ($markerClass != 'Marker') {
         $sqlQuery->addLeftJoin("Marker", 'Marker.ID = ' . $markerClass . '.ID');
     }
     $this->extraSQL($sqlQuery);
     // Execute and return a Query object
     $result = $sqlQuery->execute();
     return $result;
 }