NativeQueryBuilderHelperTest::testSimpleSql() - Code Metrics - micayael/native-query-from-file-builder-bundle - Measure and Improve Code Quality continuously with Scrutinizer

NativeQueryBuilderHelperTest::testSimpleSql() A
last analyzed 2023-06-09 15:50 UTC

↳ Parent: NativeQueryBuilderHelperTest

Complexity

Conditions	1
Paths	1

Size

Total Lines	5
Code Lines	2

Duplication

Lines	0
Ratio	0 %

Importance

Changes	2
Bugs	0	Features	0

Metric	Value
cc	1
eloc	2
c	2
b	0
f	0
nc	1
nop	0
dl	0
loc	5
rs	10

<?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(): void
    {
        $eventDispatcher = $this->createMock(EventDispatcherInterface::class);
        $cache = null;
        $config = [
            'sql_queries_dir' => __DIR__.'/../queries',
            'file_extension' => 'yaml',
            'cache_sql' => false,
        ];

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

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

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

        $params = [];

        $this->expectException(NonExistentQueryDirectoryException::class);
        $this->expectExceptionMessageMatches('/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->expectExceptionMessageMatches('/El archivo de queries solicitado ".+" no existe/');

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

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

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

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

    public function testSimpleSql()
    {
        $sql = $this->helper->getSqlFromYamlKey('clients:clients');

        $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',
//            'order' => '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(): void
20			{
21			$eventDispatcher = $this->createMock(EventDispatcherInterface::class);
22			$cache = null;
23			$config = [
24			'sql_queries_dir' => __DIR__.'/../queries',
25			'file_extension' => 'yaml',
26			'cache_sql' => false,
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_existente',
38			'file_extension' => 'yaml',
39			'cache_sql' => false,
40			];
41
42			$helper = new NativeQueryBuilderHelper($eventDispatcher, $cache, $config);
43
44			$params = [];
45
46			$this->expectException(NonExistentQueryDirectoryException::class);
47			$this->expectExceptionMessageMatches('/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->expectExceptionMessageMatches('/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->expectExceptionMessageMatches('/El queries solicitado ".+" no existe/');
68
69			$this->helper->getSqlFromYamlKey('clients:non_existent', $params);
70			}
71
72			public function testSimpleSql()
73			{
74			$sql = $this->helper->getSqlFromYamlKey('clients:clients');
75
76			$this->assertEquals('SELECT * FROM clients', $sql);
77			}
78
79			// ----------------------------------------------------------------------------------------------
80			// Required Params
81			// ----------------------------------------------------------------------------------------------
82
83			public function testSqlWithRequiredParams()
84			{
85			$params = [
86			'slug' => 'jhon-doe',
87			];
88
89			$sql = $this->helper->getSqlFromYamlKey('clients:client_by_slug', $params);
90
91			$this->assertEquals('SELECT * FROM clients WHERE slug = :slug', $sql);
92			}
93
94			// ----------------------------------------------------------------------------------------------
95			// Snippets (optionals & required)
96			// ----------------------------------------------------------------------------------------------
97
98			public function testSqlWithOptionalFiltersNotUsingFilters()
99			{
100			$params = [];
101
102			$sql = $this->helper->getSqlFromYamlKey('clients:clients_optional_filters.base', $params);
103
104			$this->assertEquals('SELECT * FROM clients c ORDER BY c.id DESC', $sql);
105			}
106
107			public function testSqlWithOptionalFilterName()
108			{
109			$params = [
110			'firstname' => 'Jhon',
111			];
112
113			$sql = $this->helper->getSqlFromYamlKey('clients:clients_optional_filters.base', $params);
114
115			$this->assertEquals('SELECT * FROM clients c WHERE (firstname = :firstname) ORDER BY c.id DESC', $sql);
116			}
117
118			public function testSqlWithOptionalFiltersNameAndLastname()
119			{
120			$params = [
121			'firstname' => 'Jhon',
122			'lastname' => 'Doe',
123			];
124
125			$sql = $this->helper->getSqlFromYamlKey('clients:clients_optional_filters.base', $params);
126
127			$this->assertEquals('SELECT * FROM clients c WHERE (firstname = :firstname) AND (lastname = :lastname) ORDER BY c.id DESC', $sql);
128			}
129
130			public function testSqlWithOptionalFiltersAndWhereIncluded()
131			{
132			$params = [
133			'year' => 1983,
134			'firstname' => 'Jhon',
135			'lastname' => 'Doe',
136			];
137
138			$sql = $this->helper->getSqlFromYamlKey('clients:clients_optional_filters_and_where_included.base', $params);
139
140			$this->assertEquals('SELECT * FROM clients c WHERE YEAR(birthday) > :year AND (firstname = :firstname) AND (lastname = :lastname)', $sql);
141			}
142
143			public function testSqlWithRequiredKeysAndNoFilters()
144			{
145			$sql = $this->helper->getSqlFromYamlKey('clients:clients_required_key.base');
146
147			$this->assertEquals('SELECT c.id, c.firstname as name, c.lastname FROM clients c', $sql);
148			}
149
150			public function testSqlWithRequiredKeyAndFilters()
151			{
152			$params = [
153			'firstname' => 'Jhon',
154			];
155
156			$sql = $this->helper->getSqlFromYamlKey('clients:clients_required_key.base', $params);
157
158			$this->assertEquals('SELECT c.id, c.firstname as name, c.lastname FROM clients c WHERE (c.firstname = :firstname)', $sql);
159			}
160
161			public function testSqlWithRequiredKeysAndFilters()
162			{
163			$params = [
164			'firstname' => 'Jhon',
165			'lastname' => 'Doe',
166			];
167
168			$sql = $this->helper->getSqlFromYamlKey('clients:clients_required_keys.base', $params);
169
170			$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);
171			}
172
173			// ----------------------------------------------------------------------------------------------
174			// Special filters
175			// ----------------------------------------------------------------------------------------------
176
177			public function testSqlWithWhereIn()
178			{
179			$params = [
180			'firstnames' => ['Jhon', 'Mary', 'Steven'],
181			];
182
183			$sql = $this->helper->getSqlFromYamlKey('clients:clients_special_filters.base', $params);
184
185			$this->assertEquals('SELECT * FROM clients c WHERE (firstname IN(:firstnames_0,:firstnames_1,:firstnames_2))', $sql);
186			}
187
188			// ----------------------------------------------------------------------------------------------
189			// Subqueries - Multipart Query
190			// ----------------------------------------------------------------------------------------------
191
192			public function testSqlWithAnySubquery()
193			{
194			$params = [
195			'firstname' => 'Jhon',
196			'date' => '2018-01-01',
197			];
198
199			$sql = $this->helper->getSqlFromYamlKey('clients:clients_any_subquery.base', $params);
200
201			$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);
202			}
203
204			// ----------------------------------------------------------------------------------------------
205			// Pagination
206			// ----------------------------------------------------------------------------------------------
207
208			public function testSqlWithPagination()
209			{
210			$params = [
211			'name' => 'Jhon',
212			'min_date' => '2018-01-01',
213			];
214
215			$sql = $this->helper->getSqlFromYamlKey('clients:clients_pagination.count', $params);
216
217			$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);
218
219			$sql = $this->helper->getSqlFromYamlKey('clients:clients_pagination.base', $params);
220
221			$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);
222			}
223
224			public function testSqlWithPaginationOrderBy()
225			{
226			$params = [
227			'name' => 'Jhon',
228			'min_date' => '2018-01-01',
229			'orderby' => 'c.date desc, c.id asc',
230			];
231
232			$sql = $this->helper->getSqlFromYamlKey('clients:clients_pagination_orderby.count', $params);
233
234			$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);
235
236			$sql = $this->helper->getSqlFromYamlKey('clients:clients_pagination_orderby.base', $params);
237
238			$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);
239			}
240
241			// public function testSqlWithPaginationCustomOrderBy()
242			// {
243			// $params = [
244			// 'name' => 'Jhon',
245			// 'min_date' => '2018-01-01',
246			// 'order' => 'c.date desc, c.id asc',
247			// ];
248			//
249			// $sql = $this->helper->getSqlFromYamlKey('clients:clients_pagination_orderby.count', $params);
250			//
251			// $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);
252			//
253			// $sql = $this->helper->getSqlFromYamlKey('clients:clients_pagination_orderby.base', $params);
254			//
255			// $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);
256			// }
257			}
258

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

NativeQueryBuilderHelperTest::testSimpleSql() A last analyzed 2023-06-09 15:50 UTC

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like

NativeQueryBuilderHelperTest::testSimpleSql() A
last analyzed 2023-06-09 15:50 UTC