QueryDqlFunctionTest::testFunctionTrim() - Code Metrics - Inspection of "[GH-4641] IDENTITY() field type conversion" - doctrine/orm - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Pull Request — 2.8.x (#8025)

by Benjamin

created 2020-02-16 23:35 UTC

QueryDqlFunctionTest::testFunctionTrim() A

↳ Parent: QueryDqlFunctionTest

Complexity

Conditions	1
Paths	1

Size

Total Lines	21
Code Lines	17

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	1
eloc	17
nc	1
nop	0
dl	0
loc	21
rs	9.7
c	0
b	0
f	0

<?php

namespace Doctrine\Tests\ORM\Functional;

use Doctrine\ORM\AbstractQuery;
use Doctrine\Tests\Models\Company\CompanyCar;
use Doctrine\Tests\Models\Company\CompanyManager;
use Doctrine\Tests\OrmFunctionalTestCase;
use function sprintf;

/**
 * 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 $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);

        self::assertEquals(
            (new \DateTimeImmutable($result['now']))->modify(sprintf('+%d %s', $amount, $unit)),
            new \DateTimeImmutable($result['add']),
            '',
            $delta
        );
    }

    /**
     * @group DDC-1014
     * @group DDC-2938
     *
     * @dataProvider dateAddSubProvider
     */
    public function testDateSub(string $unit, int $amount, 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);

        self::assertEquals(
            (new \DateTimeImmutable($result['now']))->modify(sprintf('-%d %s', $amount, $unit)),
            new \DateTimeImmutable($result['sub']),
            '',
            $delta
        );
    }

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

        return [
            'year'   => ['year', 1, $secondsInDay],
            'month'  => ['month', 1, $secondsInDay],
            'week'   => ['week', 1, $secondsInDay],
            'day'    => ['day', 2, $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']);
    }

    public function testIdentity()
    {
        $query = $this->_em->createQuery("SELECT IDENTITY(m.car) as id FROM Doctrine\Tests\Models\Company\CompanyManager m WHERE m.name = 'Roman B.'");

        $row = $query->getSingleResult();

        $this->assertIsInt($row['id']);
    }

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

        $car = new CompanyCar('wroom');
        $manager1->setCar($car);

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

doctrine / orm

Pull Request — 2.8.x (#8025)

QueryDqlFunctionTest::testFunctionTrim() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like