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\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 SelectSubqueryTest extends TestCase
25{
26 public function testSubqueryInWhere(): 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 * FROM {$users} WHERE id IN (SELECT user_id FROM {$orders}) ORDER BY 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 testScalarSubquery(): 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 name, (SELECT COUNT(*) FROM {$orders} WHERE {$orders}.user_id = {$users}.id) AS order_count FROM {$users} ORDER BY 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 testSubqueryInFrom(): 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 sub.name FROM (SELECT name FROM {$users} WHERE id <= 2) AS sub ORDER BY sub.name";
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