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