symfony7之使用mysql8的CTE功能

使用CTE查询:

    public function getEventOrderTickets(Event $event): array
    {
        $sql = <<<SQL
WITH reserved AS (
SELECT ticket_id, SUM(quantity) AS reservedAmount
FROM ticket_reservations
GROUP BY ticket_id
)
SELECT
t.id,
t.title,
COALESCE(t.quantity, 0) AS availability,
COALESCE(rs.reservedAmount, 0) as reservedAmount,
s.title AS sectionTitle,

    COUNT(CASE WHEN o.status_id != :initialStatus THEN 1 ELSE NULL END) AS registeredAmount,
    COUNT(DISTINCT CASE WHEN o.status_id = :completedStatus THEN JSON_UNQUOTE(JSON_EXTRACT(r.camel_cased_registration_data, '$.email')) ELSE NULL END) AS completedCount,
    COUNT(DISTINCT CASE WHEN r.checked_in = 1 AND o.status_id = :completedStatus THEN JSON_UNQUOTE(JSON_EXTRACT(r.camel_cased_registration_data, '$.email')) ELSE NULL END) AS checkedInCount

FROM tickets t
LEFT JOIN ticket_sections s ON t.section_id = s.id
JOIN events e ON t.event_id = e.id
LEFT JOIN event_registrations r ON r.ticket_id = t.id
LEFT JOIN event_registration_orders o ON r.order_id = o.id
LEFT JOIN reserved rs ON rs.ticket_id = t.id

WHERE e.id = :eventId
GROUP BY t.id
ORDER BY t.created_at DESC
SQL;
$params = [
'eventId'         => $event->getId(),
'initialStatus'   => EventRegistrationOrderStatusEnum::INITIAL->value,
'completedStatus' => EventRegistrationOrderStatusEnum::COMPLETED->value,
];

        return $this->getEntityManager()->getConnection()
            ->executeQuery($sql, $params)
            ->fetchAllAssociative();
    }

---

使用子查询:

public function getEventOrderTickets(Event $event): array
{
$sql = <<<SQL
SELECT
t.id,
t.title,
COALESCE(t.quantity, 0) AS availability,
COALESCE(rs.reservedAmount, 0) as reservedAmount,
s.title AS sectionTitle,
COUNT(CASE WHEN o.status_id != :initialStatus THEN 1 ELSE NULL END) AS registeredAmount,
COUNT(DISTINCT CASE WHEN o.status_id = :completedStatus THEN JSON_UNQUOTE(JSON_EXTRACT(r.camel_cased_registration_data, '$.email')) ELSE NULL END) AS completedCount,
COUNT(DISTINCT CASE WHEN r.checked_in = 1 AND o.status_id = :completedStatus THEN JSON_UNQUOTE(JSON_EXTRACT(r.camel_cased_registration_data, '$.email')) ELSE NULL END) AS checkedInCount
FROM tickets t
LEFT JOIN ticket_sections s ON t.section_id = s.id
JOIN events e ON t.event_id = e.id
LEFT JOIN event_registrations r ON r.ticket_id = t.id
LEFT JOIN event_registration_orders o ON r.order_id = o.id
LEFT JOIN (
SELECT ticket_id, SUM(quantity) AS reservedAmount
FROM ticket_reservations
GROUP BY ticket_id
) rs ON rs.ticket_id = t.id
WHERE e.id = :eventId
GROUP BY t.id
ORDER BY t.created_at DESC
SQL;
$params = [
'eventId'         => $event->getId(),
'initialStatus'   => EventRegistrationOrderStatusEnum::INITIAL->value,
'completedStatus' => EventRegistrationOrderStatusEnum::COMPLETED->value,
];

        return $this->getEntityManager()->getConnection()
            ->executeQuery($sql, $params)
            ->fetchAllAssociative();
    }

---

关于CTE

• CTE(Common Table Expression)
• 语法真的非常强大，在复杂查询中几乎是“结构清晰 + 可复用 + 易读”的利器。

---

✅ CTE（WITH 语法）强大的几点

1. 让复杂 SQL 拆分更清晰

你可以把中间步骤抽出来，不需要嵌套多层子查询，逻辑清楚、语义明确：

WITH active_users AS (
  SELECT id, email FROM users WHERE is_active = 1
),
order_totals AS (
  SELECT user_id, SUM(total_price) AS total FROM orders GROUP BY user_id
)
SELECT 
  u.email, o.total
FROM active_users u
LEFT JOIN order_totals o ON u.id = o.user_id

相比多层嵌套的子查询，这种结构清晰易维护。

---

2. 可以递归（WITH RECURSIVE）——非常适合处理树形结构

WITH RECURSIVE category_tree AS (
  SELECT id, parent_id, name FROM categories WHERE id = 1
  UNION ALL
  SELECT c.id, c.parent_id, c.name
  FROM categories c
  INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

🔍 这对处理无限级分类、组织结构树、评论嵌套等非常强大。

---

3. 可被多次引用，提高复用性和效率

比如你聚合一个统计值，在后面多个 JOIN 或 SELECT 中重复使用，CTE 避免重复写 SQL，而不像子查询只能用一次。

---

4. 比子查询更容易调试和优化

你可以先调试 CTE 中的部分逻辑，逐步构建主查询。对于优化器来说，有些数据库（如 PostgreSQL）还会将 CTE 内联提升性能（MySQL 目前不一定内联）。

---

🧠 小贴士（MySQL 限制）

• MySQL 8.0+ 才支持 CTE。
• CTE 默认不是 materialized 的（MySQL 会尽量 inline）。

* 如果你希望明确“只执行一次”，你可能需要注意不同数据库（如 PostgreSQL、SQL Server 和 MySQL）对 CTE 的优化策略不同。

---

##### ✅ 在 Symfony Doctrine 中用法也很友好

如果你是用：

* `->getConnection()->executeQuery($sql)`：可以直接写 `WITH`。
* Doctrine DQL 不支持 CTE，但可以通过原生 SQL 或用 View、SubQuery 实现类似效果。

---

##### CTE 默认不是 materialized 的（MySQL 会尽量 inline）, 怎么理解?
这个问题非常关键，尤其当你对性能敏感时，**CTE 是否 materialized（物化）** 会直接影响查询的执行效率。

---

##### 什么是 CTE 的 materialized 与 inline？

当你写：

```sql
WITH my_cte AS (
  SELECT ... FROM big_table WHERE condition
)
SELECT * FROM my_cte WHERE another_condition;

数据库有两种执行策略：

策略	说明	优缺点
materialized（物化）	数据库先执行 my_cte，将结果缓存（临时表），然后再用于主查询	✅ 结果复用，避免重复计算 ❌ 对性能有开销（写入临时表）
inline（内联）	数据库把 my_cte 看作“纯粹的子查询”，每用一次就重新执行一次	✅ 避免临时表 ❌ 多次引用会重复计算，影响性能

---

🐬 MySQL 中的行为（重点）

✅ MySQL 8+ 中，默认是 inline 的

即：CTE 不会被 materialized，而是像“语法糖的子查询”，在查询树中每次展开。

WITH my_cte AS (
  SELECT * FROM big_table WHERE col > 100
)
SELECT * FROM my_cte WHERE col < 500;

等价于：

SELECT * FROM (
  SELECT * FROM big_table WHERE col > 100
) AS my_cte
WHERE col < 500;

也就是说：MySQL 不会单独执行 my_cte 一次并缓存结果。

---

🧪 举个例子帮助你理解

假设：

WITH slow_query AS (
  SELECT SLEEP(1) AS delay FROM some_table
)
SELECT * FROM slow_query;

• 如果是 materialized，只会 SLEEP(1) 一次。
• 如果是 inline，你引用多次，就会 SLEEP(1) 多次。

---

🧩 那能不能控制它是否 materialized 呢？

✅ 在 MySQL 8.0.23+ 引入了语法控制：

WITH my_cte AS MATERIALIZED (
  SELECT ...
)

或者：

WITH my_cte AS NOT MATERIALIZED (
  SELECT ...
)

但注意：

• 默认是 NOT MATERIALIZED（即 inline）
• PostgreSQL 默认是 MATERIALIZED，可以手动加 NOT MATERIALIZED 控制（正相反）

---

🔧 小结（MySQL 里的理解）

理解点	说明
默认行为	MySQL 默认不物化（inline）
优点	快速、避免不必要的临时表
缺点	多次引用时可能导致重复计算
控制方式（8.0.23+）	可显式写 AS MATERIALIZED

---

🎯 你该怎么做？

• 一次性引用，inline 足够。
• 多次引用 or 逻辑复杂 or 数据量大 → 用 AS MATERIALIZED 提高性能（8.0.23+）
• 对于写业务复杂统计、递归、去重汇总表时，materialized 更安全。
• 用 EXPLAIN 查看实际执行计划是关键。

---