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

   1 <?php
   2
   3 namespace Doctrine\Tests\ORM\Functional;
   4
   5 use Doctrine\Tests\Models\Company\CompanyManager;
   6
   7 require_once __DIR__ . '/../../TestInit.php';
   8
   9 /**
  10  * Functional Query tests.
  11  *
  12  * @author robo
  13  */
  14 class QueryDqlFunctionTest extends \Doctrine\Tests\OrmFunctionalTestCase
  15 {
  16     protected function setUp()
  17     {
  18         $this->useModelSet('company');
  19         parent::setUp();
  20
  21         $this->generateFixture();
  22     }
  23
  24     public function testAggregateSum()
  25     {
  26         $salarySum = $this->_em->createQuery('SELECT SUM(m.salary) AS salary FROM Doctrine\Tests\Models\Company\CompanyManager m')
  27                                ->getSingleResult();
  28
  29         $this->assertEquals(1500000, $salarySum['salary']);
  30     }
  31
  32     public function testAggregateAvg()
  33     {
  34         $salaryAvg = $this->_em->createQuery('SELECT AVG(m.salary) AS salary FROM Doctrine\Tests\Models\Company\CompanyManager m')
  35                                ->getSingleResult();
  36
  37         $this->assertEquals(375000, round($salaryAvg['salary'], 0));
  38     }
  39
  40     public function testAggregateMin()
  41     {
  42         $salary = $this->_em->createQuery('SELECT MIN(m.salary) AS salary FROM Doctrine\Tests\Models\Company\CompanyManager m')
  43                                ->getSingleResult();
  44
  45         $this->assertEquals(100000, $salary['salary']);
  46     }
  47
  48     public function testAggregateMax()
  49     {
  50         $salary = $this->_em->createQuery('SELECT MAX(m.salary) AS salary FROM Doctrine\Tests\Models\Company\CompanyManager m')
  51                                ->getSingleResult();
  52
  53         $this->assertEquals(800000, $salary['salary']);
  54     }
  55
  56     public function testAggregateCount()
  57     {
  58         $managerCount = $this->_em->createQuery('SELECT COUNT(m.id) AS managers FROM Doctrine\Tests\Models\Company\CompanyManager m')
  59                                ->getSingleResult();
  60
  61         $this->assertEquals(4, $managerCount['managers']);
  62     }
  63
  64     public function testFunctionAbs()
  65     {
  66         $result = $this->_em->createQuery('SELECT m, ABS(m.salary * -1) AS abs FROM Doctrine\Tests\Models\Company\CompanyManager m')
  67                          ->getResult();
  68
  69         $this->assertEquals(4, count($result));
  70         $this->assertEquals(100000, $result[0]['abs']);
  71         $this->assertEquals(200000, $result[1]['abs']);
  72         $this->assertEquals(400000, $result[2]['abs']);
  73         $this->assertEquals(800000, $result[3]['abs']);
  74     }
  75
  76     public function testFunctionConcat()
  77     {
  78         $arg = $this->_em->createQuery('SELECT m, CONCAT(m.name, m.department) AS namedep FROM Doctrine\Tests\Models\Company\CompanyManager m')
  79                          ->getArrayResult();
  80
  81         $this->assertEquals(4, count($arg));
  82         $this->assertEquals('Roman B.IT', $arg[0]['namedep']);
  83         $this->assertEquals('Benjamin E.HR', $arg[1]['namedep']);
  84         $this->assertEquals('Guilherme B.Complaint Department', $arg[2]['namedep']);
  85         $this->assertEquals('Jonathan W.Administration', $arg[3]['namedep']);
  86     }
  87
  88     public function testFunctionLength()
  89     {
  90         $result = $this->_em->createQuery('SELECT m, LENGTH(CONCAT(m.name, m.department)) AS namedeplength FROM Doctrine\Tests\Models\Company\CompanyManager m')
  91                          ->getArrayResult();
  92
  93         $this->assertEquals(4, count($result));
  94         $this->assertEquals(10, $result[0]['namedeplength']);
  95         $this->assertEquals(13, $result[1]['namedeplength']);
  96         $this->assertEquals(32, $result[2]['namedeplength']);
  97         $this->assertEquals(25, $result[3]['namedeplength']);
  98     }
  99
 100     public function testFunctionLocate()
 101     {
 102         $dql = "SELECT m, LOCATE('e', LOWER(m.name)) AS loc, LOCATE('e', LOWER(m.name), 7) AS loc2 ".
 103                "FROM Doctrine\Tests\Models\Company\CompanyManager m";
 104
 105         $result = $this->_em->createQuery($dql)
 106                          ->getArrayResult();
 107
 108         $this->assertEquals(4, count($result));
 109         $this->assertEquals(0, $result[0]['loc']);
 110         $this->assertEquals(2, $result[1]['loc']);
 111         $this->assertEquals(6, $result[2]['loc']);
 112         $this->assertEquals(0, $result[3]['loc']);
 113         $this->assertEquals(0, $result[0]['loc2']);
 114         $this->assertEquals(10, $result[1]['loc2']);
 115         $this->assertEquals(9, $result[2]['loc2']);
 116         $this->assertEquals(0, $result[3]['loc2']);
 117     }
 118
 119     public function testFunctionLower()
 120     {
 121         $result = $this->_em->createQuery("SELECT m, LOWER(m.name) AS lowername FROM Doctrine\Tests\Models\Company\CompanyManager m")
 122                          ->getArrayResult();
 123
 124         $this->assertEquals(4, count($result));
 125         $this->assertEquals('roman b.', $result[0]['lowername']);
 126         $this->assertEquals('benjamin e.', $result[1]['lowername']);
 127         $this->assertEquals('guilherme b.', $result[2]['lowername']);
 128         $this->assertEquals('jonathan w.', $result[3]['lowername']);
 129     }
 130
 131     public function testFunctionMod()
 132     {
 133         $result = $this->_em->createQuery("SELECT m, MOD(m.salary, 3500) AS amod FROM Doctrine\Tests\Models\Company\CompanyManager m")
 134                          ->getArrayResult();
 135
 136         $this->assertEquals(4, count($result));
 137         $this->assertEquals(2000, $result[0]['amod']);
 138         $this->assertEquals(500, $result[1]['amod']);
 139         $this->assertEquals(1000, $result[2]['amod']);
 140         $this->assertEquals(2000, $result[3]['amod']);
 141     }
 142
 143     public function testFunctionSqrt()
 144     {
 145         $result = $this->_em->createQuery("SELECT m, SQRT(m.salary) AS sqrtsalary FROM Doctrine\Tests\Models\Company\CompanyManager m")
 146                          ->getArrayResult();
 147
 148         $this->assertEquals(4, count($result));
 149         $this->assertEquals(316, round($result[0]['sqrtsalary']));
 150         $this->assertEquals(447,  round($result[1]['sqrtsalary']));
 151         $this->assertEquals(632, round($result[2]['sqrtsalary']));
 152         $this->assertEquals(894, round($result[3]['sqrtsalary']));
 153     }
 154
 155     public function testFunctionUpper()
 156     {
 157         $result = $this->_em->createQuery("SELECT m, UPPER(m.name) AS uppername FROM Doctrine\Tests\Models\Company\CompanyManager m")
 158                          ->getArrayResult();
 159
 160         $this->assertEquals(4, count($result));
 161         $this->assertEquals('ROMAN B.', $result[0]['uppername']);
 162         $this->assertEquals('BENJAMIN E.', $result[1]['uppername']);
 163         $this->assertEquals('GUILHERME B.', $result[2]['uppername']);
 164         $this->assertEquals('JONATHAN W.', $result[3]['uppername']);
 165     }
 166
 167     public function testFunctionSubstring()
 168     {
 169         $dql = "SELECT m, SUBSTRING(m.name, 1, 3) AS str1, SUBSTRING(m.name, 5) AS str2 ".
 170                 "FROM Doctrine\Tests\Models\Company\CompanyManager m ORDER BY m.name";
 171
 172         $result = $this->_em->createQuery($dql)
 173                          ->getArrayResult();
 174
 175         $this->assertEquals(4, count($result));
 176         $this->assertEquals('Ben', $result[0]['str1']);
 177         $this->assertEquals('Gui', $result[1]['str1']);
 178         $this->assertEquals('Jon', $result[2]['str1']);
 179         $this->assertEquals('Rom', $result[3]['str1']);
 180
 181         $this->assertEquals('amin E.', $result[0]['str2']);
 182         $this->assertEquals('herme B.', $result[1]['str2']);
 183         $this->assertEquals('than W.', $result[2]['str2']);
 184         $this->assertEquals('n B.', $result[3]['str2']);
 185     }
 186
 187     public function testFunctionTrim()
 188     {
 189         $dql = "SELECT m, TRIM(TRAILING '.' FROM m.name) AS str1, ".
 190                " TRIM(LEADING '.' FROM m.name) AS str2, TRIM(CONCAT(' ', CONCAT(m.name, ' '))) AS str3 ".
 191                "FROM Doctrine\Tests\Models\Company\CompanyManager m";
 192
 193         $result = $this->_em->createQuery($dql)->getArrayResult();
 194
 195         $this->assertEquals(4, count($result));
 196         $this->assertEquals('Roman B', $result[0]['str1']);
 197         $this->assertEquals('Benjamin E', $result[1]['str1']);
 198         $this->assertEquals('Guilherme B', $result[2]['str1']);
 199         $this->assertEquals('Jonathan W', $result[3]['str1']);
 200         $this->assertEquals('Roman B.', $result[0]['str2']);
 201         $this->assertEquals('Benjamin E.', $result[1]['str2']);
 202         $this->assertEquals('Guilherme B.', $result[2]['str2']);
 203         $this->assertEquals('Jonathan W.', $result[3]['str2']);
 204         $this->assertEquals('Roman B.', $result[0]['str3']);
 205         $this->assertEquals('Benjamin E.', $result[1]['str3']);
 206         $this->assertEquals('Guilherme B.', $result[2]['str3']);
 207         $this->assertEquals('Jonathan W.', $result[3]['str3']);
 208     }
 209
 210     public function testOperatorAdd()
 211     {
 212         $result = $this->_em->createQuery('SELECT m, m.salary+2500 AS add FROM Doctrine\Tests\Models\Company\CompanyManager m')
 213                 ->getResult();
 214
 215         $this->assertEquals(4, count($result));
 216         $this->assertEquals(102500, $result[0]['add']);
 217         $this->assertEquals(202500, $result[1]['add']);
 218         $this->assertEquals(402500, $result[2]['add']);
 219         $this->assertEquals(802500, $result[3]['add']);
 220     }
 221
 222     public function testOperatorSub()
 223     {
 224         $result = $this->_em->createQuery('SELECT m, m.salary-2500 AS sub FROM Doctrine\Tests\Models\Company\CompanyManager m')
 225                 ->getResult();
 226
 227         $this->assertEquals(4, count($result));
 228         $this->assertEquals(97500, $result[0]['sub']);
 229         $this->assertEquals(197500, $result[1]['sub']);
 230         $this->assertEquals(397500, $result[2]['sub']);
 231         $this->assertEquals(797500, $result[3]['sub']);
 232     }
 233
 234     public function testOperatorMultiply()
 235     {
 236         $result = $this->_em->createQuery('SELECT m, m.salary*2 AS op FROM Doctrine\Tests\Models\Company\CompanyManager m')
 237                 ->getResult();
 238
 239         $this->assertEquals(4, count($result));
 240         $this->assertEquals(200000, $result[0]['op']);
 241         $this->assertEquals(400000, $result[1]['op']);
 242         $this->assertEquals(800000, $result[2]['op']);
 243         $this->assertEquals(1600000, $result[3]['op']);
 244     }
 245
 246     /**
 247      * @group test
 248      */
 249     public function testOperatorDiv()
 250     {
 251         $result = $this->_em->createQuery('SELECT m, (m.salary/0.5) AS op FROM Doctrine\Tests\Models\Company\CompanyManager m')
 252                 ->getResult();
 253
 254         $this->assertEquals(4, count($result));
 255         $this->assertEquals(200000, $result[0]['op']);
 256         $this->assertEquals(400000, $result[1]['op']);
 257         $this->assertEquals(800000, $result[2]['op']);
 258         $this->assertEquals(1600000, $result[3]['op']);
 259     }
 260
 261     public function testConcatFunction()
 262     {
 263         $arg = $this->_em->createQuery('SELECT CONCAT(m.name, m.department) AS namedep FROM Doctrine\Tests\Models\Company\CompanyManager m order by namedep desc')
 264                 ->getArrayResult();
 265
 266         $this->assertEquals(4, count($arg));
 267         $this->assertEquals('Roman B.IT', $arg[0]['namedep']);
 268         $this->assertEquals('Jonathan W.Administration', $arg[1]['namedep']);
 269         $this->assertEquals('Guilherme B.Complaint Department', $arg[2]['namedep']);
 270         $this->assertEquals('Benjamin E.HR', $arg[3]['namedep']);
 271     }
 272
 273     /**
 274      * @group DDC-1014
 275      */
 276     public function testDateDiff()
 277     {
 278         $query = $this->_em->createQuery("SELECT DATE_DIFF(CURRENT_TIMESTAMP(), DATE_ADD(CURRENT_TIMESTAMP(), 10, 'day')) AS diff FROM Doctrine\Tests\Models\Company\CompanyManager m");
 279         $arg = $query->getArrayResult();
 280
 281         $this->assertEquals(-10, $arg[0]['diff'], "Should be roughly -10 (or -9)", 1);
 282
 283         $query = $this->_em->createQuery("SELECT DATE_DIFF(DATE_ADD(CURRENT_TIMESTAMP(), 10, 'day'), CURRENT_TIMESTAMP()) AS diff FROM Doctrine\Tests\Models\Company\CompanyManager m");
 284         $arg = $query->getArrayResult();
 285
 286         $this->assertEquals(10, $arg[0]['diff'], "Should be roughly 10 (or 9)", 1);
 287     }
 288
 289     /**
 290      * @group DDC-1014
 291      */
 292     public function testDateAdd()
 293     {
 294         $arg = $this->_em->createQuery("SELECT DATE_ADD(CURRENT_TIMESTAMP(), 10, 'day') AS add FROM Doctrine\Tests\Models\Company\CompanyManager m")
 295                 ->getArrayResult();
 296
 297         $this->assertTrue(strtotime($arg[0]['add']) > 0);
 298
 299         $arg = $this->_em->createQuery("SELECT DATE_ADD(CURRENT_TIMESTAMP(), 10, 'month') AS add FROM Doctrine\Tests\Models\Company\CompanyManager m")
 300                 ->getArrayResult();
 301
 302         $this->assertTrue(strtotime($arg[0]['add']) > 0);
 303     }
 304
 305     /**
 306      * @group DDC-1014
 307      */
 308     public function testDateSub()
 309     {
 310         $arg = $this->_em->createQuery("SELECT DATE_SUB(CURRENT_TIMESTAMP(), 10, 'day') AS add FROM Doctrine\Tests\Models\Company\CompanyManager m")
 311                 ->getArrayResult();
 312
 313         $this->assertTrue(strtotime($arg[0]['add']) > 0);
 314
 315         $arg = $this->_em->createQuery("SELECT DATE_SUB(CURRENT_TIMESTAMP(), 10, 'month') AS add FROM Doctrine\Tests\Models\Company\CompanyManager m")
 316                 ->getArrayResult();
 317
 318         $this->assertTrue(strtotime($arg[0]['add']) > 0);
 319     }
 320
 321     /**
 322      * @group DDC-1213
 323      */
 324     public function testBitOrComparison()
 325     {
 326         $dql    = 'SELECT m, ' .
 327                     'BIT_OR(4, 2) AS bit_or,' .
 328                     'BIT_OR( (m.salary/100000) , 2 ) AS salary_bit_or ' .
 329                     'FROM Doctrine\Tests\Models\Company\CompanyManager m ' .
 330                 'ORDER BY ' .
 331                     'm.id ' ;
 332         $result = $this->_em->createQuery($dql)->getArrayResult();
 333
 334         $this->assertEquals(4 | 2, $result[0]['bit_or']);
 335         $this->assertEquals(4 | 2, $result[1]['bit_or']);
 336         $this->assertEquals(4 | 2, $result[2]['bit_or']);
 337         $this->assertEquals(4 | 2, $result[3]['bit_or']);
 338
 339         $this->assertEquals(($result[0][0]['salary']/100000) | 2, $result[0]['salary_bit_or']);
 340         $this->assertEquals(($result[1][0]['salary']/100000) | 2, $result[1]['salary_bit_or']);
 341         $this->assertEquals(($result[2][0]['salary']/100000) | 2, $result[2]['salary_bit_or']);
 342         $this->assertEquals(($result[3][0]['salary']/100000) | 2, $result[3]['salary_bit_or']);
 343     }
 344
 345     /**
 346     * @group DDC-1213
 347     */
 348     public function testBitAndComparison()
 349     {
 350         $dql    = 'SELECT m, ' .
 351                     'BIT_AND(4, 2) AS bit_and,' .
 352                     'BIT_AND( (m.salary/100000) , 2 ) AS salary_bit_and ' .
 353                     'FROM Doctrine\Tests\Models\Company\CompanyManager m ' .
 354                 'ORDER BY ' .
 355                     'm.id ' ;
 356         $result = $this->_em->createQuery($dql)->getArrayResult();
 357
 358         $this->assertEquals(4 & 2, $result[0]['bit_and']);
 359         $this->assertEquals(4 & 2, $result[1]['bit_and']);
 360         $this->assertEquals(4 & 2, $result[2]['bit_and']);
 361         $this->assertEquals(4 & 2, $result[3]['bit_and']);
 362
 363         $this->assertEquals(($result[0][0]['salary']/100000) & 2, $result[0]['salary_bit_and']);
 364         $this->assertEquals(($result[1][0]['salary']/100000) & 2, $result[1]['salary_bit_and']);
 365         $this->assertEquals(($result[2][0]['salary']/100000) & 2, $result[2]['salary_bit_and']);
 366         $this->assertEquals(($result[3][0]['salary']/100000) & 2, $result[3]['salary_bit_and']);
 367     }
 368
 369     protected function generateFixture()
 370     {
 371         $manager1 = new CompanyManager();
 372         $manager1->setName('Roman B.');
 373         $manager1->setTitle('Foo');
 374         $manager1->setDepartment('IT');
 375         $manager1->setSalary(100000);
 376
 377         $manager2 = new CompanyManager();
 378         $manager2->setName('Benjamin E.');
 379         $manager2->setTitle('Foo');
 380         $manager2->setDepartment('HR');
 381         $manager2->setSalary(200000);
 382
 383         $manager3 = new CompanyManager();
 384         $manager3->setName('Guilherme B.');
 385         $manager3->setTitle('Foo');
 386         $manager3->setDepartment('Complaint Department');
 387         $manager3->setSalary(400000);
 388
 389         $manager4 = new CompanyManager();
 390         $manager4->setName('Jonathan W.');
 391         $manager4->setTitle('Foo');
 392         $manager4->setDepartment('Administration');
 393         $manager4->setSalary(800000);
 394
 395         $this->_em->persist($manager1);
 396         $this->_em->persist($manager2);
 397         $this->_em->persist($manager3);
 398         $this->_em->persist($manager4);
 399         $this->_em->flush();
 400         $this->_em->clear();
 401     }
 402 }