SelectTest::testToStringWithLeftJoin() - Code Metrics - abterphp/qb - Measure and Improve Code Quality continuously with Scrutinizer

SelectTest::testToStringWithLeftJoin() A
last analyzed 2021-07-05 23:35 UTC

↳ Parent: SelectTest

Complexity

Conditions	1
Paths	1

Size

Total Lines	13
Code Lines	8

Duplication

Lines	0
Ratio	0 %

Importance

Changes

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

<?php

declare(strict_types=1);

namespace QB\Generic\Statement;

use PDO;
use PHPUnit\Framework\TestCase;
use QB\Generic\Clause\Column;
use QB\Generic\Clause\IJoin;
use QB\Generic\Clause\Join;
use QB\Generic\Clause\Table;
use QB\Generic\Expr\Expr;
use RuntimeException;

/**
 * @SuppressWarnings(PHPMD.TooManyPublicMethods)
 */
class SelectTest extends TestCase
{
    /**
     * @suppress PhanNoopCast
     */
    public function testToStringThrowsAnExceptionIfNotInitialized()
    {
        $this->expectException(RuntimeException::class);

        (string)$this->getSut();
    }

    public function testToStringConstant()
    {
        $sql = (string)$this->getSut()->columns('1');

        $this->assertSame('SELECT 1', $sql);
    }

    public function testToStringExpressions()
    {
        $sql = (string)$this->getSut()->columns(new Column('COUNT(foo)', 'foo_count'));

        $this->assertSame('SELECT COUNT(foo) AS foo_count', $sql);
    }

    public function testToStringFromTwoTables()
    {
        $sql = (string)$this->getSut('foo', 'bar');

        $expectedSql = "SELECT *\nFROM foo, bar";

        $this->assertSame($expectedSql, $sql);
    }

    public function testToStringFromTwoTablesWithOneAlias()
    {
        $sql = (string)$this->getSut(new Table('foo', 'f'), 'bar');

        $expectedSql = "SELECT *\nFROM foo AS f, bar";

        $this->assertSame($expectedSql, $sql);
    }

    public function testToStringFromTwoTablesWithAliases()
    {
        $sql = (string)$this->getSut(new Table('foo', 'f'), new Table('bar', 'b'));

        $expectedSql = "SELECT *\nFROM foo AS f, bar AS b";

        $this->assertSame($expectedSql, $sql);
    }

    public function testWithInnerJoin()
    {
        $sql = (string)$this->getSut('foo')
            ->innerJoin('baz', 'foo.id = baz.foo_id');

        $parts   = [];
        $parts[] = "SELECT *";
        $parts[] = "FROM foo";
        $parts[] = "INNER JOIN baz ON foo.id = baz.foo_id";

        $expectedSql = implode(PHP_EOL, $parts);

        $this->assertSame($expectedSql, $sql);
    }

    public function testToStringWithLeftJoin()
    {
        $sql = (string)$this->getSut('foo')
            ->leftJoin(new Table('baz', 'b'), new Expr('foo.id = b.foo_id'));

        $parts   = [];
        $parts[] = "SELECT *";
        $parts[] = "FROM foo";
        $parts[] = "LEFT JOIN baz AS b ON foo.id = b.foo_id";

        $expectedSql = implode(PHP_EOL, $parts);

        $this->assertSame($expectedSql, $sql);
    }

    public function testToStringWithRightJoin()
    {
        $sql = (string)$this->getSut(new Table('foo', 'f'))
            ->rightJoin(new Table('baz', 'b'), new Expr('f.id = b.foo_id'));

        $parts   = [];
        $parts[] = "SELECT *";
        $parts[] = "FROM foo AS f";
        $parts[] = "RIGHT JOIN baz AS b ON f.id = b.foo_id";

        $expectedSql = implode(PHP_EOL, $parts);

        $this->assertSame($expectedSql, $sql);
    }

    public function testToStringWithFullJoin()
    {
        $sql = (string)$this->getSut(new Table('foo', 'f'))
            ->fullJoin(new Table('baz', 'b'), new Expr('f.id = b.foo_id'));

        $parts   = [];
        $parts[] = "SELECT *";
        $parts[] = "FROM foo AS f";
        $parts[] = "FULL JOIN baz AS b ON f.id = b.foo_id";

        $expectedSql = implode(PHP_EOL, $parts);

        $this->assertSame($expectedSql, $sql);
    }

    public function testToStringWithManyJoins()
    {
        $join0 = new Join(IJoin::TYPE_INNER_JOIN, new Table('bar', 'b0'), 'b0.foo_id = foo.id');
        $join1 = new Join(IJoin::TYPE_LEFT_JOIN, new Table('bar', 'b1'), 'b1.foo_id = foo.id AND FALSE');
        $join2 = new Join(IJoin::TYPE_LEFT_JOIN, new Table('bar', 'b2'), 'b2.foo_id = foo.id AND 0');

        $sql = (string)$this->getSut('foo')
            ->columns('foo.*')
            ->join($join0, $join1, $join2);

        $parts   = [];
        $parts[] = "SELECT foo.*";
        $parts[] = "FROM foo";
        $parts[] = "INNER JOIN bar AS b0 ON b0.foo_id = foo.id";
        $parts[] = "LEFT JOIN bar AS b1 ON b1.foo_id = foo.id AND FALSE";
        $parts[] = "LEFT JOIN bar AS b2 ON b2.foo_id = foo.id AND 0";

        $expectedSql = implode(PHP_EOL, $parts);

        $this->assertSame($expectedSql, $sql);
    }

    public function testToStringComplex()
    {
        $sql = (string)$this->getSut()
            ->from('foo', 'bar')
            ->modifier('DISTINCT')
            ->columns('COUNT(DISTINCT baz) AS baz_count', 'q.foo_id')
            ->innerJoin(new Table('quix', 'q'), 'foo.id = q.foo_id')
            ->where('foo.bar = "foo-bar"', new Expr('bar.foo = ?', ['bar-foo']))
            ->groupBy('q.foo_id', new Expr('q.bar.id'))
            ->having('baz_count > 0')
            ->orderBy('baz_count')
            ->limit(10)
            ->offset(20);

        $parts   = [];
        $parts[] = 'SELECT DISTINCT COUNT(DISTINCT baz) AS baz_count, q.foo_id';
        $parts[] = 'FROM foo, bar';
        $parts[] = 'INNER JOIN quix AS q ON foo.id = q.foo_id';
        $parts[] = 'WHERE foo.bar = "foo-bar" AND bar.foo = ?';
        $parts[] = 'GROUP BY q.foo_id, q.bar.id';
        $parts[] = 'HAVING baz_count > 0';
        $parts[] = 'ORDER BY baz_count ASC';
        $parts[] = 'OFFSET 20 ROWS';
        $parts[] = 'FETCH FIRST 10 ROWS ONLY';

        $expectedSql = implode(PHP_EOL, $parts);

        $this->assertSame($expectedSql, $sql);
    }

    public function testGetParamsComplex()
    {
        $query = $this->getSut()
            ->from('foo', 'bar')
            ->columns(new Column(new Expr('COUNT(*) + ?', [2]), 'cpp'))
            ->leftJoin(new Table('baz', 'b'), new Expr('b.c < ?', [3]))
            ->where(new Expr('foo.a IN (?)', [[4], [5]]))
            ->groupBy(new Expr('foo.c > ?', [6]))
            ->having(new Expr('foo.maybe = ?', [7]));

        $expectedParams = [
            [2, PDO::PARAM_INT],
            [3, PDO::PARAM_INT],
            [4, PDO::PARAM_INT],
            [5, PDO::PARAM_INT],
            [6, PDO::PARAM_INT],
            [7, PDO::PARAM_INT],
        ];

        $this->assertSame($expectedParams, $query->getParams());
    }

    /**
     * @param Table|string ...$tables
     *
     * @return ISelect
     */
    protected function getSut(Table|string ...$tables): ISelect
    {
        return (new Select())->from(...$tables);
    }
}


1			<?php
2
3			declare(strict_types=1);
4
5			namespace QB\Generic\Statement;
6
7			use PDO;
8			use PHPUnit\Framework\TestCase;
9			use QB\Generic\Clause\Column;
10			use QB\Generic\Clause\IJoin;
11			use QB\Generic\Clause\Join;
12			use QB\Generic\Clause\Table;
13			use QB\Generic\Expr\Expr;
14			use RuntimeException;
15
16			/**
17			* @SuppressWarnings(PHPMD.TooManyPublicMethods)
18			*/
19			class SelectTest extends TestCase
20			{
21			/**
22			* @suppress PhanNoopCast
23			*/
24			public function testToStringThrowsAnExceptionIfNotInitialized()
25			{
26			$this->expectException(RuntimeException::class);
27
28			(string)$this->getSut();
29			}
30
31			public function testToStringConstant()
32			{
33			$sql = (string)$this->getSut()->columns('1');
34
35			$this->assertSame('SELECT 1', $sql);
36			}
37
38			public function testToStringExpressions()
39			{
40			$sql = (string)$this->getSut()->columns(new Column('COUNT(foo)', 'foo_count'));
41
42			$this->assertSame('SELECT COUNT(foo) AS foo_count', $sql);
43			}
44
45			public function testToStringFromTwoTables()
46			{
47			$sql = (string)$this->getSut('foo', 'bar');
48
49			$expectedSql = "SELECT *\nFROM foo, bar";
50
51			$this->assertSame($expectedSql, $sql);
52			}
53
54			public function testToStringFromTwoTablesWithOneAlias()
55			{
56			$sql = (string)$this->getSut(new Table('foo', 'f'), 'bar');
57
58			$expectedSql = "SELECT *\nFROM foo AS f, bar";
59
60			$this->assertSame($expectedSql, $sql);
61			}
62
63			public function testToStringFromTwoTablesWithAliases()
64			{
65			$sql = (string)$this->getSut(new Table('foo', 'f'), new Table('bar', 'b'));
66
67			$expectedSql = "SELECT *\nFROM foo AS f, bar AS b";
68
69			$this->assertSame($expectedSql, $sql);
70			}
71
72			public function testWithInnerJoin()
73			{
74			$sql = (string)$this->getSut('foo')
75			->innerJoin('baz', 'foo.id = baz.foo_id');
76
77			$parts = [];
78			$parts[] = "SELECT *";
79			$parts[] = "FROM foo";
80			$parts[] = "INNER JOIN baz ON foo.id = baz.foo_id";
81
82			$expectedSql = implode(PHP_EOL, $parts);
83
84			$this->assertSame($expectedSql, $sql);
85			}
86
87			public function testToStringWithLeftJoin()
88			{
89			$sql = (string)$this->getSut('foo')
90			->leftJoin(new Table('baz', 'b'), new Expr('foo.id = b.foo_id'));
91
92			$parts = [];
93			$parts[] = "SELECT *";
94			$parts[] = "FROM foo";
95			$parts[] = "LEFT JOIN baz AS b ON foo.id = b.foo_id";
96
97			$expectedSql = implode(PHP_EOL, $parts);
98
99			$this->assertSame($expectedSql, $sql);
100			}
101
102			public function testToStringWithRightJoin()
103			{
104			$sql = (string)$this->getSut(new Table('foo', 'f'))
105			->rightJoin(new Table('baz', 'b'), new Expr('f.id = b.foo_id'));
106
107			$parts = [];
108			$parts[] = "SELECT *";
109			$parts[] = "FROM foo AS f";
110			$parts[] = "RIGHT JOIN baz AS b ON f.id = b.foo_id";
111
112			$expectedSql = implode(PHP_EOL, $parts);
113
114			$this->assertSame($expectedSql, $sql);
115			}
116
117			public function testToStringWithFullJoin()
118			{
119			$sql = (string)$this->getSut(new Table('foo', 'f'))
120			->fullJoin(new Table('baz', 'b'), new Expr('f.id = b.foo_id'));
121
122			$parts = [];
123			$parts[] = "SELECT *";
124			$parts[] = "FROM foo AS f";
125			$parts[] = "FULL JOIN baz AS b ON f.id = b.foo_id";
126
127			$expectedSql = implode(PHP_EOL, $parts);
128
129			$this->assertSame($expectedSql, $sql);
130			}
131
132			public function testToStringWithManyJoins()
133			{
134			$join0 = new Join(IJoin::TYPE_INNER_JOIN, new Table('bar', 'b0'), 'b0.foo_id = foo.id');
135			$join1 = new Join(IJoin::TYPE_LEFT_JOIN, new Table('bar', 'b1'), 'b1.foo_id = foo.id AND FALSE');
136			$join2 = new Join(IJoin::TYPE_LEFT_JOIN, new Table('bar', 'b2'), 'b2.foo_id = foo.id AND 0');
137
138			$sql = (string)$this->getSut('foo')
139			->columns('foo.*')
140			->join($join0, $join1, $join2);
141
142			$parts = [];
143			$parts[] = "SELECT foo.*";
144			$parts[] = "FROM foo";
145			$parts[] = "INNER JOIN bar AS b0 ON b0.foo_id = foo.id";
146			$parts[] = "LEFT JOIN bar AS b1 ON b1.foo_id = foo.id AND FALSE";
147			$parts[] = "LEFT JOIN bar AS b2 ON b2.foo_id = foo.id AND 0";
148
149			$expectedSql = implode(PHP_EOL, $parts);
150
151			$this->assertSame($expectedSql, $sql);
152			}
153
154			public function testToStringComplex()
155			{
156			$sql = (string)$this->getSut()
157			->from('foo', 'bar')
158			->modifier('DISTINCT')
159			->columns('COUNT(DISTINCT baz) AS baz_count', 'q.foo_id')
160			->innerJoin(new Table('quix', 'q'), 'foo.id = q.foo_id')
161			->where('foo.bar = "foo-bar"', new Expr('bar.foo = ?', ['bar-foo']))
162			->groupBy('q.foo_id', new Expr('q.bar.id'))
163			->having('baz_count > 0')
164			->orderBy('baz_count')
165			->limit(10)
166			->offset(20);
167
168			$parts = [];
169			$parts[] = 'SELECT DISTINCT COUNT(DISTINCT baz) AS baz_count, q.foo_id';
170			$parts[] = 'FROM foo, bar';
171			$parts[] = 'INNER JOIN quix AS q ON foo.id = q.foo_id';
172			$parts[] = 'WHERE foo.bar = "foo-bar" AND bar.foo = ?';
173			$parts[] = 'GROUP BY q.foo_id, q.bar.id';
174			$parts[] = 'HAVING baz_count > 0';
175			$parts[] = 'ORDER BY baz_count ASC';
176			$parts[] = 'OFFSET 20 ROWS';
177			$parts[] = 'FETCH FIRST 10 ROWS ONLY';
178
179			$expectedSql = implode(PHP_EOL, $parts);
180
181			$this->assertSame($expectedSql, $sql);
182			}
183
184			public function testGetParamsComplex()
185			{
186			$query = $this->getSut()
187			->from('foo', 'bar')
188			->columns(new Column(new Expr('COUNT(*) + ?', [2]), 'cpp'))
189			->leftJoin(new Table('baz', 'b'), new Expr('b.c < ?', [3]))
190			->where(new Expr('foo.a IN (?)', [[4], [5]]))
191			->groupBy(new Expr('foo.c > ?', [6]))
192			->having(new Expr('foo.maybe = ?', [7]));
193
194			$expectedParams = [
195			[2, PDO::PARAM_INT],
196			[3, PDO::PARAM_INT],
197			[4, PDO::PARAM_INT],
198			[5, PDO::PARAM_INT],
199			[6, PDO::PARAM_INT],
200			[7, PDO::PARAM_INT],
201			];
202
203			$this->assertSame($expectedParams, $query->getParams());
204			}
205
206			/**
207			* @param Table\|string ...$tables
208			*
209			* @return ISelect
210			*/
211			protected function getSut(Table\|string ...$tables): ISelect
212			{
213			return (new Select())->from(...$tables);
214			}
215			}
216

abterphp / qb

SelectTest::testToStringWithLeftJoin() A last analyzed 2021-07-05 23:35 UTC

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like

SelectTest::testToStringWithLeftJoin() A
last analyzed 2021-07-05 23:35 UTC