packages/ztd-query-pdo-adapter/tests/Integration/Sqlite/SelectGroupByTest.php

1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Integration\Sqlite;
6
7use PDO;
8use PHPUnit\Framework\Attributes\CoversNothing;
9use PHPUnit\Framework\Attributes\Large;
10use PHPUnit\Framework\TestCase;
11use ZtdQuery\Adapter\Pdo\ZtdPdo;
12
13/**
14 * @requires extension pdo_sqlite
15 *
16 * @phpstan-type Row array<string, mixed>
17 */
18#[CoversNothing]
19#[Large]
20final class SelectGroupByTest extends TestCase
21{
22    public function testGroupByWithCount(): void
23    {
24        $rawPdo = new PDO('sqlite::memory:', null, null, [
25            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
26            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
27        ]);
28        $rawPdo->exec('CREATE TABLE sales (id INTEGER PRIMARY KEY, category TEXT NOT NULL, amount INTEGER NOT NULL)');
29        $rawPdo->exec("INSERT INTO sales (id, category, amount) VALUES (1, 'A', 100), (2, 'B', 200), (3, 'A', 150), (4, 'B', 50), (5, 'C', 300)");
30
31        $ztdPdo = ZtdPdo::fromPdo($rawPdo);
32        $ztdPdo->exec("INSERT INTO sales (id, category, amount) VALUES (1, 'A', 100), (2, 'B', 200), (3, 'A', 150), (4, 'B', 50), (5, 'C', 300)");
33
34        $sql = 'SELECT category, COUNT(*) AS cnt FROM sales GROUP BY category ORDER BY category';
35
36        $stmt = $rawPdo->query($sql);
37        self::assertNotFalse($stmt);
38        /** @var list<Row> $rawRows */
39        $rawRows = $stmt->fetchAll();
40        $stmt = $ztdPdo->query($sql);
41        self::assertNotFalse($stmt);
42        /** @var list<Row> $ztdRows */
43        $ztdRows = $stmt->fetchAll();
44
45        self::assertSame($rawRows, $ztdRows);
46    }
47
48    public function testGroupByWithSum(): void
49    {
50        $rawPdo = new PDO('sqlite::memory:', null, null, [
51            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
52            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
53        ]);
54        $rawPdo->exec('CREATE TABLE sales (id INTEGER PRIMARY KEY, category TEXT NOT NULL, amount INTEGER NOT NULL)');
55        $rawPdo->exec("INSERT INTO sales (id, category, amount) VALUES (1, 'A', 100), (2, 'B', 200), (3, 'A', 150), (4, 'B', 50), (5, 'C', 300)");
56
57        $ztdPdo = ZtdPdo::fromPdo($rawPdo);
58        $ztdPdo->exec("INSERT INTO sales (id, category, amount) VALUES (1, 'A', 100), (2, 'B', 200), (3, 'A', 150), (4, 'B', 50), (5, 'C', 300)");
59
60        $sql = 'SELECT category, SUM(amount) AS total FROM sales GROUP BY category ORDER BY category';
61
62        $stmt = $rawPdo->query($sql);
63        self::assertNotFalse($stmt);
64        /** @var list<Row> $rawRows */
65        $rawRows = $stmt->fetchAll();
66        $stmt = $ztdPdo->query($sql);
67        self::assertNotFalse($stmt);
68        /** @var list<Row> $ztdRows */
69        $ztdRows = $stmt->fetchAll();
70
71        self::assertSame($rawRows, $ztdRows);
72    }
73
74    public function testGroupByWithMinMax(): void
75    {
76        $rawPdo = new PDO('sqlite::memory:', null, null, [
77            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
78            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
79        ]);
80        $rawPdo->exec('CREATE TABLE sales (id INTEGER PRIMARY KEY, category TEXT NOT NULL, amount INTEGER NOT NULL)');
81        $rawPdo->exec("INSERT INTO sales (id, category, amount) VALUES (1, 'A', 100), (2, 'B', 200), (3, 'A', 150), (4, 'B', 50), (5, 'C', 300)");
82
83        $ztdPdo = ZtdPdo::fromPdo($rawPdo);
84        $ztdPdo->exec("INSERT INTO sales (id, category, amount) VALUES (1, 'A', 100), (2, 'B', 200), (3, 'A', 150), (4, 'B', 50), (5, 'C', 300)");
85
86        $sql = 'SELECT category, MIN(amount) AS min_amt, MAX(amount) AS max_amt FROM sales GROUP BY category ORDER BY category';
87
88        $stmt = $rawPdo->query($sql);
89        self::assertNotFalse($stmt);
90        /** @var list<Row> $rawRows */
91        $rawRows = $stmt->fetchAll();
92        $stmt = $ztdPdo->query($sql);
93        self::assertNotFalse($stmt);
94        /** @var list<Row> $ztdRows */
95        $ztdRows = $stmt->fetchAll();
96
97        self::assertSame($rawRows, $ztdRows);
98    }
99}
100