PHP SQLQuery::from方法代码示例

	/**
	 * Set up the simplest intial query
	 */
	function initialiseQuery() {
		// Get the tables to join to
		$tableClasses = ClassInfo::dataClassesFor($this->dataClass);
		
		// 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("DataObject::buildSQL: 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);
		$select = array("\"$baseClass\".*");

		// Build our intial query
		$this->query = new SQLQuery(array());
		$this->query->distinct = true;
		
		if($sort = singleton($this->dataClass)->stat('default_sort')) {
			$this->sort($sort);
		}

		$this->query->from("\"$baseClass\"");
		$this->selectAllFromTable($this->query, $baseClass);

		singleton($this->dataClass)->extend('augmentDataQueryCreation', $this->query, $this);
	}

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

	/**
	 * Build a {@link SQLQuery} object to perform the given query.
	 *
	 * @param string $filter A filter to be inserted into the WHERE clause.
	 * @param string|array $sort A sort expression to be inserted into the ORDER BY clause. If omitted, self::$default_sort will be used.
	 * @param string|array $limit A limit expression to be inserted into the LIMIT clause.
	 * @param string $join A single join clause. This can be used for filtering, only 1 instance of each DataObject will be returned.
	 * @param boolean $restictClasses Restrict results to only objects of either this class of a subclass of this class
	 * @param string $having A filter to be inserted into the HAVING clause.
	 *
	 * @return SQLQuery Query built.
	 */
	public function buildSQL($filter = "", $sort = "", $limit = "", $join = "", $restrictClasses = true, $having = "") {
		// Find a default sort
		if(!$sort) {
			$sort = $this->stat('default_sort');
		}

		// Get the tables to join to
		$tableClasses = ClassInfo::dataClassesFor($this->class);
		if(!$tableClasses) {
			if(!ManifestBuilder::has_been_included()) {
				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("DataObject::buildSQL: Can't find data classes (classes linked to tables) for $this->class. Please ensure you run dev/build after creating a new DataObject.", E_USER_ERROR);
			}
		}

		$baseClass = array_shift($tableClasses);
		$select = array("`$baseClass`.*");

		// Build our intial query
		$query = new SQLQuery($select);
		$query->from("`$baseClass`");
		$query->where($filter);
		$query->orderby($sort);
		$query->limit($limit);

		// Add SQL for multi-value fields on the base table
		$databaseFields = $this->databaseFields();
		if($databaseFields) foreach($databaseFields as $k => $v) {
			if(!in_array($k, array('ClassName', 'LastEdited', 'Created'))) {
				if(ClassInfo::classImplements($v, 'CompositeDBField')) {
					$this->dbObject($k)->addToQuery($query);
				}
			}
		}
		// Join all the tables
		if($tableClasses && self::$subclass_access) {
			foreach($tableClasses as $tableClass) {
				$query->from[$tableClass] = "LEFT JOIN `$tableClass` ON `$tableClass`.ID = `$baseClass`.ID";
				$query->select[] = "`$tableClass`.*";

				// Add SQL for multi-value fields
				$SNG = singleton($tableClass);
				$databaseFields = $SNG->databaseFields();
				if($databaseFields) foreach($databaseFields as $k => $v) {
					if(!in_array($k, array('ClassName', 'LastEdited', 'Created'))) {
						if(ClassInfo::classImplements($v, 'CompositeDBField')) {
							$SNG->dbObject($k)->addToQuery($query);
						}
					}
				}
			}
		}

		$query->select[] = "`$baseClass`.ID";
		$query->select[] = "if(`$baseClass`.ClassName,`$baseClass`.ClassName,'$baseClass') AS RecordClassName";

		// Get the ClassName values to filter to
		$classNames = ClassInfo::subclassesFor($this->class);

		if(!$classNames) {
			user_error("DataObject::get() Can't find data sub-classes for '$callerClass'");
		}

		// If querying the base class, don't bother filtering on class name
		if($restrictClasses && $this->class != $baseClass) {
			// Get the ClassName values to filter to
			$classNames = ClassInfo::subclassesFor($this->class);
			if(!$classNames) {
				user_error("DataObject::get() Can't find data sub-classes for '$callerClass'");
			}

			$query->where[] = "`$baseClass`.ClassName IN ('" . implode("','", $classNames) . "')";
		}

		if($having) {
			$query->having[] = $having;
		}

		if($join) {
			$query->from[] = $join;
			$query->groupby[] = reset($query->from) . ".ID";
		}

		return $query;
	}

 public function testInnerJoin()
 {
     $query = new SQLQuery();
     $query->from('MyTable');
     $query->innerJoin('MyOtherTable', 'MyOtherTable.ID = 2');
     $query->leftJoin('MyLastTable', 'MyOtherTable.ID = MyLastTable.ID');
     $this->assertEquals('SELECT * FROM MyTable ' . 'INNER JOIN "MyOtherTable" AS "MyOtherTable" ON MyOtherTable.ID = 2 ' . 'LEFT JOIN "MyLastTable" AS "MyLastTable" ON MyOtherTable.ID = MyLastTable.ID', $query->sql());
     $query = new SQLQuery();
     $query->from('MyTable');
     $query->innerJoin('MyOtherTable', 'MyOtherTable.ID = 2', 'table1');
     $query->leftJoin('MyLastTable', 'MyOtherTable.ID = MyLastTable.ID', 'table2');
     $this->assertEquals('SELECT * FROM MyTable ' . 'INNER JOIN "MyOtherTable" AS "table1" ON MyOtherTable.ID = 2 ' . 'LEFT JOIN "MyLastTable" AS "table2" ON MyOtherTable.ID = MyLastTable.ID', $query->sql());
 }

 /**
  * Build a {@link SQLQuery} object to perform the given query.
  *
  * @param string $filter A filter to be inserted into the WHERE clause.
  * @param string|array $sort A sort expression to be inserted into the ORDER BY clause. If omitted, self::$default_sort will be used.
  * @param string|array $limit A limit expression to be inserted into the LIMIT clause.
  * @param string $join A single join clause. This can be used for filtering, only 1 instance of each DataObject will be returned.
  * @param boolean $restictClasses Restrict results to only objects of either this class of a subclass of this class
  * @param string $having A filter to be inserted into the HAVING clause.
  *
  * @return SQLQuery Query built.
  */
 public function buildSQL($filter = "", $sort = "", $limit = "", $join = "", $restrictClasses = true, $having = "")
 {
     // Cache the big hairy part of buildSQL
     if (!isset(self::$cache_buildSQL_query[$this->class])) {
         // Get the tables to join to
         $tableClasses = ClassInfo::dataClassesFor($this->class);
         if (!$tableClasses) {
             if (!ManifestBuilder::has_been_included()) {
                 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("DataObject::buildSQL: Can't find data classes (classes linked to tables) for {$this->class}. Please ensure you run dev/build after creating a new DataObject.", E_USER_ERROR);
             }
         }
         $baseClass = array_shift($tableClasses);
         // $collidingFields will keep a list fields that appear in mulitple places in the class
         // heirarchy for this table.  They will be dealt with more explicitly in the SQL query
         // to ensure that junk data from other tables doesn't corrupt data objects
         $collidingFields = array();
         // Build our intial query
         $query = new SQLQuery(array());
         $query->from("\"{$baseClass}\"");
         // Add SQL for multi-value fields on the base table
         $databaseFields = self::database_fields($baseClass);
         if ($databaseFields) {
             foreach ($databaseFields as $k => $v) {
                 if (!in_array($k, array('ClassName', 'LastEdited', 'Created')) && ClassInfo::classImplements($v, 'CompositeDBField')) {
                     $this->dbObject($k)->addToQuery($query);
                 } else {
                     $query->select[$k] = "\"{$baseClass}\".\"{$k}\"";
                 }
             }
         }
         // Join all the tables
         if ($tableClasses && self::$subclass_access) {
             foreach ($tableClasses as $tableClass) {
                 $query->from[$tableClass] = "LEFT JOIN \"{$tableClass}\" ON \"{$tableClass}\".\"ID\" = \"{$baseClass}\".\"ID\"";
                 // Add SQL for multi-value fields
                 $databaseFields = self::database_fields($tableClass);
                 $compositeFields = self::composite_fields($tableClass, false);
                 if ($databaseFields) {
                     foreach ($databaseFields as $k => $v) {
                         if (!isset($compositeFields[$k])) {
                             // Update $collidingFields if necessary
                             if (isset($query->select[$k])) {
                                 if (!isset($collidingFields[$k])) {
                                     $collidingFields[$k] = array($query->select[$k]);
                                 }
                                 $collidingFields[$k][] = "\"{$tableClass}\".\"{$k}\"";
                             } else {
                                 $query->select[$k] = "\"{$tableClass}\".\"{$k}\"";
                             }
                         }
                     }
                 }
                 if ($compositeFields) {
                     foreach ($compositeFields as $k => $v) {
                         $dbO = $this->dbObject($k);
                         if ($dbO) {
                             $dbO->addToQuery($query);
                         }
                     }
                 }
             }
         }
         // Resolve colliding fields
         if ($collidingFields) {
             foreach ($collidingFields as $k => $collisions) {
                 $caseClauses = array();
                 foreach ($collisions as $collision) {
                     if (preg_match('/^"([^"]+)"/', $collision, $matches)) {
                         $collisionBase = $matches[1];
                         $collisionClasses = ClassInfo::subclassesFor($collisionBase);
                         $caseClauses[] = "WHEN \"{$baseClass}\".\"ClassName\" IN ('" . implode("', '", $collisionClasses) . "') THEN {$collision}";
                     } else {
                         user_error("Bad collision item '{$collision}'", E_USER_WARNING);
                     }
                 }
                 $query->select[$k] = "CASE " . implode(" ", $caseClauses) . " ELSE NULL END" . " AS \"{$k}\"";
             }
         }
         $query->select[] = "\"{$baseClass}\".\"ID\"";
         $query->select[] = "CASE WHEN \"{$baseClass}\".\"ClassName\" IS NOT NULL THEN \"{$baseClass}\".\"ClassName\" ELSE '{$baseClass}' END AS \"RecordClassName\"";
         // Get the ClassName values to filter to
         $classNames = ClassInfo::subclassesFor($this->class);
         if (!$classNames) {
             user_error("DataObject::get() Can't find data sub-classes for '{$callerClass}'");
         }
         // If querying the base class, don't bother filtering on class name
//.........这里部分代码省略.........

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

 /**
  * Delete the object. $version is either the version number, or "all" to completely delete the object and all versions. The two cases work
  * as follows:
  *  - if a specified version is being deleted, we remove it from the versions table for the object, and invoke the recalculateStages handler which
  *    will perform all reconcilation of the live and staged records for the object.
  *  - if all versions are being deleted, we remove all versions from the versions table, and again invoke recalculateStages.
  * FIX: this works on the assumption that the moderated class extends directly from DataObject.
  */
 public function deleteVersioned($version)
 {
     $baseTable = ClassInfo::baseDataClass($this->owner->class);
     $q = new SQLQuery();
     $q->from("{$baseTable}_versions");
     $q->where("RecordID = " . $this->owner->ID);
     if ($version != "all") {
         $q->where("Version=" . $version);
     }
     $q->delete = true;
     $q->execute();
     $this->recalculateStages();
 }

	public function testWhereAny() {
		$query = new SQLQuery();
		$query->from( 'MyTable' );

		$query->whereAny(array("Monkey = 'Chimp'", "Color = 'Brown'"));
		$this->assertEquals("SELECT * FROM MyTable WHERE (Monkey = 'Chimp' OR Color = 'Brown')",$query->sql());
	}

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