packages/sql-catalog/tests/Unit/Extension/Laravel/SelectCompilerTest.php
1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Unit\Extension\Laravel;
6
7use PHPUnit\Framework\Attributes\CoversClass;
8use PHPUnit\Framework\Attributes\UsesClass;
9use PHPUnit\Framework\TestCase;
10use SqlCatalog\Core\Evaluation\ArrayEntry;
11use SqlCatalog\Core\Evaluation\ArrayTerm;
12use SqlCatalog\Core\Evaluation\Domain;
13use SqlCatalog\Core\Evaluation\LiteralTerm;
14use SqlCatalog\Core\Evaluation\ObjectTerm;
15use SqlCatalog\Core\Evaluation\OpaqueTerm;
16use SqlCatalog\Core\Evaluation\PatternTerm;
17use SqlCatalog\Core\Text\LiteralText;
18use SqlCatalog\Core\Text\TextGeneralization;
19use SqlCatalog\Core\Text\TextHole;
20use SqlCatalog\Core\Text\TextPattern;
21use SqlCatalog\Core\Type\TypeShape;
22use SqlCatalog\Extension\Laravel\Clauses;
23use SqlCatalog\Extension\Laravel\Grammar;
24use SqlCatalog\Extension\Laravel\Predicates;
25use SqlCatalog\Extension\Laravel\QueryState;
26use SqlCatalog\Extension\Laravel\SelectCompiler;
27
28#[CoversClass(SelectCompiler::class)]
29#[UsesClass(Domain::class)]
30#[UsesClass(ArrayTerm::class)]
31#[UsesClass(ArrayEntry::class)]
32#[UsesClass(ObjectTerm::class)]
33#[UsesClass(LiteralTerm::class)]
34#[UsesClass(OpaqueTerm::class)]
35#[UsesClass(PatternTerm::class)]
36#[UsesClass(TextPattern::class)]
37#[UsesClass(TextHole::class)]
38#[UsesClass(LiteralText::class)]
39#[UsesClass(TextGeneralization::class)]
40#[UsesClass(TypeShape::class)]
41#[UsesClass(QueryState::class)]
42#[UsesClass(Grammar::class)]
43#[UsesClass(Clauses::class)]
44#[UsesClass(Predicates::class)]
45final class SelectCompilerTest extends TestCase
46{
47 public function testCompileFindUsesTheQualifiedKeyAndOneRowLimit(): void
48 {
49 $state = new QueryState(['table' => Domain::literal('users'), 'key' => Domain::literal('users.id')]);
50 [$sql, $bindings] = (new SelectCompiler(new Grammar(\SqlCatalog\Facade\Builtins::dialects()->find('sqlite'))))->compile($state, 'find', [Domain::literal(7)]);
51 self::assertSame('select * from "users" where "users"."id" = ? limit 1', $sql->soleLiteral()?->value);
52 self::assertSame(7, $bindings->soleArray()?->positional()[0]->soleLiteral()?->value);
53 }
54
55 public function testCompilePreservesExplicitProjectionAndWrapsExists(): void
56 {
57 $compiler = new SelectCompiler(new Grammar(\SqlCatalog\Facade\Builtins::dialects()->find('mysql')));
58 $state = (new Clauses(new Grammar(\SqlCatalog\Facade\Builtins::dialects()->find('mysql'))))->select(new QueryState(['table' => Domain::literal('users')]), [Domain::literal('id')]);
59 self::assertSame('select `id` from `users` limit 1', $compiler->compile($state, 'first', [QueryState::list([Domain::literal('name')])])[0]->soleLiteral()?->value);
60 self::assertSame('select exists(select `id` from `users`) as `exists`', $compiler->compile($state, 'exists', [])[0]->soleLiteral()?->value);
61 self::assertFalse($compiler->compile($state, 'get', [Domain::literal('a'), Domain::literal('b')])[0]->isExact());
62 self::assertSame('select `id` from `users` where 0 = 1', $compiler->compile($state->with('key', Domain::literal('users.id'))->with('model', Domain::literal('User')), 'find', [QueryState::list([])])[0]->soleLiteral()?->value);
63 self::assertFalse($compiler->compile($state->with('key', Domain::literal('id')), 'find', [QueryState::list([])])[0]->isExact());
64 }
65
66 #[\PHPUnit\Framework\Attributes\DataProvider('providerSelectPreservesExplicitOffsetsAndComponentBindingOrder')]
67 public function testSelectPreservesExplicitOffsetsAndComponentBindingOrder(string $dialect, int $offset, string $expected): void
68 {
69 $state = new QueryState(['table' => Domain::literal('users'), 'columns' => QueryState::list([Domain::literal('? as x')]), 'selectBindings' => QueryState::list([Domain::literal(1)]), 'where' => QueryState::list([Domain::literal('id > ?')]), 'whereBindings' => QueryState::list([Domain::literal(2)]), 'offset' => Domain::literal($offset)]);
70 [$sql, $bindings] = (new SelectCompiler(new Grammar(\SqlCatalog\Facade\Builtins::dialects()->find($dialect))))->select($state);
71 self::assertSame($expected, $sql->soleLiteral()?->value);
72 self::assertSame([1, 2], array_map(static fn (Domain $v): mixed => $v->soleLiteral()?->value, $bindings->soleArray()?->positional() ?? []));
73 }
74
75 /**
76 * @return iterable<array{string, int, string}>
77 */
78 public static function providerSelectPreservesExplicitOffsetsAndComponentBindingOrder(): iterable
79 {
80 foreach (['mysql' => '`users`', 'sqlite' => '"users"', 'pgsql' => '"users"'] as $dialect => $table) {
81 yield [$dialect, 0, 'select ? as x from ' . $table . ' where id > ? offset 0'];
82 yield [$dialect, 3, 'select ? as x from ' . $table . ' where id > ? offset 3'];
83 }
84 }
85
86 public function testAggregateQuotesTheResultAliasAndDiscardsProjectionBindings(): void
87 {
88 $state = new QueryState(['table' => Domain::literal('users'), 'selectBindings' => QueryState::list([Domain::literal(1)]), 'orders' => QueryState::list([Domain::literal('id desc')])]);
89 $compiler = new SelectCompiler(new Grammar(\SqlCatalog\Facade\Builtins::dialects()->find('sqlite')));
90 [$sql, $bindings] = $compiler->aggregate($state, 'count', []);
91 self::assertSame('select count(*) as "aggregate" from "users"', $sql->soleLiteral()?->value);
92 self::assertSame([], $bindings->soleArray()?->entries);
93 self::assertSame('select count(*) as "aggregate" from "users"', $compiler->aggregate($state->with('distinct', Domain::literal(true)), 'count', [])[0]->soleLiteral()?->value);
94 self::assertSame('select count(distinct "id") as "aggregate" from "users"', $compiler->aggregate($state->with('distinct', Domain::literal(true)), 'count', [Domain::literal('id')])[0]->soleLiteral()?->value);
95 }
96
97 /**
98 * @param list<Domain> $arguments
99 */
100 #[\PHPUnit\Framework\Attributes\DataProvider('providerReadOperations')]
101 public function testCompileHonorsTerminalColumnsAndAggregateArguments(string $method, array $arguments, string $expected): void
102 {
103 $state = new QueryState(['table' => Domain::literal('users'), 'key' => Domain::literal('id')]);
104 [$sql, $bindings] = (new SelectCompiler(new Grammar(\SqlCatalog\Facade\Builtins::dialects()->find('sqlite'))))->compile($state, $method, $arguments);
105 self::assertSame($expected, $sql->soleLiteral()?->value);
106 self::assertNotNull($bindings->soleArray());
107 }
108
109 /**
110 * @return iterable<array{string, list<Domain>, string}>
111 */
112 public static function providerReadOperations(): iterable
113 {
114 yield ['get', [QueryState::list([Domain::literal('name')])], 'select "name" from "users"'];
115 yield ['first', [QueryState::list([Domain::literal('name')])], 'select "name" from "users" limit 1'];
116 yield ['firstorfail', [], 'select * from "users" limit 1'];
117 yield ['find', [Domain::literal(1), QueryState::list([Domain::literal('name')])], 'select "name" from "users" where "id" = ? limit 1'];
118 yield ['pluck', [Domain::literal('name'), Domain::literal('id')], 'select "name", "id" from "users"'];
119 yield ['doesntexist', [], 'select exists(select * from "users") as "exists"'];
120 yield ['findorfail', [Domain::literal(1)], 'select * from "users" where "id" = ? limit 1'];
121 yield ['sole', [], 'select * from "users" limit 2'];
122 yield ['value', [Domain::literal('email')], 'select "email" from "users" limit 1'];
123 yield ['cursor', [], 'select * from "users"'];
124 foreach (['count', 'sum', 'avg', 'min', 'max'] as $method) {
125 yield [$method, [Domain::literal('id')], 'select '.$method.'("id") as "aggregate" from "users"'];
126 }
127 }
128
129 public function testSelectAssemblesAllComponentsAndBindingGroupsInSqlOrder(): void
130 {
131 $state = new QueryState(['table' => Domain::literal('users'), 'columns' => QueryState::list([Domain::literal('? as a')]), 'joins' => QueryState::list([Domain::literal('join teams on teams.id = users.team_id')]), 'where' => QueryState::list([Domain::literal('active = ?')]), 'groups' => QueryState::list([Domain::literal('a'), Domain::literal('b')]), 'having' => QueryState::list([Domain::literal('count(*) > ?'), Domain::literal('and count(*) < ?')]), 'orders' => QueryState::list([Domain::literal('a asc'), Domain::literal('b desc')]), 'distinct' => Domain::literal(true), 'offset' => Domain::literal(0), 'selectBindings' => QueryState::list([Domain::literal(1)]), 'joinBindings' => QueryState::list([Domain::literal(2)]), 'whereBindings' => QueryState::list([Domain::literal(3)]), 'havingBindings' => QueryState::list([Domain::literal(4)]), 'orderBindings' => QueryState::list([Domain::literal(5)])]);
132 [$sql, $bindings] = (new SelectCompiler(new Grammar(\SqlCatalog\Facade\Builtins::dialects()->find('sqlite'))))->select($state);
133 self::assertSame('select distinct ? as a from "users" join teams on teams.id = users.team_id where active = ? group by a, b having count(*) > ? and count(*) < ? order by a asc, b desc offset 0', $sql->soleLiteral()?->value);
134 self::assertSame([1, 2, 3, 4, 5], array_map(static fn (Domain $value): mixed => $value->soleLiteral()?->value, $bindings->soleArray()?->positional() ?? []));
135 }
136
137 public function testCompileKeepsMissingFindKeysAndExcessPluckArgumentsOpen(): void
138 {
139 $compiler = new SelectCompiler(new Grammar(\SqlCatalog\Facade\Builtins::dialects()->find('sqlite')));
140 $state = new QueryState(['table' => Domain::literal('users')]);
141 self::assertFalse($compiler->compile($state, 'find', [])[0]->isExact());
142 self::assertFalse($compiler->compile($state, 'pluck', array_map(Domain::literal(...), ['a', 'b', 'c']))[0]->isExact());
143 self::assertFalse($compiler->aggregate($state->with('having', QueryState::list([Domain::literal('count(*) > 1')])), 'count', [])[0]->isExact());
144 }
145
146
147 public function testKeyComparesScalarsAndListsAgainstThePrimaryKey(): void
148 {
149 $compiler = new SelectCompiler(new Grammar(\SqlCatalog\Facade\Builtins::dialects()->find('sqlite')));
150 $state = new QueryState(['table' => Domain::literal('users'), 'key' => Domain::literal('users.id'), 'model' => Domain::literal('User')]);
151 $one = $compiler->key($state, Domain::literal(7));
152 self::assertSame('"users"."id" = ?', $one->items('where')[0]->soleLiteral()?->value);
153 self::assertSame(1, $one->get('limit')->soleLiteral()?->value);
154 $many = $compiler->key($state, QueryState::list([Domain::literal(1), Domain::literal('2')]));
155 self::assertSame('"users"."id" in (1, 2)', $many->items('where')[0]->soleLiteral()?->value);
156 self::assertSame([], $many->items('whereBindings'));
157 self::assertNull($many->get('limit')->soleLiteral()?->value);
158 self::assertSame('0 = 1', $compiler->key($state, QueryState::list([]))->items('where')[0]->soleLiteral()?->value);
159 self::assertFalse($compiler->key($state, QueryState::list([Domain::unknown()]))->items('where')[0]->isExact());
160 $strings = $compiler->key($state->with('keyType', Domain::literal('string')), QueryState::list([Domain::literal('a'), Domain::literal('b')]));
161 self::assertSame('"users"."id" in (?, ?)', $strings->items('where')[0]->soleLiteral()?->value);
162 self::assertSame(['a', 'b'], array_map(static fn (Domain $v): mixed => $v->soleLiteral()?->value, $strings->items('whereBindings')));
163 $typed = $compiler->key($state, Domain::opaque(TypeShape::of(['array']), \SqlCatalog\Core\Text\Origin::Parameter, '$ids'));
164 self::assertFalse($typed->items('where')[0]->isExact());
165 self::assertSame([], $typed->items('whereBindings'));
166 self::assertFalse($compiler->key(new QueryState(['key' => Domain::literal('id')]), QueryState::list([Domain::literal(1)]))->get('problem')->isExact());
167 }
168
169 public function testPageWindowsChunksByTheLoopAndPagesByTheRequest(): void
170 {
171 $compiler = new SelectCompiler(new Grammar(\SqlCatalog\Facade\Builtins::dialects()->find('sqlite')));
172 $state = new QueryState(['table' => Domain::literal('users')]);
173 $chunk = $compiler->page($state, 'chunk', [Domain::literal(100)]);
174 self::assertSame(100, $chunk->get('limit')->soleLiteral()?->value);
175 self::assertSame(\SqlCatalog\Core\Text\Origin::Loop, $chunk->get('offset')->patterns()[0]->holes()[0]->origin);
176 $page = $compiler->page($state, 'paginate', []);
177 self::assertSame(15, $page->get('limit')->soleLiteral()?->value);
178 self::assertSame(\SqlCatalog\Core\Text\Origin::External, $page->get('offset')->patterns()[0]->holes()[0]->origin);
179 self::assertSame(21, $compiler->page($state, 'simplepaginate', [Domain::literal(20)])->get('limit')->soleLiteral()?->value);
180 self::assertSame(8, $compiler->page($state->with('perPage', Domain::literal(7)), 'simplepaginate', [Domain::literal(null)])->get('limit')->soleLiteral()?->value);
181 self::assertSame(7, $compiler->page($state->with('perPage', Domain::literal(7)), 'paginate', [])->get('limit')->soleLiteral()?->value);
182 self::assertSame(1000, $compiler->page($state, 'lazy', [])->get('limit')->soleLiteral()?->value);
183 self::assertSame(1000, $compiler->page($state, 'each', [Domain::unknown()])->get('limit')->soleLiteral()?->value);
184 self::assertSame(50, $compiler->page($state, 'each', [Domain::unknown(), Domain::literal(50)])->get('limit')->soleLiteral()?->value);
185 self::assertSame('select * from "users" limit 1000 offset {$}', $compiler->compile($state, 'lazy', [])[0]->patterns()[0]->display());
186 self::assertFalse($compiler->page($state, 'simplepaginate', [Domain::unknown()])->get('limit')->isExact());
187 [$sql] = $compiler->compile($state, 'paginate', [Domain::literal(10), QueryState::list([Domain::literal('id')])]);
188 self::assertSame('select "id" from "users" limit 10 offset {$}', $sql->patterns()[0]->display());
189 }
190
191 public function testTotalCountsWithoutTheWindowOrOrdering(): void
192 {
193 $compiler = new SelectCompiler(new Grammar(\SqlCatalog\Facade\Builtins::dialects()->find('sqlite')));
194 $state = new QueryState(['table' => Domain::literal('users'), 'columns' => QueryState::list([Domain::literal('"id"')]), 'where' => QueryState::list([Domain::literal('"a" = ?')]), 'whereBindings' => QueryState::list([Domain::literal(1)]), 'orders' => QueryState::list([Domain::literal('"id" asc')]), 'limit' => Domain::literal(10), 'offset' => Domain::literal(20)]);
195 [$sql, $bindings] = $compiler->total($state);
196 self::assertSame('select count(*) as "aggregate" from "users" where "a" = ?', $sql->soleLiteral()?->value);
197 self::assertSame([1], array_map(static fn (Domain $v): mixed => $v->soleLiteral()?->value, $bindings->soleArray()?->positional() ?? []));
198 self::assertSame('select count(*) as "aggregate" from (select "id" from "users" where "a" = ? group by "a") as "aggregate_table"', $compiler->total($state->with('groups', QueryState::list([Domain::literal('"a"')])))[0]->soleLiteral()?->value);
199 self::assertSame('select count(*) as "aggregate" from (select * from "users" where "a" = ? having "x" > ?) as "aggregate_table"', $compiler->total($state->with('columns', QueryState::list([]))->with('having', QueryState::list([Domain::literal('"x" > ?')])))[0]->soleLiteral()?->value);
200 self::assertSame('select count(*) as "aggregate" from (select "users".* from "users" inner join "t" on "t"."u" = "users"."id" where "a" = ? group by "a") as "aggregate_table"', $compiler->total($state->with('columns', QueryState::list([]))->with('joins', QueryState::list([Domain::literal('inner join "t" on "t"."u" = "users"."id"')]))->with('groups', QueryState::list([Domain::literal('"a"')])))[0]->soleLiteral()?->value);
201 self::assertSame('select count(*) as "aggregate" from "users" where "a" = ?', $compiler->total($state->with('distinct', Domain::literal(true)))[0]->soleLiteral()?->value);
202 }
203
204 public function testSelectWritesUnresolvedLimitsAndOffsetsAsGaps(): void
205 {
206 $compiler = new SelectCompiler(new Grammar(\SqlCatalog\Facade\Builtins::dialects()->find('mysql')));
207 $state = new QueryState(['table' => Domain::literal('users'), 'columns' => QueryState::list([Domain::literal('*')]), 'limit' => Domain::opaque(TypeShape::of(['int']), \SqlCatalog\Core\Text\Origin::Parameter, '$n'), 'offset' => Domain::literal(null)]);
208 [$sql] = $compiler->select($state);
209 self::assertSame('select * from `users` limit {$}', $sql->patterns()[0]->display());
210 self::assertSame(\SqlCatalog\Core\Text\Origin::Parameter, $sql->patterns()[0]->holes()[0]->origin);
211 self::assertCount(1, $sql->patterns()[0]->holes());
212 }
213}
214