public function testQuerySetLimitToZero()
{
$q = new Doctrine_Query();
$q->from('User u');
$q->limit(20);
$q->limit(0);
$this->assertEqual($q->getSqlQuery(), 'SELECT e.id AS e__id, e.name AS e__name, e.loginname AS e__loginname, e.password AS e__password, e.type AS e__type, e.created AS e__created, e.updated AS e__updated, e.email_id AS e__email_id FROM entity e WHERE (e.type = 0)');
}
/**
* Get items
*
* @param int $offset
* @param int $itemsPerPage
* @return Doctrine_Collection
*/
public function getItems($offset, $itemsPerPage)
{
if ($itemsPerPage !== null) {
$this->_query->limit($itemsPerPage);
}
if ($offset !== null) {
$this->_query->offset($offset);
}
return $this->_query->execute();
}
/**
* Returns an collection of items for a page.
*
* @param integer $offset Page offset
* @param integer $itemCountPerPage Number of items per page
* @return array
*/
public function getItems($offset, $itemCountPerPage)
{
$data = $this->_query->limit($itemCountPerPage)->offset($offset)->execute();
if ($data instanceof Doctrine_Collection) {
return $data->getData();
} elseif (is_array($data)) {
return $data;
} else {
$message = sprintf('Unexpected datatype for getItems(): %s', Robo47_Core::getType($data));
throw new Robo47_Paginator_Adapter_Exception($message);
}
}
/**
* getSetting
* pulls the sfSetting object for a given setting
*
* @param string $setting
* @static
* @access public
* @return object sfSetting
*/
static function getSetting($setting)
{
if (!is_string($setting) || empty($setting)) {
return 0;
}
// If all the settings have been requested, there's no need to check for
// individuals. This avoids additional queries later on.
if (sfContext::getInstance()->getRequest()->hasAttribute('AllsfSettings')) {
$settings = sfContext::getInstance()->getRequest()->getAttribute('AllsfSettings');
} else {
$settings = sfContext::getInstance()->getRequest()->getAttribute('sfSettings');
}
if (isset($settings[$setting])) {
$obj = $settings[$setting];
return $obj;
} else {
// Setting was not pre-loaded via ->load() but we'll be nice and retrieve
// the setting anyhow:
$query = new Doctrine_Query();
$query->addSelect("s.*");
$query->addFrom("sfSetting s");
$query->addWhere("s.name = :name", array(":name" => $setting));
if ($obj = $query->limit(1)->execute()->getFirst()) {
// Store this setting in memory for later retrieval to avoid a second
// query for the same setting:
$settings[$obj->getName()] = $obj;
sfContext::getInstance()->getRequest()->setAttribute('sfSettings', $settings);
// return it:
return $obj;
} else {
return 0;
}
}
}
/**
* Implementation of method from Zend_Paginator_Adapter_Interface.
*
* @param integer $offset
* @param integer $itemsPerPage
* @return array[numeric|whatever]=>array|Doctrine_Record
*/
public function getItems($offset, $itemsPerPage)
{
$this->_query->limit($itemsPerPage);
$this->_query->offset($offset);
$result = $this->_query->execute(array(), $this->_hydrationMode);
return $result;
}
public function addUpcoming(Doctrine_Query $q, $limit = null)
{
$q->orderBy('start_date');
$q->addWhere('DATE(start_date) >= DATE(NOW())');
if (!is_null($limit)) {
$q->limit($limit);
}
}
public function testGetLimitSubqueryWithOrderByOnAggregateValuesAndColumns()
{
$q = new Doctrine_Query();
$q->select('u.name, COUNT(DISTINCT a.id) num_albums');
$q->from('User u, u.Album a');
$q->orderby('num_albums, u.name');
$q->groupby('u.id');
$q->limit(5);
$q->execute();
$this->assertEqual($this->dbh->pop(), 'SELECT e.id AS e__id, e.name AS e__name, COUNT(DISTINCT a.id) AS a__0 FROM entity e LEFT JOIN album a ON e.id = a.user_id WHERE e.id IN (SELECT doctrine_subquery_alias.id FROM (SELECT DISTINCT e2.id, COUNT(DISTINCT a2.id) AS a2__0 FROM entity e2 LEFT JOIN album a2 ON e2.id = a2.user_id WHERE (e2.type = 0) GROUP BY e2.id ORDER BY a2__0, e2.name LIMIT 5) AS doctrine_subquery_alias) AND (e.type = 0) GROUP BY e.id ORDER BY a__0, e.name');
}
public function testGetLimitSubqueryWithHavingOnAggregateValuesIncorrectAlias()
{
$q = new Doctrine_Query();
$q->select('u.name, COUNT(a.id) num_albums');
$q->from('User u, u.Album a');
$q->orderby('num_albums DESC');
$q->having('num_albums > 0');
$q->groupby('u.id');
$q->limit(5);
$q->execute();
$this->dbh->pop();
$this->assertEqual($this->dbh->pop(), 'SELECT DISTINCT e2.id, COUNT(a2.id) AS a2__0 FROM entity e2 LEFT JOIN album a2 ON e2.id = a2.user_id WHERE (e2.type = 0) GROUP BY e2.id HAVING a2__0 > 0 ORDER BY a2__0 DESC LIMIT 5');
}
public function testSubqueryExtractionUsesWrongAliases()
{
$q = new Doctrine_Query();
$q->from('RelX x');
$q->leftJoin('x.y xy');
$q->where('x.created_at IN (SELECT MAX(x2.created_at) latestInCategory FROM RelX x2 WHERE x.category = x2.category)');
$q->limit(5);
//echo $sql = $q->getSqlQuery();
// echo $sql;
$xs = $q->execute();
// Doctrine_Ticket_1254_TestCase : method testSubqueryExtractionUsesWrongAliases failed on line 76
// This fails sometimes at
$this->assertEqual(2, count($xs));
}
public function testSubqueryExtractionUsesWrongAliases()
{
$q = new Doctrine_Query();
$q->from('RelX x');
$q->leftJoin('x.y xy');
$q->where('x.created_at IN (SELECT MAX(x2.created_at) latestInCategory FROM RelX x2 WHERE x.category = x2.category)');
$q->limit(5);
//echo $sql = $q->getSql();
// echo $sql;
$xs = $q->execute();
$this->assertEqual(3, count($xs));
}
public function testGetLimitSubqueryOrderBy2()
{
$q = new Doctrine_Query();
$q->select('u.name, COUNT(DISTINCT a.id) num_albums');
$q->from('User u, u.Album a');
$q->orderby('num_albums');
$q->groupby('u.id');
try {
// this causes getLimitSubquery() to be used, and it fails
$q->limit(5);
$users = $q->execute();
$count = $users->count();
} catch (Doctrine_Exception $e) {
$this->fail();
}
$this->assertEqual($q->getSql(), 'SELECT e.id AS e__id, e.name AS e__name, COUNT(DISTINCT a.id) AS a__0 FROM entity e LEFT JOIN album a ON e.id = a.user_id WHERE e.id IN (SELECT DISTINCT e2.id FROM entity e2 LEFT JOIN album a2 ON e2.id = a2.user_id WHERE (e2.type = 0) GROUP BY e2.id ORDER BY a__0 LIMIT 5) AND (e.type = 0) GROUP BY e.id ORDER BY a__0');
}
public function testMultipleAggregateValuesWithArrayFetching()
{
$query = new Doctrine_Query();
$query->select('u.*, COUNT(DISTINCT b.id) num_books, COUNT(DISTINCT a.id) num_albums');
$query->from('User u');
$query->leftJoin('u.Album a, u.Book b');
$query->where("u.name = 'jon'");
$query->limit(1);
$users = $query->execute(array(), Doctrine_Core::HYDRATE_ARRAY);
try {
$name = $users[0]['name'];
$num_albums = $users[0]['num_albums'];
$num_books = $users[0]['num_books'];
} catch (Doctrine_Exception $e) {
$this->fail();
}
$this->assertEqual($num_albums, 3);
$this->assertEqual($num_books, 2);
}
/**
* Add a limit/offset to a Doctrine_Query.
*
* @param Doctrine_Query $q
* @param int $limit
* @param int $offset
*/
protected function rec_query_page(Doctrine_Query $q, $limit, $offset)
{
if ($limit > 0) {
$q->limit($limit);
}
if ($offset > 0) {
$q->offset($offset);
}
}
/**
* Returns an array of items for a page.
*
* @param integer $offset Page offset
* @param integer $itemCountPerPage Number of items per page
* @return array
*/
public function getItems($offset, $itemCountPerPage)
{
$this->_query->limit($itemCountPerPage)->offset($offset);
return $this->_query->execute();
}
/**
* set the hook limit
*
* @param integer $limit
* @return void
*/
public function hookLimit($limit)
{
$this->query->limit((int) $limit);
}
/**
* Typesafe call to modify
* @access private
**/
protected function modifyImpl(Doctrine_Query &$o)
{
$o->offset($this->offset);
if ($this->limit > 0) {
$o->limit($this->limit);
}
}
/**
* Build the query limit clause
*
* @param $start
* @param $offset
* @return Bvb_Grid_Source_Doctrine
*/
public function buildQueryLimit($start, $offset)
{
$this->_query->limit($start)->offset($offset);
return $this;
}
/**
* Create new iterator, uses $limit to run multiple queries in chunks
*
* @param Doctrine_Query $q
* @param int $limit
*/
public function __construct(Doctrine_Query $q, $limit)
{
$this->q = $q->limit($limit);
$this->offset = 0;
$this->limit = $limit;
}
public function testLimitSubqueryNotNeededIfSelectSingleFieldDistinct()
{
$q = new Doctrine_Query();
$q->select('u.id')->distinct()->from('User u LEFT JOIN u.Phonenumber p');
$q->where('u.name = ?');
$q->limit(5);
$users = $q->execute(array('zYne'));
$this->assertEqual(1, $users->count());
$this->assertEqual($q->getSql(), "SELECT DISTINCT e.id AS e__id FROM entity e LEFT JOIN phonenumber p ON e.id = p.entity_id WHERE e.name = ? AND (e.type = 0) LIMIT 5");
}
/**
* Returns an array of items for a page.
*
* @param integer $offset Page offset
* @param integer $itemCountPerPage Number of items per page
* @return array
*/
public function getItems($offset, $itemCountPerPage)
{
$this->_query->limit($itemCountPerPage)->offset($offset);
return $this->_query->execute(array(), $this->_hydrationMode);
}
static function setPagingFromOptions(Doctrine_Query $q, array $options, $defaultNum = null, $maxNum = null)
{
$num = @$options['num'] ? $options['num'] : $defaultNum;
if ($num && $maxNum) {
$num = $num > $maxNum ? $maxNum : $num;
}
if ($num) {
$q->limit($num);
if ($page = @$options['page']) {
$q->offset($num * ($page - 1));
}
}
return $q;
}
