PHP SQLQuery::limit方法代码示例

 function testSelectWithLimitClause()
 {
     // numeric limit
     $query = new SQLQuery();
     $query->from[] = "MyTable";
     $query->limit("99");
     $this->assertEquals("SELECT * FROM MyTable LIMIT 99", $query->sql());
     // array limit
     $query = new SQLQuery();
     $query->from[] = "MyTable";
     $query->limit(array('limit' => 99));
     $this->assertEquals("SELECT * FROM MyTable LIMIT 99", $query->sql());
     // array limit with start (MySQL specific)
     $query = new SQLQuery();
     $query->from[] = "MyTable";
     $query->limit(array('limit' => 99, 'start' => 97));
     $this->assertEquals("SELECT * FROM MyTable LIMIT 97,99", $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 = "") {
		// 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;
	}

 function testSelectWithLimitClause()
 {
     // These are MySQL specific :-S
     if (DB::getConn() instanceof MySQLDatabase) {
         // numeric limit
         $query = new SQLQuery();
         $query->from[] = "MyTable";
         $query->limit("99");
         $this->assertEquals("SELECT * FROM MyTable LIMIT 99", $query->sql());
         // array limit
         $query = new SQLQuery();
         $query->from[] = "MyTable";
         $query->limit(array('limit' => 99));
         $this->assertEquals("SELECT * FROM MyTable LIMIT 99", $query->sql());
         // array limit with start (MySQL specific)
         $query = new SQLQuery();
         $query->from[] = "MyTable";
         $query->limit(array('limit' => 99, 'start' => 97));
         $this->assertEquals("SELECT * FROM MyTable LIMIT 99 OFFSET 97", $query->sql());
     }
 }

 /**
  * Test {@link DataObjectSet->parseQueryLimit()}
  */
 function testParseQueryLimit()
 {
     // Create empty objects, because they don't need to have contents
     $sql = new SQLQuery('*', '"Member"');
     $max = $sql->unlimitedRowCount();
     $set = new DataObjectSet();
     // Test handling an array
     $set->parseQueryLimit($sql->limit(array('limit' => 5, 'start' => 2)));
     $expected = array('pageStart' => 2, 'pageLength' => 5, 'totalSize' => $max);
     $this->assertEquals($expected, $set->getPageLimits(), 'The page limits match expected values.');
     // Test handling OFFSET string
     // uppercase
     $set->parseQueryLimit($sql->limit('3 OFFSET   1'));
     $expected = array('pageStart' => 1, 'pageLength' => 3, 'totalSize' => $max);
     $this->assertEquals($expected, $set->getPageLimits(), 'The page limits match expected values.');
     // and lowercase
     $set->parseQueryLimit($sql->limit('32   offset   3'));
     $expected = array('pageStart' => 3, 'pageLength' => 32, 'totalSize' => $max);
     $this->assertEquals($expected, $set->getPageLimits(), 'The page limits match expected values.');
     // Finally check MySQL LIMIT syntax
     $set->parseQueryLimit($sql->limit('7, 7'));
     $expected = array('pageStart' => 7, 'pageLength' => 7, 'totalSize' => $max);
     $this->assertEquals($expected, $set->getPageLimits(), 'The page limits match expected values.');
 }