PHP SQLQuery::select方法代码示例

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

 /**
  * Selects record based on WHERE Clause.
  *
  * @param array $where Parameters to use as WHERE clause condition
  * @param  array [$fields               = array('*')] fields to select from the query
  *
  * @return array of calling class data type
  */
 public static function selectWhere($where, $fields = array('*'))
 {
     //Initialize parameters
     static::initialize();
     //Run query
     $result = SQLQuery::select(static::$tableName, static::$classNS, static::$DBH, $fields, $where);
     //Return result
     return $result;
 }

function get_user($user_id) {
	$DB = new SQLQuery();
	$DB->chooseTable(DB_USERS_TABLE);
	$user = $DB->select($user_id);
	if (empty($user)) {
		return NULL;
	} else {
		return $user[0]["users"];
	}
}

 function testSelectWithPredicateFilters()
 {
     $query = new SQLQuery();
     $query->select(array("Name"))->from("SQLQueryTest_DO");
     $match = new ExactMatchFilter("Name", "Value");
     $match->setModel('SQLQueryTest_DO');
     $match->apply($query);
     $match = new PartialMatchFilter("Meta", "Value");
     $match->setModel('SQLQueryTest_DO');
     $match->apply($query);
     $this->assertEquals("SELECT Name FROM SQLQueryTest_DO WHERE (`SQLQueryTest_DO`.`Name` = 'Value') AND (`SQLQueryTest_DO`.`Meta` LIKE '%Value%')", $query->sql());
 }

 protected function purgeUnconfirmedRegistrations()
 {
     $query = new SQLQuery();
     $conn = DB::getConn();
     $query->select('"EventRegistration"."ID"');
     $query->from('"EventRegistration"');
     $query->innerJoin('CalendarDateTime', '"TimeID" = "DateTime"."ID"', 'DateTime');
     $query->innerJoin('CalendarEvent', '"DateTime"."EventID" = "Event"."ID"', 'Event');
     $query->innerJoin('RegisterableEvent', '"Event"."ID" = "Registerable"."ID"', 'Registerable');
     $query->where('"Registerable"."ConfirmTimeLimit" > 0');
     $query->where('"Status"', 'Unconfirmed');
     $created = $conn->formattedDatetimeClause('"EventRegistration"."Created"', '%U');
     $query->where(sprintf('%s < %s', $created . ' + "Registerable"."ConfirmTimeLimit"', time()));
     if ($ids = $query->execute()->column()) {
         $count = count($ids);
         DB::query(sprintf('UPDATE "EventRegistration" SET "Status" = \'Canceled\' WHERE "ID" IN (%s)', implode(', ', $ids)));
     } else {
         $count = 0;
     }
     echo "{$count} unconfirmed registrations were canceled.\n";
 }

 function testDbDatetimeDifference()
 {
     if ($this->supportDbDatetime) {
         $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');
         $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');
         $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');
         $query = new SQLQuery();
         $query->select($this->adapter->datetimeDifferenceClause('"LastEdited"', '"Created"') . ' AS "test"')->from('"DbDateTimeTest_Team"')->limit(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');
     }
 }

 /**
  * 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->select("count(*)");
             $countQuery->from = array('(' . $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();
 }

 /**
  * Get the latest members
  *
  * @param int $limit Number of members to return
  * @return DataObjectSet
  */
 function getLatestMembers($limit = 1)
 {
     $filter = '';
     if ($forumGroup = DataObject::get_one('Group', "\"Code\" = 'forum-members'")) {
         $filter = "\"GroupID\" = '" . $forumGroup->ID . "' ";
     }
     if ($adminGroup = DataObject::get_one('Group', "(\"Code\" = 'administrators' OR \"Code\" = 'Administrators')")) {
         $filter .= $filter ? "OR \"GroupID\" = '" . $adminGroup->ID . "'" : "\"GroupID\" = '" . $adminGroup->ID . "'";
     }
     // do a lookup on the specific Group_Members table for the latest member ID
     if ($filter) {
         $limit = (int) $limit;
         $query = new SQLQuery();
         $query->select('"MemberID"')->from('"Group_Members"')->where($filter)->orderby('"ID" DESC')->limit($limit);
         $latestMemberIDs = $query->execute()->column();
         if ($latestMemberIDs) {
             $members = new DataObjectSet();
             foreach ($latestMemberIDs as $key => $id) {
                 $members->push(DataObject::get_by_id('Member', $id));
             }
             return $members;
         }
     }
     return DataObject::get("Member", "", "ID DESC", "", $limit);
 }

 /**
  * Returns the number of tickets available for an event time.
  *
  * @param  RegisterableDateTime $time
  * @param  int $excludeId A registration ID to exclude from calculations.
  * @return array
  */
 public function getAvailableForDateTime(RegisterableDateTime $time, $excludeId = null)
 {
     if ($this->StartType == 'Date') {
         $start = strtotime($this->StartDate);
     } else {
         $start = $time->getStartTimestamp();
         $start = sfTime::subtract($start, $this->StartDays, sfTime::DAY);
         $start = sfTime::subtract($start, $this->StartHours, sfTime::HOUR);
         $start = sfTime::subtract($start, $this->StartMins, sfTime::MINUTE);
     }
     if ($start >= time()) {
         return array('available' => false, 'reason' => 'Tickets are not yet available.', 'available_at' => $start);
     }
     if ($this->EndType == 'Date') {
         $end = strtotime($this->EndDate);
     } else {
         $end = $time->getStartTimestamp();
         $end = sfTime::subtract($end, $this->EndDays, sfTime::DAY);
         $end = sfTime::subtract($end, $this->EndHours, sfTime::HOUR);
         $end = sfTime::subtract($end, $this->EndMins, sfTime::MINUTE);
     }
     if (time() >= $end) {
         return array('available' => false, 'reason' => 'Tickets are no longer available.');
     }
     if (!($quantity = $this->Available)) {
         return array('available' => true);
     }
     $booked = new SQLQuery();
     $booked->select('SUM("Quantity")');
     $booked->from('"EventRegistration_Tickets"');
     $booked->leftJoin('EventRegistration', '"EventRegistration"."ID" = "EventRegistrationID"');
     if ($excludeId) {
         $booked->where('"EventRegistration"."ID"', '<>', $excludeId);
     }
     $booked->where('"Status"', '<>', 'Canceled');
     $booked->where('"EventTicketID"', $this->ID);
     $booked->where('"EventRegistration"."TimeID"', $time->ID);
     $booked = $booked->execute()->value();
     if ($booked < $quantity) {
         return array('available' => $quantity - $booked);
     } else {
         return array('available' => false, 'reason' => 'All tickets have been booked.');
     }
 }

	/**
	 * Retun an array of maps containing the keys, 'ID' and 'ParentID' for each page to be displayed
	 * in the search.
	 * 
	 * @return Array
	 */
	function pagesIncluded() {
		$ids = array();
		$q = new SQLQuery();
		$q->select(array('"ID"','"ParentID"'))
			->from('"SiteTree"');
		$where = array();
		
		$SQL_params = Convert::raw2sql($this->params);
		foreach($SQL_params as $name => $val) {
			switch($name) {
				// Match against URLSegment, Title, MenuTitle & Content
				case 'Term':
					if($val) $where[] = "\"URLSegment\" LIKE '%$val%' OR \"Title\" LIKE '%$val%' OR \"MenuTitle\" LIKE '%$val%' OR \"Content\" LIKE '%$val%'";
					break;
				// Match against date
				case 'LastEditedFrom':
					if($val) $where[] = "\"LastEdited\" >= '$val'";
					break;
				case 'LastEditedTo':
					if($val) $where[] = "\"LastEdited\" <= '$val'";
					break;
				// Match against exact ClassName
				case 'ClassName':
					if($val && $val != 'All') {
						$where[] = "\"ClassName\" = '$val'";
					}
					break;
				default:
					// Partial string match against a variety of fields 
					if(!empty($val) && singleton("SiteTree")->hasDatabaseField($name)) {
						$where[] = "\"$name\" LIKE '%$val%'";
					}
			}
		}
		$q->where(empty($where) ? '' : '(' . implode(') AND (',$where) . ')');

		foreach($q->execute() as $row) {
			$ids[] = array('ID'=>$row['ID'],'ParentID'=>$row['ParentID']);
		}
		
		return $ids;
	}

 /**
  * Returns the overall number of places remaining at this event, TRUE if
  * there are unlimited places or FALSE if they are all taken.
  *
  * @param  int $excludeId A registration ID to exclude from calculations.
  * @return int|bool
  */
 public function getRemainingCapacity($excludeId = null)
 {
     if (!$this->Capacity) {
         return true;
     }
     $taken = new SQLQuery();
     $taken->select('SUM("Quantity")');
     $taken->from('EventRegistration_Tickets');
     $taken->leftJoin('EventRegistration', '"EventRegistration"."ID" = "EventRegistrationID"');
     if ($excludeId) {
         $taken->where('"EventRegistration"."ID"', '<>', $excludeId);
     }
     $taken->where('"Status"', '<>', 'Canceled');
     $taken->where('"EventRegistration"."TimeID"', $this->ID);
     $taken = $taken->execute()->value();
     return $this->Capacity >= $taken ? $this->Capacity - $taken : false;
 }