packages/ztd-query-pdo-adapter/tests/Integration/PostgreSql/SelectSubqueryTest.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 SelectSubqueryTest extends TestCase
24{
25 public function testSubqueryInWhere(): 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 * FROM {$users} WHERE id IN (SELECT user_id FROM {$orders}) ORDER BY 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 testScalarSubquery(): 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 name, (SELECT COUNT(*) FROM {$orders} WHERE {$orders}.user_id = {$users}.id) AS order_count FROM {$users} ORDER BY 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 testSubqueryInFrom(): 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 sub.name FROM (SELECT name FROM {$users} WHERE id <= 2) AS sub ORDER BY sub.name";
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