PostgreSQL+SQLAlchemy实现category表item_count自动更新

PostgreSQL的生成列确实不允许在表达式中使用跨表子查询，所以你需要换用数据库触发器或者视图来实现需求，以下是具体方案：

方案1：数据库触发器（推荐，持久化存储总和） #

通过触发器监听子分类表的增删改操作，自动更新对应父分类的item_count。

1. 创建触发器函数 #

这个函数负责在子分类数据变化时，计算并更新父分类的总和：

CREATE OR REPLACE FUNCTION update_category_item_count()
RETURNS TRIGGER AS $$
BEGIN
    -- 插入/更新子分类时，更新对应父分类
    IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
        UPDATE category
        SET item_count = COALESCE(
            (SELECT SUM(item_count) FROM subcategory WHERE parent_id = NEW.parent_id),
            0
        )
        WHERE id = NEW.parent_id;
    END IF;

    -- 删除子分类时，更新原父分类
    IF TG_OP = 'DELETE' THEN
        UPDATE category
        SET item_count = COALESCE(
            (SELECT SUM(item_count) FROM subcategory WHERE parent_id = OLD.parent_id),
            0
        )
        WHERE id = OLD.parent_id;
    END IF;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

2. 绑定触发器到子分类表 #

让触发器在子分类表发生增删改后自动执行上面的函数：

CREATE TRIGGER trigger_subcategory_change
AFTER INSERT OR UPDATE OR DELETE ON subcategory
FOR EACH ROW EXECUTE FUNCTION update_category_item_count();

方案2：创建视图（无需存储，实时计算） #

如果不需要把总和持久化存储在category表，只是查询时获取实时数据，可以创建视图：

CREATE VIEW category_with_total AS
SELECT
    c.id,
    COALESCE(SUM(s.item_count), 0) AS item_count
FROM category c
LEFT JOIN subcategory s ON c.id = s.parent_id
GROUP BY c.id;

查询这个视图就能得到每个分类的实时子项总和，无需维护存储列。

在SQLAlchemy中实现触发器 #

如果用ORM管理表结构，可以通过DDL语句在表创建后自动添加触发器：

from sqlalchemy import create_engine, Column, Integer, ForeignKey, func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.schema import DDL
from sqlalchemy import event

Base = declarative_base()

class Category(Base):
    __tablename__ = 'category'
    id = Column(Integer, primary_key=True)
    item_count = Column(Integer, default=0)

class Subcategory(Base):
    __tablename__ = 'subcategory'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('category.id'))
    item_count = Column(Integer)

# 注册触发器函数的DDL
trigger_func_ddl = DDL("""
CREATE OR REPLACE FUNCTION update_category_item_count()
RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
        UPDATE category
        SET item_count = COALESCE(
            (SELECT SUM(item_count) FROM subcategory WHERE parent_id = NEW.parent_id),
            0
        )
        WHERE id = NEW.parent_id;
    END IF;

    IF TG_OP = 'DELETE' THEN
        UPDATE category
        SET item_count = COALESCE(
            (SELECT SUM(item_count) FROM subcategory WHERE parent_id = OLD.parent_id),
            0
        )
        WHERE id = OLD.parent_id;
    END IF;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;
""")

# 注册触发器的DDL
trigger_ddl = DDL("""
CREATE TRIGGER trigger_subcategory_change
AFTER INSERT OR UPDATE OR DELETE ON subcategory
FOR EACH ROW EXECUTE FUNCTION update_category_item_count();
""")

# 绑定到Subcategory表的创建事件
event.listen(Subcategory.__table__, 'after_create', trigger_func_ddl)
event.listen(Subcategory.__table__, 'after_create', trigger_ddl)

# 初始化数据库
engine = create_engine('postgresql://user:password@host/dbname')
Base.metadata.create_all(engine)

额外提示 #

• 触发器是数据库层面的控制，不管通过ORM还是直接执行SQL操作子分类，都会自动更新父分类，可靠性更高。
• 若需批量操作子分类，可先禁用触发器，完成后手动执行一次全局更新：

UPDATE category c
SET item_count = COALESCE(
    (SELECT SUM(item_count) FROM subcategory s WHERE s.parent_id = c.id),
    0
);

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