使用ActiveRecord统计分类及所有子分类的帖子总数

我项目里有两张表：posts和post_categories。posts表的每篇帖子都有post_category_id字段，作为外键关联post_categories.id；post_categories表有自引用的parent_id字段，关联自身的id（parent_id为0代表主菜单）。

当前网站的帖子分类页面只显示当前分类下的帖子，但我需要统计该分类及其所有子分类下的帖子总数，同时统计包含的分类数量（包括自身）。

表结构示例 #

post_categories表：

+-----+---------------+-----------+
| id  | title         | parent_id |
+-----+---------------+-----------+
| 1   | 机械类        | 0         |
| 2   | 交通工具      | 1         |
| 3   | 工业设备      | 1         |
| 4   | 民用汽车      | 2         |
| 5   | 赛车          | 2         |
+-----+---------------+-----------+

posts表：

+-----+--------+------------------+
| id  | title  | post_category_id |
+-----+--------+------------------+
| 1   | 拉力赛  | 5                |
| 2   | F1赛事 | 5                |
| 3   | 厢式货车| 4                |
| 4   | 卡车    | 4                |
+-----+--------+------------------+

当前问题 #

如果某个分类下没有直接关联的帖子，哪怕它有多个子分类且子分类有大量帖子，该分类的帖子数仍显示为0。我试过MySQL的RECURSIVE但没成功，找不到关联两张表的递归查询示例，而且分类层级没有上限，没法固定层数写死代码。

需求示例（以分类「交通工具」id=2为例） #

1. 统计post_category_id=2的帖子数（本例为0）
2. 统计post_category_id属于(4,5)的帖子数（4、5的parent_id是2）
3. 求和得到总帖子数，同时统计包含的分类数量（本例是3：2、4、5），最终要输出类似交通工具 (包含4篇帖子，共3个分类)的内容

可以用MySQL的**递归CTE（Common Table Expression）**先获取目标分类及其所有子分类的ID集合，再关联posts表统计总数，同时统计分类数量。

完整查询语句 #

WITH RECURSIVE category_tree AS (
    -- 起始节点：目标分类本身
    SELECT id, title FROM post_categories WHERE id = 2
    UNION ALL
    -- 递归获取所有子分类
    SELECT pc.id, pc.title
    FROM post_categories pc
    JOIN category_tree ct ON pc.parent_id = ct.id
)
SELECT
    ct.title AS category_title,
    COUNT(p.id) AS total_posts,
    COUNT(DISTINCT ct.id) AS total_categories
FROM category_tree ct
LEFT JOIN posts p ON ct.id = p.post_category_id
GROUP BY ct.title;

代码解释 #

1. 递归CTE category_tree：
   • 锚点查询：先获取目标分类（这里是id=2）的基础信息
   • 递归查询：不断关联post_categories表，获取所有父ID属于已查询分类的子分类，直到没有子分类为止
2. 统计关联：
   • 用LEFT JOIN关联posts表，确保即使分类下没有帖子也能返回0
   • COUNT(p.id)统计所有关联的帖子总数
   • COUNT(DISTINCT ct.id)统计当前分类及其所有子分类的数量

动态适配任意分类 #

如果要查询任意分类，只需要修改锚点查询中的id = 2为目标分类ID即可。比如要查「机械类」id=1，就改成WHERE id = 1，会返回机械类及其所有子分类的帖子总数和分类数量。

输出结果示例（针对id=2） #

+----------------+-------------+------------------+
| category_title | total_posts | total_categories |
+----------------+-------------+------------------+
| 交通工具       | 4           | 3                |
+----------------+-------------+------------------+

内容的提问来源于stack exchange，提问作者Kane