QueryDqlFunctionTest::dateAddSubProvider() - Code Metrics - Inspection of "Merge pull request #6742 from Legenyes/feature/DDC..." - doctrine/orm - Measure and Improve Code Quality continuously with Scrutinizer

Failed Conditions

Push — master ( ed86ee...a82f6c )

by Luís

created 2017-10-31 19:46 UTC

QueryDqlFunctionTest::dateAddSubProvider() A

↳ Parent: QueryDqlFunctionTest

Complexity

Conditions	1
Paths	1

Size

Total Lines	13
Code Lines	9

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
dl	0
loc	13
rs	9.4285
c	0
b	0
f	0
cc	1
eloc	9
nc	1
nop	0

<?php

namespace Doctrine\Tests\ORM\Functional;

use Doctrine\ORM\AbstractQuery;
use Doctrine\Tests\Models\Company\CompanyManager;
use Doctrine\Tests\OrmFunctionalTestCase;

/**
 * Functional Query tests.
 *
 * @author robo
 */
class QueryDqlFunctionTest extends OrmFunctionalTestCase
{
    protected function setUp()
    {
        $this->useModelSet('company');
        parent::setUp();

        $this->generateFixture();
    }

    public function testAggregateSum()
    {
        $salarySum = $this->_em->createQuery('SELECT SUM(m.salary) AS salary FROM Doctrine\Tests\Models\Company\CompanyManager m')
                               ->getSingleResult();

        $this->assertEquals(1500000, $salarySum['salary']);
    }

    public function testAggregateAvg()
    {
        $salaryAvg = $this->_em->createQuery('SELECT AVG(m.salary) AS salary FROM Doctrine\Tests\Models\Company\CompanyManager m')
                               ->getSingleResult();

        $this->assertEquals(375000, round($salaryAvg['salary'], 0));
    }

    public function testAggregateMin()
    {
        $salary = $this->_em->createQuery('SELECT MIN(m.salary) AS salary FROM Doctrine\Tests\Models\Company\CompanyManager m')
                               ->getSingleResult();

        $this->assertEquals(100000, $salary['salary']);
    }

    public function testAggregateMax()
    {
        $salary = $this->_em->createQuery('SELECT MAX(m.salary) AS salary FROM Doctrine\Tests\Models\Company\CompanyManager m')
                               ->getSingleResult();

        $this->assertEquals(800000, $salary['salary']);
    }

    public function testAggregateCount()
    {
        $managerCount = $this->_em->createQuery('SELECT COUNT(m.id) AS managers FROM Doctrine\Tests\Models\Company\CompanyManager m')
                               ->getSingleResult();

        $this->assertEquals(4, $managerCount['managers']);
    }

    public function testFunctionAbs()
    {
        $result = $this->_em->createQuery('SELECT m, ABS(m.salary * -1) AS abs FROM Doctrine\Tests\Models\Company\CompanyManager m ORDER BY m.salary ASC')
                         ->getResult();

        $this->assertEquals(4, count($result));
        $this->assertEquals(100000, $result[0]['abs']);
        $this->assertEquals(200000, $result[1]['abs']);
        $this->assertEquals(400000, $result[2]['abs']);
        $this->assertEquals(800000, $result[3]['abs']);
    }

    public function testFunctionConcat()
    {
        $arg = $this->_em->createQuery('SELECT m, CONCAT(m.name, m.department) AS namedep FROM Doctrine\Tests\Models\Company\CompanyManager m ORDER BY m.salary ASC')
                         ->getArrayResult();

        $this->assertEquals(4, count($arg));
        $this->assertEquals('Roman B.IT', $arg[0]['namedep']);
        $this->assertEquals('Benjamin E.HR', $arg[1]['namedep']);
        $this->assertEquals('Guilherme B.Complaint Department', $arg[2]['namedep']);
        $this->assertEquals('Jonathan W.Administration', $arg[3]['namedep']);
    }

    public function testFunctionLength()
    {
        $result = $this->_em->createQuery('SELECT m, LENGTH(CONCAT(m.name, m.department)) AS namedeplength FROM Doctrine\Tests\Models\Company\CompanyManager m ORDER BY m.salary ASC')
                         ->getArrayResult();

        $this->assertEquals(4, count($result));
        $this->assertEquals(10, $result[0]['namedeplength']);
        $this->assertEquals(13, $result[1]['namedeplength']);
        $this->assertEquals(32, $result[2]['namedeplength']);
        $this->assertEquals(25, $result[3]['namedeplength']);
    }

    public function testFunctionLocate()
    {
        $dql = "SELECT m, LOCATE('e', LOWER(m.name)) AS loc, LOCATE('e', LOWER(m.name), 7) AS loc2 ".
               "FROM Doctrine\Tests\Models\Company\CompanyManager m ORDER BY m.salary ASC";

        $result = $this->_em->createQuery($dql)
                         ->getArrayResult();

        $this->assertEquals(4, count($result));
        $this->assertEquals(0, $result[0]['loc']);
        $this->assertEquals(2, $result[1]['loc']);
        $this->assertEquals(6, $result[2]['loc']);
        $this->assertEquals(0, $result[3]['loc']);
        $this->assertEquals(0, $result[0]['loc2']);
        $this->assertEquals(10, $result[1]['loc2']);
        $this->assertEquals(9, $result[2]['loc2']);
        $this->assertEquals(0, $result[3]['loc2']);
    }

    public function testFunctionLower()
    {
        $result = $this->_em->createQuery("SELECT m, LOWER(m.name) AS lowername FROM Doctrine\Tests\Models\Company\CompanyManager m ORDER BY m.salary ASC")
                         ->getArrayResult();

        $this->assertEquals(4, count($result));
        $this->assertEquals('roman b.', $result[0]['lowername']);
        $this->assertEquals('benjamin e.', $result[1]['lowername']);
        $this->assertEquals('guilherme b.', $result[2]['lowername']);
        $this->assertEquals('jonathan w.', $result[3]['lowername']);
    }

    public function testFunctionMod()
    {
        $result = $this->_em->createQuery("SELECT m, MOD(m.salary, 3500) AS amod FROM Doctrine\Tests\Models\Company\CompanyManager m ORDER BY m.salary ASC")
                         ->getArrayResult();

        $this->assertEquals(4, count($result));
        $this->assertEquals(2000, $result[0]['amod']);
        $this->assertEquals(500, $result[1]['amod']);
        $this->assertEquals(1000, $result[2]['amod']);
        $this->assertEquals(2000, $result[3]['amod']);
    }

    public function testFunctionSqrt()
    {
        $result = $this->_em->createQuery("SELECT m, SQRT(m.salary) AS sqrtsalary FROM Doctrine\Tests\Models\Company\CompanyManager m ORDER BY m.salary ASC")
                         ->getArrayResult();

        $this->assertEquals(4, count($result));
        $this->assertEquals(316, round($result[0]['sqrtsalary']));
        $this->assertEquals(447,  round($result[1]['sqrtsalary']));
        $this->assertEquals(632, round($result[2]['sqrtsalary']));
        $this->assertEquals(894, round($result[3]['sqrtsalary']));
    }

    public function testFunctionUpper()
    {
        $result = $this->_em->createQuery("SELECT m, UPPER(m.name) AS uppername FROM Doctrine\Tests\Models\Company\CompanyManager m ORDER BY m.salary ASC")
                         ->getArrayResult();

        $this->assertEquals(4, count($result));
        $this->assertEquals('ROMAN B.', $result[0]['uppername']);
        $this->assertEquals('BENJAMIN E.', $result[1]['uppername']);
        $this->assertEquals('GUILHERME B.', $result[2]['uppername']);
        $this->assertEquals('JONATHAN W.', $result[3]['uppername']);
    }

    public function testFunctionSubstring()
    {
        $dql = "SELECT m, SUBSTRING(m.name, 1, 3) AS str1, SUBSTRING(m.name, 5) AS str2 ".
                "FROM Doctrine\Tests\Models\Company\CompanyManager m ORDER BY m.name";

        $result = $this->_em->createQuery($dql)
                         ->getArrayResult();

        $this->assertEquals(4, count($result));
        $this->assertEquals('Ben', $result[0]['str1']);
        $this->assertEquals('Gui', $result[1]['str1']);
        $this->assertEquals('Jon', $result[2]['str1']);
        $this->assertEquals('Rom', $result[3]['str1']);

        $this->assertEquals('amin E.', $result[0]['str2']);
        $this->assertEquals('herme B.', $result[1]['str2']);
        $this->assertEquals('than W.', $result[2]['str2']);
        $this->assertEquals('n B.', $result[3]['str2']);
    }

    public function testFunctionTrim()
    {
        $dql = "SELECT m, TRIM(TRAILING '.' FROM m.name) AS str1, ".
               " TRIM(LEADING '.' FROM m.name) AS str2, TRIM(CONCAT(' ', CONCAT(m.name, ' '))) AS str3 ".
               "FROM Doctrine\Tests\Models\Company\CompanyManager m ORDER BY m.salary ASC";

        $result = $this->_em->createQuery($dql)->getArrayResult();

        $this->assertEquals(4, count($result));
        $this->assertEquals('Roman B', $result[0]['str1']);
        $this->assertEquals('Benjamin E', $result[1]['str1']);
        $this->assertEquals('Guilherme B', $result[2]['str1']);
        $this->assertEquals('Jonathan W', $result[3]['str1']);
        $this->assertEquals('Roman B.', $result[0]['str2']);
        $this->assertEquals('Benjamin E.', $result[1]['str2']);
        $this->assertEquals('Guilherme B.', $result[2]['str2']);
        $this->assertEquals('Jonathan W.', $result[3]['str2']);
        $this->assertEquals('Roman B.', $result[0]['str3']);
        $this->assertEquals('Benjamin E.', $result[1]['str3']);
        $this->assertEquals('Guilherme B.', $result[2]['str3']);
        $this->assertEquals('Jonathan W.', $result[3]['str3']);
    }

    public function testOperatorAdd()
    {
        $result = $this->_em->createQuery('SELECT m, m.salary+2500 AS add FROM Doctrine\Tests\Models\Company\CompanyManager m ORDER BY m.salary ASC')
                ->getResult();

        $this->assertEquals(4, count($result));
        $this->assertEquals(102500, $result[0]['add']);
        $this->assertEquals(202500, $result[1]['add']);
        $this->assertEquals(402500, $result[2]['add']);
        $this->assertEquals(802500, $result[3]['add']);
    }

    public function testOperatorSub()
    {
        $result = $this->_em->createQuery('SELECT m, m.salary-2500 AS sub FROM Doctrine\Tests\Models\Company\CompanyManager m ORDER BY m.salary ASC')
                ->getResult();

        $this->assertEquals(4, count($result));
        $this->assertEquals(97500, $result[0]['sub']);
        $this->assertEquals(197500, $result[1]['sub']);
        $this->assertEquals(397500, $result[2]['sub']);
        $this->assertEquals(797500, $result[3]['sub']);
    }

    public function testOperatorMultiply()
    {
        $result = $this->_em->createQuery('SELECT m, m.salary*2 AS op FROM Doctrine\Tests\Models\Company\CompanyManager m ORDER BY m.salary ASC')
                ->getResult();

        $this->assertEquals(4, count($result));
        $this->assertEquals(200000, $result[0]['op']);
        $this->assertEquals(400000, $result[1]['op']);
        $this->assertEquals(800000, $result[2]['op']);
        $this->assertEquals(1600000, $result[3]['op']);
    }

    /**
     * @group test
     */
    public function testOperatorDiv()
    {
        $result = $this->_em->createQuery('SELECT m, (m.salary/0.5) AS op FROM Doctrine\Tests\Models\Company\CompanyManager m ORDER BY m.salary ASC')
                ->getResult();

        $this->assertEquals(4, count($result));
        $this->assertEquals(200000, $result[0]['op']);
        $this->assertEquals(400000, $result[1]['op']);
        $this->assertEquals(800000, $result[2]['op']);
        $this->assertEquals(1600000, $result[3]['op']);
    }

    public function testConcatFunction()
    {
        $arg = $this->_em->createQuery('SELECT CONCAT(m.name, m.department) AS namedep FROM Doctrine\Tests\Models\Company\CompanyManager m order by namedep desc')
                ->getArrayResult();

        $this->assertEquals(4, count($arg));
        $this->assertEquals('Roman B.IT', $arg[0]['namedep']);
        $this->assertEquals('Jonathan W.Administration', $arg[1]['namedep']);
        $this->assertEquals('Guilherme B.Complaint Department', $arg[2]['namedep']);
        $this->assertEquals('Benjamin E.HR', $arg[3]['namedep']);
    }

    /**
     * @group DDC-1014
     */
    public function testDateDiff()
    {
        $query = $this->_em->createQuery("SELECT DATE_DIFF(CURRENT_TIMESTAMP(), DATE_ADD(CURRENT_TIMESTAMP(), 10, 'day')) AS diff FROM Doctrine\Tests\Models\Company\CompanyManager m");
        $arg = $query->getArrayResult();

        $this->assertEquals(-10, $arg[0]['diff'], "Should be roughly -10 (or -9)", 1);

        $query = $this->_em->createQuery("SELECT DATE_DIFF(DATE_ADD(CURRENT_TIMESTAMP(), 10, 'day'), CURRENT_TIMESTAMP()) AS diff FROM Doctrine\Tests\Models\Company\CompanyManager m");
        $arg = $query->getArrayResult();

        $this->assertEquals(10, $arg[0]['diff'], "Should be roughly 10 (or 9)", 1);
    }

    /**
     * @group DDC-1014
     * @group DDC-2938
     *
     * @dataProvider dateAddSubProvider
     */
    public function testDateAdd(string $unit, int $amount, int $expectedValue, int $delta = 0) : void
    {
        $query = sprintf(
            'SELECT CURRENT_TIMESTAMP() as now, DATE_ADD(CURRENT_TIMESTAMP(), %d, \'%s\') AS add FROM %s m',
            $amount,
            $unit,
            CompanyManager::class
        );

        $result = $this->_em->createQuery($query)
                            ->setMaxResults(1)
                            ->getSingleResult(AbstractQuery::HYDRATE_ARRAY);

        self::assertArrayHasKey('now', $result);
        self::assertArrayHasKey('add', $result);

        $diff = strtotime($result['add']) - strtotime($result['now']);

        self::assertEquals($expectedValue, $diff, '', $delta);
    }

    /**
     * @group DDC-1014
     * @group DDC-2938
     *
     * @dataProvider dateAddSubProvider
     */
    public function testDateSub(string $unit, int $amount, int $expectedValue, int $delta = 0) : void
    {
        $query = sprintf(
            'SELECT CURRENT_TIMESTAMP() as now, DATE_SUB(CURRENT_TIMESTAMP(), %d, \'%s\') AS sub FROM %s m',
            $amount,
            $unit,
            CompanyManager::class
        );

        $result = $this->_em->createQuery($query)
                            ->setMaxResults(1)
                            ->getSingleResult(AbstractQuery::HYDRATE_ARRAY);

        self::assertArrayHasKey('now', $result);
        self::assertArrayHasKey('sub', $result);

        $diff = strtotime($result['now']) - strtotime($result['sub']);

        self::assertEquals($expectedValue, $diff, '', $delta);
    }

    public function dateAddSubProvider() : array
    {
        $secondsInDay = 86400;

        return [
            'year'   => ['year', 1, 365 * $secondsInDay, 3 * $secondsInDay],
            'month'  => ['month', 1, 30 * $secondsInDay, 2 * $secondsInDay],
            'week'   => ['week', 1, 7 * $secondsInDay, $secondsInDay],
            'hour'   => ['hour', 1, 3600],
            'minute' => ['minute', 1, 60],
            'second' => ['second', 10, 10],
        ];
    }

    /**
     * @group DDC-1213
     */
    public function testBitOrComparison()
    {
        $dql    = 'SELECT m, ' .
                    'BIT_OR(4, 2) AS bit_or,' .
                    'BIT_OR( (m.salary/100000) , 2 ) AS salary_bit_or ' .
                    'FROM Doctrine\Tests\Models\Company\CompanyManager m ' .
                'ORDER BY ' .
                    'm.id ' ;
        $result = $this->_em->createQuery($dql)->getArrayResult();

        $this->assertEquals(4 | 2, $result[0]['bit_or']);
        $this->assertEquals(4 | 2, $result[1]['bit_or']);
        $this->assertEquals(4 | 2, $result[2]['bit_or']);
        $this->assertEquals(4 | 2, $result[3]['bit_or']);

        $this->assertEquals(($result[0][0]['salary']/100000) | 2, $result[0]['salary_bit_or']);
        $this->assertEquals(($result[1][0]['salary']/100000) | 2, $result[1]['salary_bit_or']);
        $this->assertEquals(($result[2][0]['salary']/100000) | 2, $result[2]['salary_bit_or']);
        $this->assertEquals(($result[3][0]['salary']/100000) | 2, $result[3]['salary_bit_or']);
    }

    /**
    * @group DDC-1213
    */
    public function testBitAndComparison()
    {
        $dql    = 'SELECT m, ' .
                    'BIT_AND(4, 2) AS bit_and,' .
                    'BIT_AND( (m.salary/100000) , 2 ) AS salary_bit_and ' .
                    'FROM Doctrine\Tests\Models\Company\CompanyManager m ' .
                'ORDER BY ' .
                    'm.id ' ;
        $result = $this->_em->createQuery($dql)->getArrayResult();

        $this->assertEquals(4 & 2, $result[0]['bit_and']);
        $this->assertEquals(4 & 2, $result[1]['bit_and']);
        $this->assertEquals(4 & 2, $result[2]['bit_and']);
        $this->assertEquals(4 & 2, $result[3]['bit_and']);

        $this->assertEquals(($result[0][0]['salary']/100000) & 2, $result[0]['salary_bit_and']);
        $this->assertEquals(($result[1][0]['salary']/100000) & 2, $result[1]['salary_bit_and']);
        $this->assertEquals(($result[2][0]['salary']/100000) & 2, $result[2]['salary_bit_and']);
        $this->assertEquals(($result[3][0]['salary']/100000) & 2, $result[3]['salary_bit_and']);
    }

    protected function generateFixture()
    {
        $manager1 = new CompanyManager();
        $manager1->setName('Roman B.');
        $manager1->setTitle('Foo');
        $manager1->setDepartment('IT');
        $manager1->setSalary(100000);

        $manager2 = new CompanyManager();
        $manager2->setName('Benjamin E.');
        $manager2->setTitle('Foo');
        $manager2->setDepartment('HR');
        $manager2->setSalary(200000);

        $manager3 = new CompanyManager();
        $manager3->setName('Guilherme B.');
        $manager3->setTitle('Foo');
        $manager3->setDepartment('Complaint Department');
        $manager3->setSalary(400000);

        $manager4 = new CompanyManager();
        $manager4->setName('Jonathan W.');
        $manager4->setTitle('Foo');
        $manager4->setDepartment('Administration');
        $manager4->setSalary(800000);

        $this->_em->persist($manager1);
        $this->_em->persist($manager2);
        $this->_em->persist($manager3);
        $this->_em->persist($manager4);
        $this->_em->flush();
        $this->_em->clear();
    }
}


1		<?php
2
3		namespace Doctrine\Tests\ORM\Functional;
4
5		use Doctrine\ORM\AbstractQuery;
6		use Doctrine\Tests\Models\Company\CompanyManager;
7		use Doctrine\Tests\OrmFunctionalTestCase;
8
9		/**
10		* Functional Query tests.
11		*
12		* @author robo
13		*/
14		class QueryDqlFunctionTest extends 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	View Code Duplication	public function testFunctionAbs()
65		{
66		$result = $this->_em->createQuery('SELECT m, ABS(m.salary * -1) AS abs FROM Doctrine\Tests\Models\Company\CompanyManager m ORDER BY m.salary ASC')
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	View Code Duplication	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 ORDER BY m.salary ASC')
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	View Code Duplication	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 ORDER BY m.salary ASC')
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	View Code Duplication	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 ORDER BY m.salary ASC";
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	View Code Duplication	public function testFunctionLower()
120		{
121		$result = $this->_em->createQuery("SELECT m, LOWER(m.name) AS lowername FROM Doctrine\Tests\Models\Company\CompanyManager m ORDER BY m.salary ASC")
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	View Code Duplication	public function testFunctionMod()
132		{
133		$result = $this->_em->createQuery("SELECT m, MOD(m.salary, 3500) AS amod FROM Doctrine\Tests\Models\Company\CompanyManager m ORDER BY m.salary ASC")
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 ORDER BY m.salary ASC")
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	View Code Duplication	public function testFunctionUpper()
156		{
157		$result = $this->_em->createQuery("SELECT m, UPPER(m.name) AS uppername FROM Doctrine\Tests\Models\Company\CompanyManager m ORDER BY m.salary ASC")
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	View Code Duplication	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 ORDER BY m.salary ASC";
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	View Code Duplication	public function testOperatorAdd()
211		{
212		$result = $this->_em->createQuery('SELECT m, m.salary+2500 AS add FROM Doctrine\Tests\Models\Company\CompanyManager m ORDER BY m.salary ASC')
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	View Code Duplication	public function testOperatorSub()
223		{
224		$result = $this->_em->createQuery('SELECT m, m.salary-2500 AS sub FROM Doctrine\Tests\Models\Company\CompanyManager m ORDER BY m.salary ASC')
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	View Code Duplication	public function testOperatorMultiply()
235		{
236		$result = $this->_em->createQuery('SELECT m, m.salary*2 AS op FROM Doctrine\Tests\Models\Company\CompanyManager m ORDER BY m.salary ASC')
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	View Code Duplication	public function testOperatorDiv()
250		{
251		$result = $this->_em->createQuery('SELECT m, (m.salary/0.5) AS op FROM Doctrine\Tests\Models\Company\CompanyManager m ORDER BY m.salary ASC')
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	View Code Duplication	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		* @group DDC-2938
292		*
293		* @dataProvider dateAddSubProvider
294		*/
295	View Code Duplication	public function testDateAdd(string $unit, int $amount, int $expectedValue, int $delta = 0) : void
296		{
297		$query = sprintf(
298		'SELECT CURRENT_TIMESTAMP() as now, DATE_ADD(CURRENT_TIMESTAMP(), %d, \'%s\') AS add FROM %s m',
299		$amount,
300		$unit,
301		CompanyManager::class
302		);
303
304		$result = $this->_em->createQuery($query)
305		->setMaxResults(1)
306		->getSingleResult(AbstractQuery::HYDRATE_ARRAY);
307
308		self::assertArrayHasKey('now', $result);
309		self::assertArrayHasKey('add', $result);
310
311		$diff = strtotime($result['add']) - strtotime($result['now']);
312
313		self::assertEquals($expectedValue, $diff, '', $delta);
314		}
315
316		/**
317		* @group DDC-1014
318		* @group DDC-2938
319		*
320		* @dataProvider dateAddSubProvider
321		*/
322	View Code Duplication	public function testDateSub(string $unit, int $amount, int $expectedValue, int $delta = 0) : void
323		{
324		$query = sprintf(
325		'SELECT CURRENT_TIMESTAMP() as now, DATE_SUB(CURRENT_TIMESTAMP(), %d, \'%s\') AS sub FROM %s m',
326		$amount,
327		$unit,
328		CompanyManager::class
329		);
330
331		$result = $this->_em->createQuery($query)
332		->setMaxResults(1)
333		->getSingleResult(AbstractQuery::HYDRATE_ARRAY);
334
335		self::assertArrayHasKey('now', $result);
336		self::assertArrayHasKey('sub', $result);
337
338		$diff = strtotime($result['now']) - strtotime($result['sub']);
339
340		self::assertEquals($expectedValue, $diff, '', $delta);
341		}
342
343		public function dateAddSubProvider() : array
344		{
345		$secondsInDay = 86400;
346
347		return [
348		'year' => ['year', 1, 365 * $secondsInDay, 3 * $secondsInDay],
349		'month' => ['month', 1, 30 * $secondsInDay, 2 * $secondsInDay],
350		'week' => ['week', 1, 7 * $secondsInDay, $secondsInDay],
351		'hour' => ['hour', 1, 3600],
352		'minute' => ['minute', 1, 60],
353		'second' => ['second', 10, 10],
354		];
355		}
356
357		/**
358		* @group DDC-1213
359		*/
360		public function testBitOrComparison()
361		{
362		$dql = 'SELECT m, ' .
363		'BIT_OR(4, 2) AS bit_or,' .
364		'BIT_OR( (m.salary/100000) , 2 ) AS salary_bit_or ' .
365		'FROM Doctrine\Tests\Models\Company\CompanyManager m ' .
366		'ORDER BY ' .
367		'm.id ' ;
368		$result = $this->_em->createQuery($dql)->getArrayResult();
369
370		$this->assertEquals(4 \| 2, $result[0]['bit_or']);
371		$this->assertEquals(4 \| 2, $result[1]['bit_or']);
372		$this->assertEquals(4 \| 2, $result[2]['bit_or']);
373		$this->assertEquals(4 \| 2, $result[3]['bit_or']);
374
375		$this->assertEquals(($result[0][0]['salary']/100000) \| 2, $result[0]['salary_bit_or']);
376		$this->assertEquals(($result[1][0]['salary']/100000) \| 2, $result[1]['salary_bit_or']);
377		$this->assertEquals(($result[2][0]['salary']/100000) \| 2, $result[2]['salary_bit_or']);
378		$this->assertEquals(($result[3][0]['salary']/100000) \| 2, $result[3]['salary_bit_or']);
379		}
380
381		/**
382		* @group DDC-1213
383		*/
384		public function testBitAndComparison()
385		{
386		$dql = 'SELECT m, ' .
387		'BIT_AND(4, 2) AS bit_and,' .
388		'BIT_AND( (m.salary/100000) , 2 ) AS salary_bit_and ' .
389		'FROM Doctrine\Tests\Models\Company\CompanyManager m ' .
390		'ORDER BY ' .
391		'm.id ' ;
392		$result = $this->_em->createQuery($dql)->getArrayResult();
393
394		$this->assertEquals(4 & 2, $result[0]['bit_and']);
395		$this->assertEquals(4 & 2, $result[1]['bit_and']);
396		$this->assertEquals(4 & 2, $result[2]['bit_and']);
397		$this->assertEquals(4 & 2, $result[3]['bit_and']);
398
399		$this->assertEquals(($result[0][0]['salary']/100000) & 2, $result[0]['salary_bit_and']);
400		$this->assertEquals(($result[1][0]['salary']/100000) & 2, $result[1]['salary_bit_and']);
401		$this->assertEquals(($result[2][0]['salary']/100000) & 2, $result[2]['salary_bit_and']);
402		$this->assertEquals(($result[3][0]['salary']/100000) & 2, $result[3]['salary_bit_and']);
403		}
404
405		protected function generateFixture()
406		{
407		$manager1 = new CompanyManager();
408		$manager1->setName('Roman B.');
409		$manager1->setTitle('Foo');
410		$manager1->setDepartment('IT');
411		$manager1->setSalary(100000);
412
413		$manager2 = new CompanyManager();
414		$manager2->setName('Benjamin E.');
415		$manager2->setTitle('Foo');
416		$manager2->setDepartment('HR');
417		$manager2->setSalary(200000);
418
419		$manager3 = new CompanyManager();
420		$manager3->setName('Guilherme B.');
421		$manager3->setTitle('Foo');
422		$manager3->setDepartment('Complaint Department');
423		$manager3->setSalary(400000);
424
425		$manager4 = new CompanyManager();
426		$manager4->setName('Jonathan W.');
427		$manager4->setTitle('Foo');
428		$manager4->setDepartment('Administration');
429		$manager4->setSalary(800000);
430
431		$this->_em->persist($manager1);
432		$this->_em->persist($manager2);
433		$this->_em->persist($manager3);
434		$this->_em->persist($manager4);
435		$this->_em->flush();
436		$this->_em->clear();
437		}
438		}
439

doctrine / orm

Push — master ( ed86ee...a82f6c )

QueryDqlFunctionTest::dateAddSubProvider() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like