/**
* Constructs Neo_Doctrine_Paginator_Adapter
*
* @param Doctrine_Query_Abstract|string $query
* @param integer $hydratationMode Use constaints Doctrine_Core::HYDRATE_*.
* @param array[string]=>mixed $options Options may be:
* 'countQuery'-custom query for count counting. Dql or Doctrine_Query instance.
*/
public function __construct($query, $hydrationMode = null, $options = array())
{
if (is_string($query)) {
$newQuery = new Doctrine_Query();
$newQuery->parseDqlQuery($query);
$query = $newQuery;
} else {
if (!$query instanceof Doctrine_Query_Abstract) {
require_once 'Neo/Doctrine/Paginator/Adapter/Exception.php';
throw new Neo_Doctrine_Paginator_Adapter_Exception("Given query is not instance of Doctrine_Query");
}
}
$this->_query = $query;
$this->_hydrationMode = is_null($hydrationMode) ? Doctrine_Core::HYDRATE_RECORD : $hydrationMode;
//options
if (!empty($options['countQuery'])) {
if (is_string($options['countQuery'])) {
$countQuery = new Doctrine_Query();
$countQuery->parseDqlQuery($options['countQuery']);
$options['countQuery'] = $countQuery;
} else {
if (!$options['countQuery'] instanceof Doctrine_Query) {
require_once 'Neo/Doctrine/Paginator/Adapter/Exception.php';
throw new Neo_Doctrine_Paginator_Adapter_Exception("Given count-query is not instance of Doctrine_Query");
}
}
$this->_countQuery = $options['countQuery'];
$this->_countQuery->select('count(*) as count');
}
}
public function executeRemove()
{
$id = $this->getRequestParameter('id');
if ($id) {
$q = new Doctrine_Query();
if ($this->getUser()->hasCredential('admin')) {
$this->comment = $q->select('c.*')->from('Comment c')->where('id = ?', array($id))->execute()->getFirst();
} else {
$this->comment = $q->select('c.*')->from('Comment c')->where('id = ? and user_id = ?', array($id, $this->getUser()->getId()))->execute()->getFirst();
}
$this->forward404Unless($this->comment);
$this->comment->delete();
}
return $this->redirect($this->getRequest()->getReferer());
}
public function retrieveAsso(Doctrine_Query $q)
{
$alias = $q->getRootAlias();
$q->select("{$alias}.name, {$alias}.login, {$alias}.description, {$alias}.logo, {$alias}.salle, {$alias}.phone, {$alias}.facebook, p.id, p.asso_id, p.couleur");
$q->leftJoin("{$alias}.Pole p");
return $q->fetchOne();
}
/**
public function testShortAliasesWithSingleComponent() {
$q = new Doctrine_Query();
$q->select('u.name')->from('User u');
$this->assertEqual($q->getSqlQuery(), 'SELECT e.id AS e__id, e.name AS e__name FROM entity e WHERE (e.type = 0)');
}
*/
public function testShortAliasesWithOneToManyLeftJoin()
{
$q = new Doctrine_Query();
$q->select('u.name, p.id')->from('User u LEFT JOIN u.Phonenumber p');
$this->assertEqual($q->getSqlQuery(), 'SELECT e.id AS e__id, e.name AS e__name, p.id AS p__id FROM entity e LEFT JOIN phonenumber p ON e.id = p.entity_id WHERE (e.type = 0)');
$users = $q->execute();
$this->assertEqual($users->count(), 8);
}
public function executeShowcase()
{
$q = new Doctrine_Query();
$this->madule = $q->select('m.*')->from('Madule m')->where('id = ?', array($this->getRequestParameter('id')))->fetchOne();
$this->forward404Unless($this->madule);
$this->madule->setShowcase(!$this->madule->getShowcase());
$this->madule->save();
return $this->redirect($this->getRequest()->getReferer());
}
public static function getEschoolTypes($public_only = false)
{
$q = new Doctrine_Query();
$q->select('e.*');
$q->from('GcrEschoolType e');
if ($public_only) {
$q->where('e.is_public = ?', 't');
}
return $q->execute();
}
public function testHydratation()
{
$q = new Doctrine_Query();
$q->select('t.name')->from('NewTag t INDEXBY t.name');
try {
$results = $q->execute(array(), Doctrine_Core::HYDRATE_ARRAY);
} catch (Exception $e) {
$this->fail($e->getMessage());
}
}
public function testFetchArraySupportsTwoAggregatesInRelationAndRoot()
{
$q = new Doctrine_Query();
$q->select("u.*, p.*, CONCAT(u.name, '_1') concat1, CONCAT(u.name, '_2') concat2, CONCAT(p.phonenumber, '_3') concat3, CONCAT(p.phonenumber, '_3') concat4")->from('User u')->innerJoin('u.Phonenumber p')->where("u.name = 'zYne'");
$users = $q->execute(array(), Doctrine::HYDRATE_ARRAY);
$this->assertEqual($users[0]['concat1'], 'zYne_1');
$this->assertEqual($users[0]['concat2'], 'zYne_2');
$this->assertEqual($users[0]['Phonenumber'][0]['concat3'], '123 123_3');
$this->assertTrue(isset($users[0]['Phonenumber'][0]['concat4']));
}
public function testTicket()
{
try {
$q = new Doctrine_Query();
$result = $q->select('d.*')->from('T923_Diagnostic d')->where('d.diag_timestamp >= ? AND d.diag_timestamp <= ?', array('2008-03-27 00:00:00', '2008-03-27 23:00:00'))->addWhere('d.id_type = ?', array('101'))->orderBy('d.diag_timestamp')->limit(20)->offset(0)->execute();
$this->assertEqual($result->count(), 3);
} catch (Exception $e) {
$this->fail($e->getMessage());
}
}
public static function getTypes($public_only = false)
{
$q = new Doctrine_Query();
$q->select('i.*');
$q->from('GcrInstitutionType i');
if ($public_only) {
$q->where('i.is_public = ?', 't');
}
return $q->execute();
}
public function testDoctrineQueryJoinSelect()
{
$q = new Doctrine_Query();
$q->select('person.id, points.total')->from('T1015_Person person')->innerJoin('person.T1015_Points points WITH person.id = 1');
$results = $q->execute(array(), Doctrine_Core::HYDRATE_ARRAY);
//var_dump($results);
$person = $results[0];
// number of points for person id of 1 should be 15
$this->assertEqual(15, $person['T1015_Points']['total']);
//THIS WILL FAIL
}
public function testMultipleJoinFetchingWithArrayFetching()
{
$query = new Doctrine_Query($this->connection);
$queryCount = $this->connection->count();
try {
$categories = $query->select('c.*, subCats.*, b.*, le.*, a.*')->from('QueryTest_Category c')->leftJoin('c.subCategories subCats')->leftJoin('c.boards b')->leftJoin('b.lastEntry le')->leftJoin('le.author a')->where('c.parentCategoryId = 0')->orderBy('c.position ASC, subCats.position ASC, b.position ASC')->execute(array(), Doctrine::HYDRATE_ARRAY);
$this->pass();
} catch (Doctrine_Exception $e) {
$this->fail($e->getMessage());
}
}
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 testTicket()
{
try {
$q = new Doctrine_Query();
$q->update('EnumUpdateBug')->set('bla_id', '?', 5)->set('separator', '?', 'pipe')->where('id = 1')->execute();
} catch (Exception $e) {
$this->fail($e->getMessage());
}
$q = new Doctrine_Query();
$row = $q->select('a.*')->from('EnumUpdateBug a')->where('a.id = 1')->fetchOne();
$this->assertEqual($row->bla_id, 5);
}
public function prepareUpdate()
{
$id = $this->getRequestParameter('id');
if ($id) {
$q = new Doctrine_Query();
$this->themeGroup = $q->select('t.*')->from('ThemeGroup t')->where('id = ?', array($id))->execute()->getFirst();
$this->forward404Unless($this->themeGroup);
} else {
$this->themeGroup = new ThemeGroup();
}
$this->form = new ThemeGroupForm(array('title' => $this->themeGroup->getTitle(), 'known' => $this->themeGroup->getKnown()));
}
public function testOrderByWithDifferentOrderning()
{
try {
$q = new Doctrine_Query();
$q->select('u.name')->from('User u')->orderby('u.id ASC, u.name DESC');
// nonesese results expected, but query is syntatically ok.
$this->assertEqual($q->getQuery(), 'SELECT e.id AS e__id, e.name AS e__name FROM entity e WHERE (e.type = 0) ORDER BY e.id ASC, e.name DESC');
$this->pass();
} catch (Exception $e) {
$this->fail($e->getMessage());
}
}
public function testManyToManyJoinsandSave()
{
$q = new Doctrine_Query();
$newdata = $q->select('d.*, i.*, u.*, c.*')->from('TestMovie d, d.MovieBookmarks i, i.UserVotes u, u.User c')->execute()->getFirst();
$newdata['MovieBookmarks'][0]['UserVotes'][0]['User']['name'] = 'user2';
try {
$newdata->save();
$this->pass();
} catch (Doctrine_Exception $e) {
$this->fail();
}
}
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 execute($filterChain)
{
if ($this->isFirstCall() and !$this->context->getUser()->isAuthenticated()) {
if ($cookie = $this->getContext()->getRequest()->getCookie('remember')) {
$q = new Doctrine_Query();
$user = $q->select('u.*')->from('User u')->where('hash = ? and active = ?', array($cookie, true))->execute()->getFirst();
if ($user) {
$this->context->getUser()->logIn($user);
}
}
}
$filterChain->execute();
}
/**
* Test the existence expected indexes
*/
public function testTicket()
{
$q = new Doctrine_Query();
// simple query with 1 column selected
$cAuthors = $q->select('book_id')->from('Author')->groupBy('book_id')->where('book_id = 2')->execute();
// simple query, with 1 join and all columns selected
$cAuthors = $q->from('Author, Author.Book')->execute();
foreach ($cAuthors as $oAuthor) {
if (!$oAuthor->name) {
$this->fail('Querying the same table multiple times triggers hydration/caching(?) bug');
}
}
}
public function executeNavigation($request)
{
$module = $this->getContext()->getModuleName();
$action = $this->getContext()->getActionName();
$this->module = $module;
$this->action = $action;
$this->type = $request->getParameter('type');
$this->application_id = $request->getParameter('application_id');
if ($module == 'module' && ($action == 'read' || $action == 'update')) {
$madule = Doctrine::getTable('Madule')->find($request->getParameter('id'));
if ($madule) {
$this->application_id = $madule->getApplicationId();
}
}
$q = new Doctrine_Query();
$this->themeGroups = $q->select('t.*')->from('ThemeGroup t')->where('t.known = ?', array(true))->orderby('t.title ASC')->execute();
$this->id = $this->getRequestParameter('id');
$this->currentThemeGroup = null;
if ($this->module == 'themeGroup') {
$this->currentThemeGroup = $q->select('t.*')->from('ThemeGroup t')->where('t.id = ? and t.known = ?', array($this->id, true))->orderby('t.title ASC')->fetchOne();
}
}
/**
* Test the existence expected indexes
*/
public function testTicket()
{
$queryCountBefore = $this->conn->count();
try {
$q = new Doctrine_Query();
$r = $q->select('P.id, J.name, C.code, T.name')->from('T930_Person P')->leftJoin('P.JobPositions J')->leftJoin('J.Category C')->leftJoin('C.Translation T WITH T.lang = ?', 'fr')->fetchArray();
} catch (Exception $e) {
$this->fail($e->getMessage());
}
$this->assertEqual($queryCountBefore + 1, $this->conn->count());
$this->assertTrue(isset($r[0]['JobPositions'][0]['Category']['Translation']['fr']['name']));
$this->assertTrue(isset($r[0]['JobPositions'][1]['Category']['Translation']['fr']['name']));
$this->assertEqual($queryCountBefore + 1, $this->conn->count());
}
public function testTicket()
{
$q = new Doctrine_Query();
$result = $q->select('a.*, b.*, c.*')->from('T1 a')->leftJoin('a.T2 b')->leftJoin('b.T3 c')->setHydrationMode(Doctrine::HYDRATE_ARRAY)->fetchOne();
// there are 10 rows in T3, and they all have hello_id = 10, so we should have 10 rows here
$this->assertEqual(10, count($result["T2"]["T3"]));
// now with object hydration.
$q = new Doctrine_Query();
$result = $q->select('a.*, b.*, c.*')->from('T1 a')->leftJoin('a.T2 b')->leftJoin('b.T3 c')->fetchOne();
// test that no additional queries are executed when accessing the relations (lazy-loading).
$queryCountBefore = $this->conn->count();
// there are 10 rows in T3, and they all have hello_id = 10, so we should have 10 rows here
$this->assertEqual(10, count($result["T2"]["T3"]));
$this->assertEqual($queryCountBefore, $this->conn->count());
}
public function testAggregateValueMappingSupportsLeftJoins()
{
foreach ($this->_albums as $album) {
$album->clearRelated();
}
$q = new Doctrine_Query();
$q->select('a.name, COUNT(s.id) count')->from('Album a')->leftJoin('a.Song s')->groupby('a.id');
$albums = $q->execute();
try {
// Collection[0] should refer to the object with aggregate value
$this->assertEqual($albums[0]['count'], 4);
} catch (Exception $e) {
$this->fail('count aggregate value should be available');
}
}
public function executeRate()
{
$params = explode('.', $this->getRequestParameter('object_id'));
$type = $params[0];
$object_id = $params[1];
$value = $this->getRequestParameter('value');
$rating = null;
if ($type == Rating::APPLICATION) {
$object = Doctrine::getTable('Application')->find($object_id);
$q = new Doctrine_Query();
$rating = $q->select('r.*')->from('Rating r')->where('application_id = ? and user_id = ?', array($object_id, $this->getUser()->getId()))->fetchOne();
if (!$rating) {
$rating = new Rating();
$rating->setUserId($this->getUser()->getId());
$rating->setApplicationId($object_id);
}
} elseif ($type == Rating::MODULE) {
$object = Doctrine::getTable('Madule')->find($object_id);
$q = new Doctrine_Query();
$rating = $q->select('r.*')->from('Rating r')->where('madule_id = ? and user_id = ?', array($object_id, $this->getUser()->getId()))->fetchOne();
if (!$rating) {
$rating = new Rating();
$rating->setUserId($this->getUser()->getId());
$rating->setMaduleId($object_id);
}
} elseif ($type == Rating::THEME) {
$object = Doctrine::getTable('Theme')->find($object_id);
$q = new Doctrine_Query();
$rating = $q->select('r.*')->from('Rating r')->where('theme_id = ? and user_id = ?', array($object_id, $this->getUser()->getId()))->fetchOne();
if (!$rating) {
$rating = new Rating();
$rating->setUserId($this->getUser()->getId());
$rating->setThemeId($object_id);
}
}
$this->forward404Unless($value <= 5);
$this->forward404Unless($value >= 1);
if ($rating) {
$rating->setValue($value);
$rating->save();
$this->my_rating = $value;
$this->rating = $object->getRating();
}
}
public function testBug()
{
$person = $this->newPerson('Fixe');
$profile = $this->newProfile('Work', $person);
$guardUser = $person->get('sfGuardUser');
$id = $guardUser->get('id');
$guardUser->free();
$query = new Doctrine_Query();
$query->select('s.*, p.*, ps.*');
$query->from('sfGuardUser s');
$query->innerJoin('s.Person p');
$query->leftJoin('p.Profiles ps');
$query->where('s.id = ?', $id);
$user = $query->fetchOne();
$array = $user->toArray(true);
$this->assertEqual($array['id'], 1);
$this->assertEqual($array['name'], 'Fixe');
$this->assertTrue(isset($array['Person']['Profiles'][0]));
}
public function executeRemove()
{
$id = $this->getRequestParameter('id');
if ($id) {
$q = new Doctrine_Query();
$this->applicationThemeGroup = $q->select('a.*')->from('ApplicationThemeGroup a')->where('id = ?', array($id))->execute()->getFirst();
$this->forward404Unless($this->applicationThemeGroup);
$this->applicationThemeGroup->delete();
}
$application_id = $this->getRequestParameter('application_id');
$theme_group_id = $this->getRequestParameter('theme_group_id');
if ($application_id && $theme_group_id) {
$q = new Doctrine_Query();
$this->applicationThemeGroup = $q->select('a.*')->from('ApplicationThemeGroup a')->where('application_id = ? and theme_group_id = ?', array($application_id, $theme_group_id))->execute()->getFirst();
$this->forward404Unless($this->applicationThemeGroup);
$this->applicationThemeGroup->delete();
}
return $this->redirect($this->getRequest()->getReferer());
}
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);
}
/**
* @return
*/
protected function setOrderBy()
{
if ($this->_orderby_run) {
return;
}
switch ($this->_order_by) {
case 'slice_title':
case 'slice_filesize':
case 'slice_xsize':
case 'slice_ysize':
case 'slice_updated_at':
case 'slice_created_at':
$this->_query->orderBy('s.' . str_replace('slice_', '', $this->_order_by) . ' ' . $this->_order_direction);
break;
case 'file_title':
case 'file_filesize':
case 'file_xsize':
case 'file_ysize':
case 'file_updated_at':
case 'file_created_at':
$this->_query->leftJoin('s.File f');
$this->_query->orderBy('f.' . str_replace('file_', '', $this->_order_by) . ' ' . $this->_order_direction);
break;
case 'views':
$this->_query->leftJoin('s.File f');
$this->_query->leftJoin('f.Views v');
$this->_query->orderBy('v.count ' . $this->_order_direction);
break;
case 'loves':
$this->_query->leftJoin('s.File f');
$this->_query->leftJoin('f.Loves l');
$this->_query->orderBy('l.count ' . $this->_order_direction);
break;
case 'random':
$this->_query->select('s.*, RANDOM() as rand');
$this->_query->orderby('rand');
break;
case 'gallery_ordering':
$this->_query->orderBy('s.ordering');
}
$this->_orderby_run = true;
}
public function addThemeGroup($name)
{
$name = trim($name);
$q = new Doctrine_Query();
$maduleThemeGroup = $q->select('mt.*')->from('MaduleThemeGroup mt, mt.ThemeGroup t')->addWhere('mt.madule_id = ? and t.name = ?', array($this->getId(), $name))->fetchOne();
if (!$maduleThemeGroup) {
$q = new Doctrine_Query();
$themeGroup = $q->select('t.*')->from('ThemeGroup t')->addWhere('t.name = ?', array($name))->fetchOne();
if (!$themeGroup) {
$themeGroup = new ThemeGroup();
$themeGroup->setName($name);
$themeGroup->save();
}
$maduleThemeGroup = new MaduleThemeGroup();
$maduleThemeGroup->setThemeGroupId($themeGroup->getId());
$maduleThemeGroup->setMaduleId($this->getId());
$maduleThemeGroup->save();
}
return $maduleThemeGroup;
}
