vendor/doctrine/orm/tests/Doctrine/Tests/ORM/Functional/AdvancedDqlQueryTest.php

   1 <?php
   2
   3 namespace Doctrine\Tests\ORM\Functional;
   4
   5 use Doctrine\Tests\Models\Company\CompanyEmployee,
   6     Doctrine\Tests\Models\Company\CompanyManager,
   7     Doctrine\Tests\Models\Company\CompanyPerson,
   8     Doctrine\Tests\Models\Company\CompanyCar;
   9
  10 require_once __DIR__ . '/../../TestInit.php';
  11
  12 /**
  13  * Functional Query tests.
  14  *
  15  * @author Benjamin <kontakt@beberlei.de>
  16  */
  17 class AdvancedDqlQueryTest extends \Doctrine\Tests\OrmFunctionalTestCase
  18 {
  19     protected function setUp()
  20     {
  21         $this->useModelSet('company');
  22         parent::setUp();
  23
  24         $this->generateFixture();
  25     }
  26
  27     public function testAggregateWithHavingClause()
  28     {
  29         $dql = 'SELECT p.department, AVG(p.salary) AS avgSalary '.
  30                'FROM Doctrine\Tests\Models\Company\CompanyEmployee p '.
  31                'GROUP BY p.department HAVING SUM(p.salary) > 200000 ORDER BY p.department';
  32
  33         $result = $this->_em->createQuery($dql)->getScalarResult();
  34
  35         $this->assertEquals(2, count($result));
  36         $this->assertEquals('IT', $result[0]['department']);
  37         $this->assertEquals(150000, $result[0]['avgSalary']);
  38         $this->assertEquals('IT2', $result[1]['department']);
  39         $this->assertEquals(600000, $result[1]['avgSalary']);
  40     }
  41
  42     public function testUnnamedScalarResultsAreOneBased()
  43     {
  44         $dql = 'SELECT p.department, AVG(p.salary) '.
  45                'FROM Doctrine\Tests\Models\Company\CompanyEmployee p '.
  46                'GROUP BY p.department HAVING SUM(p.salary) > 200000 ORDER BY p.department';
  47
  48         $result = $this->_em->createQuery($dql)->getScalarResult();
  49
  50         $this->assertEquals(2, count($result));
  51         $this->assertEquals(150000, $result[0][1]);
  52         $this->assertEquals(600000, $result[1][1]);
  53     }
  54
  55     public function testOrderByResultVariableCollectionSize()
  56     {
  57         $dql = 'SELECT p.name, size(p.friends) AS friends ' .
  58                'FROM Doctrine\Tests\Models\Company\CompanyPerson p ' .
  59                'WHERE p.friends IS NOT EMPTY ' .
  60                'ORDER BY friends DESC, p.name DESC';
  61
  62         $result = $this->_em->createQuery($dql)->getScalarResult();
  63
  64         $this->assertEquals(4, count($result));
  65
  66         $this->assertEquals("Jonathan W.", $result[0]['name']);
  67         $this->assertEquals(3, $result[0]['friends']);
  68
  69         $this->assertEquals('Guilherme B.', $result[1]['name']);
  70         $this->assertEquals(2, $result[1]['friends']);
  71
  72         $this->assertEquals('Benjamin E.', $result[2]['name']);
  73         $this->assertEquals(2, $result[2]['friends']);
  74
  75         $this->assertEquals('Roman B.', $result[3]['name']);
  76         $this->assertEquals(1, $result[3]['friends']);
  77     }
  78
  79     public function testIsNullAssocation()
  80     {
  81         $dql = 'SELECT p FROM Doctrine\Tests\Models\Company\CompanyPerson p '.
  82                'WHERE p.spouse IS NULL';
  83         $result = $this->_em->createQuery($dql)->getResult();
  84
  85         $this->assertEquals(2, count($result));
  86         $this->assertTrue($result[0]->getId() > 0);
  87         $this->assertNull($result[0]->getSpouse());
  88
  89         $this->assertTrue($result[1]->getId() > 0);
  90         $this->assertNull($result[1]->getSpouse());
  91     }
  92
  93     public function testSelectSubselect()
  94     {
  95         $dql = 'SELECT p, (SELECT c.brand FROM Doctrine\Tests\Models\Company\CompanyCar c WHERE p.car = c) brandName '.
  96                'FROM Doctrine\Tests\Models\Company\CompanyManager p';
  97         $result = $this->_em->createQuery($dql)->getArrayResult();
  98
  99         $this->assertEquals(1, count($result));
 100         $this->assertEquals("Caramba", $result[0]['brandName']);
 101     }
 102
 103     public function testInSubselect()
 104     {
 105         $dql = "SELECT p.name FROM Doctrine\Tests\Models\Company\CompanyPerson p ".
 106                "WHERE p.name IN (SELECT n.name FROM Doctrine\Tests\Models\Company\CompanyPerson n WHERE n.name = 'Roman B.')";
 107         $result = $this->_em->createQuery($dql)->getScalarResult();
 108
 109         $this->assertEquals(1, count($result));
 110         $this->assertEquals('Roman B.', $result[0]['name']);
 111     }
 112
 113     public function testGroupByMultipleFields()
 114     {
 115         $dql = 'SELECT p.department, p.name, count(p.id) FROM Doctrine\Tests\Models\Company\CompanyEmployee p '.
 116                'GROUP BY p.department, p.name';
 117         $result = $this->_em->createQuery($dql)->getResult();
 118
 119         $this->assertEquals(4, count($result));
 120     }
 121
 122     public function testUpdateAs()
 123     {
 124         $dql = 'UPDATE Doctrine\Tests\Models\Company\CompanyEmployee AS p SET p.salary = 1';
 125         $this->_em->createQuery($dql)->execute();
 126
 127         $this->assertTrue(count($this->_em->createQuery(
 128             'SELECT count(p.id) FROM Doctrine\Tests\Models\Company\CompanyEmployee p WHERE p.salary = 1')->getResult()) > 0);
 129     }
 130
 131     public function testDeleteAs()
 132     {
 133         $dql = 'DELETE Doctrine\Tests\Models\Company\CompanyEmployee AS p';
 134         $this->_em->createQuery($dql)->getResult();
 135
 136         $dql = 'SELECT count(p) FROM Doctrine\Tests\Models\Company\CompanyEmployee p';
 137         $result = $this->_em->createQuery($dql)->getSingleScalarResult();
 138
 139         $this->assertEquals(0, $result);
 140     }
 141
 142     public function generateFixture()
 143     {
 144         $car = new CompanyCar('Caramba');
 145
 146         $manager1 = new CompanyManager();
 147         $manager1->setName('Roman B.');
 148         $manager1->setTitle('Foo');
 149         $manager1->setDepartment('IT');
 150         $manager1->setSalary(100000);
 151         $manager1->setCar($car);
 152
 153         $person2 = new CompanyEmployee();
 154         $person2->setName('Benjamin E.');
 155         $person2->setDepartment('IT');
 156         $person2->setSalary(200000);
 157
 158         $person3 = new CompanyEmployee();
 159         $person3->setName('Guilherme B.');
 160         $person3->setDepartment('IT2');
 161         $person3->setSalary(400000);
 162
 163         $person4 = new CompanyEmployee();
 164         $person4->setName('Jonathan W.');
 165         $person4->setDepartment('IT2');
 166         $person4->setSalary(800000);
 167
 168         $person2->setSpouse($person3);
 169
 170         $manager1->addFriend($person4);
 171         $person2->addFriend($person3);
 172         $person2->addFriend($person4);
 173         $person3->addFriend($person4);
 174
 175         $this->_em->persist($car);
 176         $this->_em->persist($manager1);
 177         $this->_em->persist($person2);
 178         $this->_em->persist($person3);
 179         $this->_em->persist($person4);
 180         $this->_em->flush();
 181         $this->_em->clear();
 182     }
 183 }