NativeQueryBuilderHelperTest::testSqlWithPaginationOrderBy() - Code Metrics - Inspection of "php-cs-fixer" - micayael/native-query-from-file-builder-bundle - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( 7deed2...ef8ff7 )

by Juan

created 2018-08-09 13:59 UTC

testSqlWithPaginationOrderBy() A

↳ Parent: NativeQueryBuilderHelperTest

Complexity

Conditions	1
Paths	1

Size

Total Lines	15
Code Lines	8

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	1
eloc	8
nc	1
nop	0
dl	0
loc	15
rs	10
c	0
b	0
f	0

<?php

namespace Micayael\NativeQueryFromFileBuilderBundle\Tests\Helper;

use Micayael\NativeQueryFromFileBuilderBundle\Exception\NonExistentQueryDirectoryException;
use Micayael\NativeQueryFromFileBuilderBundle\Exception\NonExistentQueryFileException;
use Micayael\NativeQueryFromFileBuilderBundle\Exception\NonExistentQueryKeyException;
use Micayael\NativeQueryFromFileBuilderBundle\Helper\NativeQueryBuilderHelper;
use PHPUnit\Framework\TestCase;
use Symfony\Component\EventDispatcher\EventDispatcherInterface;

class NativeQueryBuilderHelperTest extends TestCase
{
    /**
     * @var NativeQueryBuilderHelper
     */
    private $helper;

    protected function setUp()
    {
        $eventDispatcher = $this->createMock(EventDispatcherInterface::class);
        $cache = null;
        $config = [
            'sql_queries_dir' => __DIR__.'/../queries',
            'file_extension' => 'yaml',
            'debug' => true,
        ];

        $this->helper = new NativeQueryBuilderHelper($eventDispatcher, $cache, $config);
    }

    public function testNonExistentQueryDirectoryException()
    {
        $eventDispatcher = $this->createMock(EventDispatcherInterface::class);
        $cache = null;
        $config = [
            'sql_queries_dir' => __DIR__.'/../non_existent',
            'file_extension' => 'yaml',
            'debug' => true,
        ];

        $helper = new NativeQueryBuilderHelper($eventDispatcher, $cache, $config);

        $params = [];

        $this->expectException(NonExistentQueryDirectoryException::class);
        $this->expectExceptionMessageRegExp('/El directorio configurado ".+" no existe. Favor verifique la configuración del bundle "native_query_from_file_builder.sql_queries_dir"/');

        $helper->getSqlFromYamlKey('clients:product', $params);
    }

    public function testNonExistentQueryFileException()
    {
        $params = [];

        $this->expectException(NonExistentQueryFileException::class);
        $this->expectExceptionMessageRegExp('/El archivo de queries solicitado ".+" no existe/');

        $this->helper->getSqlFromYamlKey('non_existent:client', $params);
    }

    public function testNonExistentQueryKey()
    {
        $params = [];

        $this->expectException(NonExistentQueryKeyException::class);
        $this->expectExceptionMessageRegExp('/El queries solicitado ".+" no existe/');

        $this->helper->getSqlFromYamlKey('clients:non_existent', $params);
    }

    public function testSimpleSql()
    {
        $params = [];

        $sql = $this->helper->getSqlFromYamlKey('clients:clients', $params);

        $this->assertEquals('SELECT * FROM clients', $sql);
    }

    //----------------------------------------------------------------------------------------------
    // Required Params
    //----------------------------------------------------------------------------------------------

    public function testSqlWithRequiredParams()
    {
        $params = [
            'slug' => 'jhon-doe',
        ];

        $sql = $this->helper->getSqlFromYamlKey('clients:client_by_slug', $params);

        $this->assertEquals('SELECT * FROM clients WHERE slug = :slug', $sql);
    }

    //----------------------------------------------------------------------------------------------
    // Snippets (optionals & required)
    //----------------------------------------------------------------------------------------------

    public function testSqlWithOptionalFiltersNotUsingFilters()
    {
        $params = [];

        $sql = $this->helper->getSqlFromYamlKey('clients:clients_optional_filters.base', $params);

        $this->assertEquals('SELECT * FROM clients c ORDER BY c.id DESC', $sql);
    }

    public function testSqlWithOptionalFilterName()
    {
        $params = [
            'firstname' => 'Jhon',
        ];

        $sql = $this->helper->getSqlFromYamlKey('clients:clients_optional_filters.base', $params);

        $this->assertEquals('SELECT * FROM clients c WHERE (firstname = :firstname) ORDER BY c.id DESC', $sql);
    }

    public function testSqlWithOptionalFiltersNameAndLastname()
    {
        $params = [
            'firstname' => 'Jhon',
            'lastname' => 'Doe',
        ];

        $sql = $this->helper->getSqlFromYamlKey('clients:clients_optional_filters.base', $params);

        $this->assertEquals('SELECT * FROM clients c WHERE (firstname = :firstname) AND (lastname = :lastname) ORDER BY c.id DESC', $sql);
    }

    public function testSqlWithOptionalFiltersAndWhereIncluded()
    {
        $params = [
            'year' => 1983,
            'firstname' => 'Jhon',
            'lastname' => 'Doe',
        ];

        $sql = $this->helper->getSqlFromYamlKey('clients:clients_optional_filters_and_where_included.base', $params);

        $this->assertEquals('SELECT * FROM clients c WHERE YEAR(birthday) > :year AND (firstname = :firstname) AND (lastname = :lastname)', $sql);
    }

    public function testSqlWithRequiredKeysAndNoFilters()
    {
        $sql = $this->helper->getSqlFromYamlKey('clients:clients_required_key.base');

        $this->assertEquals('SELECT c.id, c.firstname as name, c.lastname FROM clients c', $sql);
    }

    public function testSqlWithRequiredKeyAndFilters()
    {
        $params = [
            'firstname' => 'Jhon',
        ];

        $sql = $this->helper->getSqlFromYamlKey('clients:clients_required_key.base', $params);

        $this->assertEquals('SELECT c.id, c.firstname as name, c.lastname FROM clients c WHERE (c.firstname = :firstname)', $sql);
    }

    public function testSqlWithRequiredKeysAndFilters()
    {
        $params = [
            'firstname' => 'Jhon',
            'lastname' => 'Doe',
        ];

        $sql = $this->helper->getSqlFromYamlKey('clients:clients_required_keys.base', $params);

        $this->assertEquals('SELECT c.id, c.firstname as name, c.lastname, YEAR(c.birthday) as year FROM clients c WHERE (c.firstname = :firstname) AND (c.lastname = :lastname)', $sql);
    }

    //----------------------------------------------------------------------------------------------
    // Special filters
    //----------------------------------------------------------------------------------------------

    public function testSqlWithWhereIn()
    {
        $params = [
            'firstnames' => ['Jhon', 'Mary', 'Steven'],
        ];

        $sql = $this->helper->getSqlFromYamlKey('clients:clients_special_filters.base', $params);

        $this->assertEquals('SELECT * FROM clients c WHERE (firstname IN(:firstnames_0,:firstnames_1,:firstnames_2))', $sql);
    }

    //----------------------------------------------------------------------------------------------
    // Subqueries - Multipart Query
    //----------------------------------------------------------------------------------------------

    public function testSqlWithAnySubquery()
    {
        $params = [
            'firstname' => 'Jhon',
            'date' => '2018-01-01',
        ];

        $sql = $this->helper->getSqlFromYamlKey('clients:clients_any_subquery.base', $params);

        $this->assertEquals('SELECT c.firstname, c.lastname FROM clients c WHERE c.sold > ANY(SELECT s.amount FROM sale s WHERE (s.date > :date) ORDER BY s.amount DESC LIMIT 10) AND (c.firstname = :firstname)', $sql);
    }

    //----------------------------------------------------------------------------------------------
    // Pagination
    //----------------------------------------------------------------------------------------------

    public function testSqlWithPagination()
    {
        $params = [
            'name' => 'Jhon',
            'min_date' => '2018-01-01',
        ];

        $sql = $this->helper->getSqlFromYamlKey('clients:clients_pagination.count', $params);

        $this->assertEquals('SELECT count(1) FROM clients c JOIN sales s on c.id = s.client_id WHERE (c.date >= :min_date) AND (c.firstname like :name)', $sql);

        $sql = $this->helper->getSqlFromYamlKey('clients:clients_pagination.base', $params);

        $this->assertEquals('SELECT * FROM clients c JOIN sales s on c.id = s.client_id WHERE (c.date >= :min_date) AND (c.firstname like :name) ORDER BY s.date DESC', $sql);
    }

    public function testSqlWithPaginationOrderBy()
    {
        $params = [
            'name' => 'Jhon',
            'min_date' => '2018-01-01',
            'orderby' => 'c.date desc, c.id asc',
        ];

        $sql = $this->helper->getSqlFromYamlKey('clients:clients_pagination_orderby.count', $params);

        $this->assertEquals('SELECT count(1) FROM clients c JOIN sales s on c.id = s.client_id WHERE (c.date >= :min_date) AND (c.firstname like :name)', $sql);

        $sql = $this->helper->getSqlFromYamlKey('clients:clients_pagination_orderby.base', $params);

        $this->assertEquals('SELECT * FROM clients c JOIN sales s on c.id = s.client_id WHERE (c.date >= :min_date) AND (c.firstname like :name) ORDER BY c.date desc, c.id asc', $sql);
    }

    public function testSqlWithPaginationCustomOrderBy()
    {
        $params = [
            'name' => 'Jhon',
            'min_date' => '2018-01-01',
            'orderby' => 'c.date desc, c.id asc',
        ];

        $sql = $this->helper->getSqlFromYamlKey('clients:clients_pagination_orderby.count', $params);

        $this->assertEquals('SELECT count(1) FROM clients c JOIN sales s on c.id = s.client_id WHERE (c.date >= :min_date) AND (c.firstname like :name)', $sql);

        $sql = $this->helper->getSqlFromYamlKey('clients:clients_pagination_orderby.base', $params);

        $this->assertEquals('SELECT * FROM clients c JOIN sales s on c.id = s.client_id WHERE (c.date >= :min_date) AND (c.firstname like :name) ORDER BY c.date desc, c.id asc', $sql);
    }
}


1			<?php
2
3			namespace Micayael\NativeQueryFromFileBuilderBundle\Tests\Helper;
4
5			use Micayael\NativeQueryFromFileBuilderBundle\Exception\NonExistentQueryDirectoryException;
6			use Micayael\NativeQueryFromFileBuilderBundle\Exception\NonExistentQueryFileException;
7			use Micayael\NativeQueryFromFileBuilderBundle\Exception\NonExistentQueryKeyException;
8			use Micayael\NativeQueryFromFileBuilderBundle\Helper\NativeQueryBuilderHelper;
9			use PHPUnit\Framework\TestCase;
10			use Symfony\Component\EventDispatcher\EventDispatcherInterface;
11
12			class NativeQueryBuilderHelperTest extends TestCase
13			{
14			/**
15			* @var NativeQueryBuilderHelper
16			*/
17			private $helper;
18
19			protected function setUp()
20			{
21			$eventDispatcher = $this->createMock(EventDispatcherInterface::class);
22			$cache = null;
23			$config = [
24			'sql_queries_dir' => __DIR__.'/../queries',
25			'file_extension' => 'yaml',
26			'debug' => true,
27			];
28
29			$this->helper = new NativeQueryBuilderHelper($eventDispatcher, $cache, $config);
30			}
31
32			public function testNonExistentQueryDirectoryException()
33			{
34			$eventDispatcher = $this->createMock(EventDispatcherInterface::class);
35			$cache = null;
36			$config = [
37			'sql_queries_dir' => __DIR__.'/../non_existent',
38			'file_extension' => 'yaml',
39			'debug' => true,
40			];
41
42			$helper = new NativeQueryBuilderHelper($eventDispatcher, $cache, $config);
43
44			$params = [];
45
46			$this->expectException(NonExistentQueryDirectoryException::class);
47			$this->expectExceptionMessageRegExp('/El directorio configurado ".+" no existe. Favor verifique la configuración del bundle "native_query_from_file_builder.sql_queries_dir"/');
48
49			$helper->getSqlFromYamlKey('clients:product', $params);
50			}
51
52			public function testNonExistentQueryFileException()
53			{
54			$params = [];
55
56			$this->expectException(NonExistentQueryFileException::class);
57			$this->expectExceptionMessageRegExp('/El archivo de queries solicitado ".+" no existe/');
58
59			$this->helper->getSqlFromYamlKey('non_existent:client', $params);
60			}
61
62			public function testNonExistentQueryKey()
63			{
64			$params = [];
65
66			$this->expectException(NonExistentQueryKeyException::class);
67			$this->expectExceptionMessageRegExp('/El queries solicitado ".+" no existe/');
68
69			$this->helper->getSqlFromYamlKey('clients:non_existent', $params);
70			}
71
72			public function testSimpleSql()
73			{
74			$params = [];
75
76			$sql = $this->helper->getSqlFromYamlKey('clients:clients', $params);
77
78			$this->assertEquals('SELECT * FROM clients', $sql);
79			}
80
81			//----------------------------------------------------------------------------------------------
82			// Required Params
83			//----------------------------------------------------------------------------------------------
84
85			public function testSqlWithRequiredParams()
86			{
87			$params = [
88			'slug' => 'jhon-doe',
89			];
90
91			$sql = $this->helper->getSqlFromYamlKey('clients:client_by_slug', $params);
92
93			$this->assertEquals('SELECT * FROM clients WHERE slug = :slug', $sql);
94			}
95
96			//----------------------------------------------------------------------------------------------
97			// Snippets (optionals & required)
98			//----------------------------------------------------------------------------------------------
99
100			public function testSqlWithOptionalFiltersNotUsingFilters()
101			{
102			$params = [];
103
104			$sql = $this->helper->getSqlFromYamlKey('clients:clients_optional_filters.base', $params);
105
106			$this->assertEquals('SELECT * FROM clients c ORDER BY c.id DESC', $sql);
107			}
108
109			public function testSqlWithOptionalFilterName()
110			{
111			$params = [
112			'firstname' => 'Jhon',
113			];
114
115			$sql = $this->helper->getSqlFromYamlKey('clients:clients_optional_filters.base', $params);
116
117			$this->assertEquals('SELECT * FROM clients c WHERE (firstname = :firstname) ORDER BY c.id DESC', $sql);
118			}
119
120			public function testSqlWithOptionalFiltersNameAndLastname()
121			{
122			$params = [
123			'firstname' => 'Jhon',
124			'lastname' => 'Doe',
125			];
126
127			$sql = $this->helper->getSqlFromYamlKey('clients:clients_optional_filters.base', $params);
128
129			$this->assertEquals('SELECT * FROM clients c WHERE (firstname = :firstname) AND (lastname = :lastname) ORDER BY c.id DESC', $sql);
130			}
131
132			public function testSqlWithOptionalFiltersAndWhereIncluded()
133			{
134			$params = [
135			'year' => 1983,
136			'firstname' => 'Jhon',
137			'lastname' => 'Doe',
138			];
139
140			$sql = $this->helper->getSqlFromYamlKey('clients:clients_optional_filters_and_where_included.base', $params);
141
142			$this->assertEquals('SELECT * FROM clients c WHERE YEAR(birthday) > :year AND (firstname = :firstname) AND (lastname = :lastname)', $sql);
143			}
144
145			public function testSqlWithRequiredKeysAndNoFilters()
146			{
147			$sql = $this->helper->getSqlFromYamlKey('clients:clients_required_key.base');
148
149			$this->assertEquals('SELECT c.id, c.firstname as name, c.lastname FROM clients c', $sql);
150			}
151
152			public function testSqlWithRequiredKeyAndFilters()
153			{
154			$params = [
155			'firstname' => 'Jhon',
156			];
157
158			$sql = $this->helper->getSqlFromYamlKey('clients:clients_required_key.base', $params);
159
160			$this->assertEquals('SELECT c.id, c.firstname as name, c.lastname FROM clients c WHERE (c.firstname = :firstname)', $sql);
161			}
162
163			public function testSqlWithRequiredKeysAndFilters()
164			{
165			$params = [
166			'firstname' => 'Jhon',
167			'lastname' => 'Doe',
168			];
169
170			$sql = $this->helper->getSqlFromYamlKey('clients:clients_required_keys.base', $params);
171
172			$this->assertEquals('SELECT c.id, c.firstname as name, c.lastname, YEAR(c.birthday) as year FROM clients c WHERE (c.firstname = :firstname) AND (c.lastname = :lastname)', $sql);
173			}
174
175			//----------------------------------------------------------------------------------------------
176			// Special filters
177			//----------------------------------------------------------------------------------------------
178
179			public function testSqlWithWhereIn()
180			{
181			$params = [
182			'firstnames' => ['Jhon', 'Mary', 'Steven'],
183			];
184
185			$sql = $this->helper->getSqlFromYamlKey('clients:clients_special_filters.base', $params);
186
187			$this->assertEquals('SELECT * FROM clients c WHERE (firstname IN(:firstnames_0,:firstnames_1,:firstnames_2))', $sql);
188			}
189
190			//----------------------------------------------------------------------------------------------
191			// Subqueries - Multipart Query
192			//----------------------------------------------------------------------------------------------
193
194			public function testSqlWithAnySubquery()
195			{
196			$params = [
197			'firstname' => 'Jhon',
198			'date' => '2018-01-01',
199			];
200
201			$sql = $this->helper->getSqlFromYamlKey('clients:clients_any_subquery.base', $params);
202
203			$this->assertEquals('SELECT c.firstname, c.lastname FROM clients c WHERE c.sold > ANY(SELECT s.amount FROM sale s WHERE (s.date > :date) ORDER BY s.amount DESC LIMIT 10) AND (c.firstname = :firstname)', $sql);
204			}
205
206			//----------------------------------------------------------------------------------------------
207			// Pagination
208			//----------------------------------------------------------------------------------------------
209
210			public function testSqlWithPagination()
211			{
212			$params = [
213			'name' => 'Jhon',
214			'min_date' => '2018-01-01',
215			];
216
217			$sql = $this->helper->getSqlFromYamlKey('clients:clients_pagination.count', $params);
218
219			$this->assertEquals('SELECT count(1) FROM clients c JOIN sales s on c.id = s.client_id WHERE (c.date >= :min_date) AND (c.firstname like :name)', $sql);
220
221			$sql = $this->helper->getSqlFromYamlKey('clients:clients_pagination.base', $params);
222
223			$this->assertEquals('SELECT * FROM clients c JOIN sales s on c.id = s.client_id WHERE (c.date >= :min_date) AND (c.firstname like :name) ORDER BY s.date DESC', $sql);
224			}
225
226			public function testSqlWithPaginationOrderBy()
227			{
228			$params = [
229			'name' => 'Jhon',
230			'min_date' => '2018-01-01',
231			'orderby' => 'c.date desc, c.id asc',
232			];
233
234			$sql = $this->helper->getSqlFromYamlKey('clients:clients_pagination_orderby.count', $params);
235
236			$this->assertEquals('SELECT count(1) FROM clients c JOIN sales s on c.id = s.client_id WHERE (c.date >= :min_date) AND (c.firstname like :name)', $sql);
237
238			$sql = $this->helper->getSqlFromYamlKey('clients:clients_pagination_orderby.base', $params);
239
240			$this->assertEquals('SELECT * FROM clients c JOIN sales s on c.id = s.client_id WHERE (c.date >= :min_date) AND (c.firstname like :name) ORDER BY c.date desc, c.id asc', $sql);
241			}
242
243			public function testSqlWithPaginationCustomOrderBy()
244			{
245			$params = [
246			'name' => 'Jhon',
247			'min_date' => '2018-01-01',
248			'orderby' => 'c.date desc, c.id asc',
249			];
250
251			$sql = $this->helper->getSqlFromYamlKey('clients:clients_pagination_orderby.count', $params);
252
253			$this->assertEquals('SELECT count(1) FROM clients c JOIN sales s on c.id = s.client_id WHERE (c.date >= :min_date) AND (c.firstname like :name)', $sql);
254
255			$sql = $this->helper->getSqlFromYamlKey('clients:clients_pagination_orderby.base', $params);
256
257			$this->assertEquals('SELECT * FROM clients c JOIN sales s on c.id = s.client_id WHERE (c.date >= :min_date) AND (c.firstname like :name) ORDER BY c.date desc, c.id asc', $sql);
258			}
259			}
260

micayael / native-query-from-file-builder-bundle

Push — master ( 7deed2...ef8ff7 )

testSqlWithPaginationOrderBy() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like