public function testCreateView()
{
$query = new Doctrine_Query($this->connection);
$query->from('User');
$view = new Doctrine_View($query, 'MyView');
$this->assertEqual($view->getName(), 'MyView');
$this->assertTrue($view->getQuery() === $query);
$this->assertTrue($view === $query->getView());
$this->assertTrue($view->getConnection() instanceof Doctrine_Connection);
$success = true;
try {
$view->create();
} catch (Exception $e) {
$success = false;
}
$this->assertTrue($success);
$users = $view->execute();
$count = $this->conn->count();
$this->assertTrue($users instanceof Doctrine_Collection);
$this->assertEqual($users->count(), 8);
$this->assertEqual($users[0]->name, 'zYne');
$this->assertEqual($users[0]->state(), Doctrine_Record::STATE_CLEAN);
$this->assertEqual($count, $this->conn->count());
$success = true;
try {
$view->drop();
} catch (Exception $e) {
$success = false;
}
$this->assertTrue($success);
}
public function execute(&$value, &$error)
{
$className = $this->getParameter('class');
$columnName = $className . '.' . $this->getParameter('column');
$primaryKeys = sfDoctrine::getTable($className)->getPrimaryKeys();
foreach ($primaryKeys as $primaryKey) {
if (is_null($primaryKeyValue = $this->getContext()->getRequest()->getParameter($primaryKey))) {
}
break;
}
$query = new Doctrine_Query();
$query->from($className);
$value = strtolower($value);
if ($primaryKeyValue === null) {
$query->where($columnName . ' = ?');
$res = $query->execute(array($value));
} else {
$query->where($columnName . ' = ? AND ' . $primaryKey . ' != ?');
$res = $query->execute(array($value, $primaryKeyValue));
}
if (sizeof($res)) {
$error = $this->getParameterHolder()->get('unique_error');
return false;
}
return true;
}
public function testZeroValuesMaintained3()
{
$q = new Doctrine_Query();
$q->from('ZeroValueTest');
$users = $q->execute();
$this->assertIdentical($users[0]['is_super_admin'], false);
}
public function testDiffForOneToManyRelatedCollection()
{
$q = new Doctrine_Query();
$q->from('User u LEFT JOIN u.Phonenumber p')
->where('u.id = 8');
$coll = $q->execute();
$this->assertEqual($coll->count(), 1);
$this->assertEqual($coll[0]->Phonenumber->count(), 3);
$this->assertTrue($coll[0]->Phonenumber instanceof Doctrine_Collection);
unset($coll[0]->Phonenumber[0]);
$coll[0]->Phonenumber->remove(2);
$this->assertEqual(count($coll[0]->Phonenumber->getSnapshot()), 3);
$coll[0]->save();
$this->assertEqual($coll[0]->Phonenumber->count(), 1);
$this->connection->clear();
$q = new Doctrine_Query();
$q = Doctrine_Query::create()->from('User u LEFT JOIN u.Phonenumber p')->where('u.id = 8');
$coll = $q->execute();
$this->assertEqual($coll[0]->Phonenumber->count(), 1);
}
public function testQuerySetOffsetToZero()
{
$q = new Doctrine_Query();
$q->from('User u');
$q->offset(20);
$q->offset(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)');
}
public function testApplyInheritance()
{
$query = new Doctrine_Query();
$query->from('InheritanceDeal d, d.Users u');
$query->where('u.id = 1');
$sql = 'SELECT i.id AS i__id, i.name AS i__name, i2.id AS i2__id, i2.username AS i2__username FROM inheritance_deal i LEFT JOIN inheritance_entity_user i3 ON (i.id = i3.entity_id) AND i3.type = 1 LEFT JOIN inheritance_user i2 ON i2.id = i3.user_id WHERE i2.id = 1';
$this->assertEqual($sql, $query->getSql());
}
public function testMultipleLeftJoin2()
{
$q = new Doctrine_Query();
$q->from('User u LEFT JOIN u.Group LEFT JOIN u.Phonenumber');
$this->assertEqual($q->getQuery(), "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, e2.id AS e2__id, e2.name AS e2__name, e2.loginname AS e2__loginname, e2.password AS e2__password, e2.type AS e2__type, e2.created AS e2__created, e2.updated AS e2__updated, e2.email_id AS e2__email_id, p.id AS p__id, p.phonenumber AS p__phonenumber, p.entity_id AS p__entity_id FROM entity e LEFT JOIN groupuser g ON e.id = g.user_id LEFT JOIN entity e2 ON e2.id = g.group_id AND e2.type = 1 LEFT JOIN phonenumber p ON e.id = p.entity_id WHERE (e.type = 0)");
}
public function testInlineMultiple()
{
$yml = <<<END
---
DC147_Multiple:
ISBN2:
name: isbn2
ISBN3:
name: isbn3
DC147_Product:
Product_1:
name: book3
MultipleValues:
Multi_1:
value: 123345678
Multiple: ISBN2
Multi_2:
value: 232323233
Multiple: ISBN3
Product_2:
name: book4
MultipleValues:
Multi_3:
value: 444455555
Multiple: ISBN2
Multi_4:
value: 232323233
Multiple: ISBN3
END;
try {
file_put_contents('test.yml', $yml);
Doctrine_Core::loadData('test.yml', true);
$this->conn->clear();
$query = new Doctrine_Query();
$query->from('DC147_Product p, p.MultipleValues v, v.Multiple m')->where('p.name = ?', 'book3');
$product = $query->fetchOne();
$this->assertEqual($product->name, 'book3');
$this->assertEqual($product->MultipleValues->count(), 2);
$this->assertEqual($product->MultipleValues[0]->value, '123345678');
$this->assertEqual(is_object($product->MultipleValues[0]->Multiple), true);
$this->assertEqual($product->MultipleValues[0]->Multiple->name, 'isbn2');
$query = new Doctrine_Query();
$query->from('DC147_Product p, p.MultipleValues v, v.Multiple m')->where('p.name = ?', 'book4');
$product = $query->fetchOne();
$this->assertEqual($product->name, 'book4');
$this->assertEqual($product->MultipleValues->count(), 2);
$this->assertEqual($product->MultipleValues[0]->value, '444455555');
$this->assertEqual($product->MultipleValues[1]->value, '232323233');
$this->assertEqual(is_object($product->MultipleValues[0]->Multiple), true);
$this->assertEqual(is_object($product->MultipleValues[1]->Multiple), true);
$this->assertEqual($product->MultipleValues[0]->Multiple->name, 'isbn2');
$this->assertEqual($product->MultipleValues[1]->Multiple->name, 'isbn3');
$this->pass();
} catch (Exception $e) {
$this->fail();
}
unlink('test.yml');
}
public function testTicket()
{
try {
$q = new Doctrine_Query();
$r = $q->from('T929_Person P')->leftJoin('P.Country Ct')->leftJoin('Ct.Translation T1 WITH T1.lang = ?', 'fr')->leftJoin('P.JobPositions J')->leftJoin('J.Category C')->leftJoin('C.Translation T2 WITH T2.lang = ?', 'fr')->where('P.name = ?', 'Jonathan')->fetchOne();
} catch (Exception $e) {
$this->fail($e->getMessage());
}
}
public function testSubclassReturnedIfInheritanceMatches()
{
$q = new Doctrine_Query();
$group = $q->from('Entity')->where('id=?')->execute(array(1))->getFirst();
$this->assertTrue($group instanceof Group);
$q = new Doctrine_Query();
$user = $q->from('Entity')->where('id=?')->execute(array(5))->getFirst();
$this->assertTrue($user instanceof User);
}
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 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 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');
}
/**
* 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 execute(&$value, &$error)
{
$value_temp = preg_replace('#\\s+#', ' ', $value);
$value_temp = strtolower(trim($value_temp));
$query = new Doctrine_Query();
$query->from('UserPrivateData')->where('(login_name = ?) OR (username = ?) OR (topo_name = ?)');
$res = $query->execute(array($value_temp, $value_temp, $value_temp));
if (sizeof($res)) {
$error = $this->getParameterHolder()->get('login_unique_error');
return false;
}
return true;
}
/**
* Get the version
*
* @param Doctrine_Record $record
* @param mixed $version
* @return array An array with version information
*/
public function getVersion(Doctrine_Record $record, $version)
{
$className = $this->_options['className'];
$q = new Doctrine_Query();
$values = array();
foreach ((array) $this->_options['table']->getIdentifier() as $id) {
$conditions[] = $className . '.' . $id . ' = ?';
$values[] = $record->get($id);
}
$where = implode(' AND ', $conditions) . ' AND ' . $className . '.' . $this->_options['versionColumn'] . ' = ?';
$values[] = $version;
$q->from($className)->where($where);
return $q->execute($values, Doctrine::HYDRATE_ARRAY);
}
public function execute(&$value, &$error)
{
$value_temp = preg_replace('#\\s+#', ' ', $value);
$value_temp = trim($value_temp);
$user_id = sfContext::getInstance()->getUser()->getId();
$query = new Doctrine_Query();
$query->from('UserPrivateData')->where('id != ? AND username = ?');
$res = $query->execute(array($user_id, $value_temp));
if (sizeof($res)) {
$error = $this->getParameterHolder()->get('nickname_unique_error');
return false;
}
return true;
}
public function testHasOneMultiLevelRelations()
{
$policy_code = new PolicyCodeN();
$policy_code->code = 1;
$policy_code->description = "Special Policy";
$policy_code->save();
$coverage_code = new CoverageCodeN();
$coverage_code->code = 1;
$coverage_code->description = "Full Coverage";
$coverage_code->save();
$coverage_code = new CoverageCodeN();
$coverage_code->code = 3;
# note we skip 2
$coverage_code->description = "Partial Coverage";
$coverage_code->save();
$liability_code = new LiabilityCodeN();
$liability_code->code = 1;
$liability_code->description = "Limited Territory";
$liability_code->save();
$rate = new RateN();
$rate->policy_code = 1;
$rate->coverage_code = 3;
$rate->liability_code = 1;
$rate->total_rate = 123.45;
$rate->save();
$policy = new PolicyN();
$policy->rate_id = 1;
$policy->policy_number = "123456789";
$policy->save();
$q = new Doctrine_Query();
# If I use
# $p = $q->from('PolicyN p')
# this test passes, but there is another issue just not reflected in this test yet, see "in my app" note below
$q->from('PolicyN p, p.RateN r, r.PolicyCodeN y, r.CoverageCodeN c, r.LiabilityCodeN l')->where('(p.id = ?)', array('1'));
$p = $q->execute()->getFirst();
$this->assertEqual($p->rate_id, 1);
$this->assertEqual($p->RateN->id, 1);
$this->assertEqual($p->RateN->policy_code, 1);
$this->assertEqual($p->RateN->coverage_code, 3);
# fail
$this->assertEqual($p->RateN->liability_code, 1);
$c = $p->RateN->coverage_code;
$c2 = $p->RateN->CoverageCodeN->code;
$c3 = $p->RateN->coverage_code;
$this->assertEqual($c, $c2);
# fail
$this->assertEqual($c, $c3);
# in my app this fails as well, but I can't reproduce this
#echo "Values " . serialize(array($c, $c2, $c3));
}
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 static function retrieveBySlug($slug, $culture = null)
{
if (is_null($culture)) {
$culture = aTools::getUserCulture();
}
$query = new Doctrine_Query();
$page = $query->from('aPage p')->where('p.slug = ?', $slug)->fetchOne();
// In case Doctrine is clever and returns the same page object
if ($page) {
$page->clearSlotCache();
$page->setCulture($culture);
}
return $page;
}
public function testQueryWithOneToManyRelation() {
$q = new Doctrine_Query($this->connection);
$q->from("User.Phonenumber");
$users = $q->execute(array(), Doctrine::FETCH_VHOLDER);
$this->assertEqual($users->count(), 8);
$this->assertTrue($users[0] instanceof Doctrine_ValueHolder);
$this->assertTrue($users[3] instanceof Doctrine_ValueHolder);
$this->assertTrue($users[7] instanceof Doctrine_ValueHolder);
$this->assertEqual(count($users[0]->Phonenumber), 1);
$this->assertEqual(count($users[1]->Phonenumber), 3);
$this->assertEqual(count($users[2]->Phonenumber), 1);
$this->assertEqual(count($users[3]->Phonenumber), 1);
$this->assertEqual(count($users[4]->Phonenumber), 3);
}
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 testManyToManyRelationFetchingWithAliasesAndCustomPKs2() {
$q = new Doctrine_Query();
try {
$q->from('M2MTest2 m INNER JOIN m.JC3');
$this->pass();
} catch(Doctrine_Exception $e) {
$this->fail();
}
try {
$q->execute();
$this->pass();
} catch(Doctrine_Exception $e) {
$this->fail();
}
}
public function testSelfReferencingWithNestedOrderBy()
{
$query = new Doctrine_Query();
$query->from('Forum_Category.Subcategory.Subcategory');
$query->orderby('Forum_Category.id ASC, Forum_Category.Subcategory.name DESC');
$coll = $query->execute();
$category = $coll[0];
$this->assertEqual($category->name, 'Root');
$this->assertEqual($category->Subcategory[0]->name, 'Sub 2');
$this->assertEqual($category->Subcategory[1]->name, 'Sub 1');
$this->assertEqual($category->Subcategory[1]->Subcategory[0]->name, 'Sub 1 Sub 1');
$this->assertEqual($category->Subcategory[1]->Subcategory[1]->name, 'Sub 1 Sub 2');
$this->assertEqual($category->Subcategory[0]->Subcategory[0]->name, 'Sub 2 Sub 1');
$this->assertEqual($category->Subcategory[0]->Subcategory[1]->name, 'Sub 2 Sub 2');
$this->connection->clear();
}
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');
}
/**
* Actualitza les sessions d'una setmana. Es pot especificar la data a la URL
* /horari/actualitza/X/Y/Z on X és el dia, Y el mes i Z l'any amb
* quatre xifres. Si no s'especifica data s'actualitza les sessions de la
* setmana actual.
*
* @param sfRequest $request A request object
*/
public function executeActualitza(sfWebRequest $request)
{
Doctrine_Manager::connection()->setAttribute(Doctrine_Core::ATTR_AUTO_FREE_QUERY_OBJECTS, true);
$query = new Doctrine_Query();
$query->from('CarreraCurs');
$carreresCursos = $query->execute();
// És un paràmetre opcional del mètode actualitza, l'inicialitzem a NULL.
$date = NULL;
// S'ha especificat una data per actualitzar
if ($request->getParameter('any')) {
$date = $request->getParameter('any') . '/' . $request->getParameter('mes') . '/' . $request->getParameter('dia');
}
foreach ($carreresCursos as $carreraCurs) {
$taulaSessions = Doctrine::getTable('Sessio');
$taulaSessions->actualitza($carreraCurs, $date);
}
}
public function testConflictingQueriesHydration()
{
// Query for the User with Phonenumbers joined in
// So you can access User->Phonenumber instanceof Doctrine_Collection
$query = new Doctrine_Query();
$query->from('User u, u.Phonenumber p');
$user1 = $query->execute()->getFirst();
// Now query for the same data, without the Phonenumber
$query = new Doctrine_Query();
$query->from('User u');
$user2 = $query->execute()->getFirst();
// Now if we try and see if Phonenumber is present in $user1 after the 2nd query
if (!isset($user1->Phonenumber)) {
$this->fail('Phonenumber overwritten by 2nd query hydrating');
} else {
$this->pass();
}
}
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 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);
}
