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