packages/ztd-query-pdo-adapter/tests/Integration/PostgreSql/SelectJoinTest.php
1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Integration\PostgreSql;
6
7use Container\Endpoint;
8use Container\PostgreSqlRelease;
9use PDO;
10use PHPUnit\Framework\Attributes\CoversNothing;
11use PHPUnit\Framework\Attributes\Large;
12use PHPUnit\Framework\TestCase;
13use ZtdQuery\Adapter\Pdo\ZtdPdo;
14
15/**
16 * @requires extension pdo_pgsql
17 * @group integration
18 * @group postgres
19 *
20 * @phpstan-type Row array<string, mixed>
21 */
22#[CoversNothing]
23#[Large]
24final class SelectJoinTest extends TestCase
25{
26 public function testInnerJoin(): void
27 {
28 $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
29 /** @var PDO $rawPdo */
30 $rawPdo = new PDO(
31 $endpoint->dsn(),
32 $endpoint->username,
33 $endpoint->password,
34 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
35 );
36
37 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
38 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
39 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
40
41 $users = 'prefix_' . bin2hex(random_bytes(8));
42 $orders = 'prefix_' . bin2hex(random_bytes(8));
43
44 try {
45 $rawPdo->exec("CREATE TABLE {$users} (id INTEGER PRIMARY KEY, name TEXT NOT NULL)");
46 $rawPdo->exec("INSERT INTO {$users} (id, name) VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie')");
47
48 $rawPdo->exec("CREATE TABLE {$orders} (id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, amount INTEGER NOT NULL)");
49 $rawPdo->exec("INSERT INTO {$orders} (id, user_id, amount) VALUES (1, 1, 100), (2, 1, 200), (3, 2, 150)");
50
51 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
52
53 $ztdPdo->exec("INSERT INTO {$users} (id, name) VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie')");
54 $ztdPdo->exec("INSERT INTO {$orders} (id, user_id, amount) VALUES (1, 1, 100), (2, 1, 200), (3, 2, 150)");
55
56 $sql = "SELECT {$users}.name, {$orders}.amount FROM {$users} INNER JOIN {$orders} ON {$users}.id = {$orders}.user_id ORDER BY {$orders}.id";
57
58 $stmt = $rawPdo->query($sql);
59 self::assertNotFalse($stmt);
60 /** @var list<Row> */
61 $rawRows = $stmt->fetchAll();
62
63 $stmt = $ztdPdo->query($sql);
64 self::assertNotFalse($stmt);
65 /** @var list<Row> */
66 $ztdRows = $stmt->fetchAll();
67
68 self::assertSame($rawRows, $ztdRows);
69 } finally {
70 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
71 }
72 }
73
74 public function testLeftJoin(): void
75 {
76 $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
77 /** @var PDO $rawPdo */
78 $rawPdo = new PDO(
79 $endpoint->dsn(),
80 $endpoint->username,
81 $endpoint->password,
82 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
83 );
84
85 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
86 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
87 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
88
89 $users = 'prefix_' . bin2hex(random_bytes(8));
90 $orders = 'prefix_' . bin2hex(random_bytes(8));
91
92 try {
93 $rawPdo->exec("CREATE TABLE {$users} (id INTEGER PRIMARY KEY, name TEXT NOT NULL)");
94 $rawPdo->exec("INSERT INTO {$users} (id, name) VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie')");
95
96 $rawPdo->exec("CREATE TABLE {$orders} (id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, amount INTEGER NOT NULL)");
97 $rawPdo->exec("INSERT INTO {$orders} (id, user_id, amount) VALUES (1, 1, 100), (2, 1, 200), (3, 2, 150)");
98
99 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
100
101 $ztdPdo->exec("INSERT INTO {$users} (id, name) VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie')");
102 $ztdPdo->exec("INSERT INTO {$orders} (id, user_id, amount) VALUES (1, 1, 100), (2, 1, 200), (3, 2, 150)");
103
104 $sql = "SELECT {$users}.name, {$orders}.amount FROM {$users} LEFT JOIN {$orders} ON {$users}.id = {$orders}.user_id ORDER BY {$users}.id, {$orders}.id";
105
106 $stmt = $rawPdo->query($sql);
107 self::assertNotFalse($stmt);
108 /** @var list<Row> */
109 $rawRows = $stmt->fetchAll();
110
111 $stmt = $ztdPdo->query($sql);
112 self::assertNotFalse($stmt);
113 /** @var list<Row> */
114 $ztdRows = $stmt->fetchAll();
115
116 self::assertSame($rawRows, $ztdRows);
117 } finally {
118 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
119 }
120 }
121
122 public function testRightJoin(): void
123 {
124 $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
125 /** @var PDO $rawPdo */
126 $rawPdo = new PDO(
127 $endpoint->dsn(),
128 $endpoint->username,
129 $endpoint->password,
130 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
131 );
132
133 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
134 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
135 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
136
137 $users = 'prefix_' . bin2hex(random_bytes(8));
138 $orders = 'prefix_' . bin2hex(random_bytes(8));
139
140 try {
141 $rawPdo->exec("CREATE TABLE {$users} (id INTEGER PRIMARY KEY, name TEXT NOT NULL)");
142 $rawPdo->exec("INSERT INTO {$users} (id, name) VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie')");
143
144 $rawPdo->exec("CREATE TABLE {$orders} (id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, amount INTEGER NOT NULL)");
145 $rawPdo->exec("INSERT INTO {$orders} (id, user_id, amount) VALUES (1, 1, 100), (2, 1, 200), (3, 2, 150)");
146
147 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
148
149 $ztdPdo->exec("INSERT INTO {$users} (id, name) VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie')");
150 $ztdPdo->exec("INSERT INTO {$orders} (id, user_id, amount) VALUES (1, 1, 100), (2, 1, 200), (3, 2, 150)");
151
152 $sql = "SELECT {$users}.name, {$orders}.amount FROM {$users} RIGHT JOIN {$orders} ON {$users}.id = {$orders}.user_id ORDER BY {$orders}.id";
153
154 $stmt = $rawPdo->query($sql);
155 self::assertNotFalse($stmt);
156 /** @var list<Row> */
157 $rawRows = $stmt->fetchAll();
158
159 $stmt = $ztdPdo->query($sql);
160 self::assertNotFalse($stmt);
161 /** @var list<Row> */
162 $ztdRows = $stmt->fetchAll();
163
164 self::assertSame($rawRows, $ztdRows);
165 } finally {
166 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
167 }
168 }
169
170 public function testFullJoin(): void
171 {
172 $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
173 /** @var PDO $rawPdo */
174 $rawPdo = new PDO(
175 $endpoint->dsn(),
176 $endpoint->username,
177 $endpoint->password,
178 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
179 );
180
181 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
182 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
183 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
184
185 $users = 'prefix_' . bin2hex(random_bytes(8));
186 $orders = 'prefix_' . bin2hex(random_bytes(8));
187
188 try {
189 $rawPdo->exec("CREATE TABLE {$users} (id INTEGER PRIMARY KEY, name TEXT NOT NULL)");
190 $rawPdo->exec("INSERT INTO {$users} (id, name) VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie')");
191
192 $rawPdo->exec("CREATE TABLE {$orders} (id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, amount INTEGER NOT NULL)");
193 $rawPdo->exec("INSERT INTO {$orders} (id, user_id, amount) VALUES (1, 1, 100), (2, 1, 200), (3, 2, 150)");
194
195 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
196
197 $ztdPdo->exec("INSERT INTO {$users} (id, name) VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie')");
198 $ztdPdo->exec("INSERT INTO {$orders} (id, user_id, amount) VALUES (1, 1, 100), (2, 1, 200), (3, 2, 150)");
199
200 $sql = "SELECT {$users}.name, {$orders}.amount FROM {$users} FULL OUTER JOIN {$orders} ON {$users}.id = {$orders}.user_id ORDER BY {$users}.id NULLS LAST, {$orders}.id NULLS LAST";
201
202 $stmt = $rawPdo->query($sql);
203 self::assertNotFalse($stmt);
204 /** @var list<Row> */
205 $rawRows = $stmt->fetchAll();
206
207 $stmt = $ztdPdo->query($sql);
208 self::assertNotFalse($stmt);
209 /** @var list<Row> */
210 $ztdRows = $stmt->fetchAll();
211
212 self::assertSame($rawRows, $ztdRows);
213 } finally {
214 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
215 }
216 }
217
218 public function testCrossJoin(): void
219 {
220 $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
221 /** @var PDO $rawPdo */
222 $rawPdo = new PDO(
223 $endpoint->dsn(),
224 $endpoint->username,
225 $endpoint->password,
226 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
227 );
228
229 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
230 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
231 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
232
233 $users = 'prefix_' . bin2hex(random_bytes(8));
234 $orders = 'prefix_' . bin2hex(random_bytes(8));
235
236 try {
237 $rawPdo->exec("CREATE TABLE {$users} (id INTEGER PRIMARY KEY, name TEXT NOT NULL)");
238 $rawPdo->exec("INSERT INTO {$users} (id, name) VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie')");
239
240 $rawPdo->exec("CREATE TABLE {$orders} (id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, amount INTEGER NOT NULL)");
241 $rawPdo->exec("INSERT INTO {$orders} (id, user_id, amount) VALUES (1, 1, 100), (2, 1, 200), (3, 2, 150)");
242
243 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
244
245 $ztdPdo->exec("INSERT INTO {$users} (id, name) VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie')");
246 $ztdPdo->exec("INSERT INTO {$orders} (id, user_id, amount) VALUES (1, 1, 100), (2, 1, 200), (3, 2, 150)");
247
248 $sql = "SELECT {$users}.name, {$orders}.amount FROM {$users} CROSS JOIN {$orders} ORDER BY {$users}.id, {$orders}.id";
249
250 $stmt = $rawPdo->query($sql);
251 self::assertNotFalse($stmt);
252 /** @var list<Row> */
253 $rawRows = $stmt->fetchAll();
254
255 $stmt = $ztdPdo->query($sql);
256 self::assertNotFalse($stmt);
257 /** @var list<Row> */
258 $ztdRows = $stmt->fetchAll();
259
260 self::assertSame($rawRows, $ztdRows);
261 } finally {
262 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
263 }
264 }
265
266 public function testNaturalJoin(): void
267 {
268 $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
269 /** @var PDO $rawPdo */
270 $rawPdo = new PDO(
271 $endpoint->dsn(),
272 $endpoint->username,
273 $endpoint->password,
274 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
275 );
276
277 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
278 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
279 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
280
281 $t1 = 'prefix_' . bin2hex(random_bytes(8));
282 $t2 = 'prefix_' . bin2hex(random_bytes(8));
283
284 try {
285 $rawPdo->exec("CREATE TABLE {$t1} (id INTEGER PRIMARY KEY, val TEXT NOT NULL)");
286 $rawPdo->exec("INSERT INTO {$t1} (id, val) VALUES (1, 'a'), (2, 'b')");
287
288 $rawPdo->exec("CREATE TABLE {$t2} (id INTEGER PRIMARY KEY, score INTEGER NOT NULL)");
289 $rawPdo->exec("INSERT INTO {$t2} (id, score) VALUES (1, 100), (2, 200)");
290
291 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
292
293 $ztdPdo->exec("INSERT INTO {$t1} (id, val) VALUES (1, 'a'), (2, 'b')");
294 $ztdPdo->exec("INSERT INTO {$t2} (id, score) VALUES (1, 100), (2, 200)");
295
296 $sql = "SELECT * FROM {$t1} NATURAL JOIN {$t2} ORDER BY id";
297
298 $stmt = $rawPdo->query($sql);
299 self::assertNotFalse($stmt);
300 /** @var list<Row> */
301 $rawRows = $stmt->fetchAll();
302
303 $stmt = $ztdPdo->query($sql);
304 self::assertNotFalse($stmt);
305 /** @var list<Row> */
306 $ztdRows = $stmt->fetchAll();
307
308 self::assertSame($rawRows, $ztdRows);
309 } finally {
310 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
311 }
312 }
313
314 public function testJoinWithTableWithoutPrimaryKey(): void
315 {
316 $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
317 /** @var PDO $rawPdo */
318 $rawPdo = new PDO(
319 $endpoint->dsn(),
320 $endpoint->username,
321 $endpoint->password,
322 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
323 );
324
325 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
326 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
327 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
328
329 $items = 'prefix_' . bin2hex(random_bytes(8));
330 $tags = 'prefix_' . bin2hex(random_bytes(8));
331
332 try {
333 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
334 $ztdPdo->exec("CREATE TABLE {$items} (id INTEGER PRIMARY KEY, name TEXT NOT NULL)");
335 $ztdPdo->exec("CREATE TABLE {$tags} (item_id INTEGER NOT NULL, tag VARCHAR(50) NOT NULL)");
336 $ztdPdo->exec("INSERT INTO {$items} VALUES (1, 'book'), (2, 'pen')");
337 $ztdPdo->exec("INSERT INTO {$tags} VALUES (1, 'new'), (1, 'sale'), (2, 'blue')");
338
339 $sql = "SELECT i.name, t.tag FROM {$items} i JOIN {$tags} t ON i.id = t.item_id ORDER BY i.id, t.tag";
340
341 $stmt = $ztdPdo->query($sql);
342 self::assertNotFalse($stmt);
343 /** @var list<Row> */
344 $ztdRows = $stmt->fetchAll();
345
346 self::assertSame([
347 ['name' => 'book', 'tag' => 'new'],
348 ['name' => 'book', 'tag' => 'sale'],
349 ['name' => 'pen', 'tag' => 'blue'],
350 ], $ztdRows);
351 } finally {
352 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
353 }
354 }
355}
356