计算sqlalchemy模型中的相关项目 [英] Count related items in a sqlalchemy model

问题描述

我试图计算各自类别中的项目数，最后得到一个可以在Jinja模板中迭代的集合.我的最终输出是这样的:

I'm trying to count the number of items in their respective categories and end up with a collection that I can iterate through in a jinja template. My final output is something like:

category1，5

category1, 5

category2，10

category2, 10

category3，0

category3, 0

零项目的情况很重要.

我的模特是:

class Category(Base):

    __tablename__ = 'category'

    id = Column(Integer, primary_key=True)
    name = Column(String(80), unique=True)
    user_id = Column(Integer, ForeignKey('user.id'))
    user = relationship(User)


class Item(Base):

    __tablename__ = 'item'

    id = Column(Integer, primary_key=True)
    name = Column(String(80))
    description = Column(String(500))
    category_id = Column(Integer, ForeignKey('category.id'))
    category = relationship(Category)
    user_id = Column(Integer, ForeignKey('user.id'))
    user = relationship(User)
    date_added = Column(DateTime, default=datetime.datetime.now)

我已被指示为 Stackoverflow:在SQLAlchemy中计数关系带我去查询

I have been kindly pointed in the direction of Stackoverflow: Counting relationships in SQLAlchemy, which led me to the query

count_categories = db_session.query(Category.name, func.count(Item.id)).join(Item.category).group_by(Category.id).all()

这几乎是正确的，但它不能处理零大小写.当一个类别有零个项目时，我仍然需要查询返回的类别.

Which is almost correct, but it does not handle the zero case. When a category has zero items, I still need the category returned by the query.

任何帮助，不胜感激.

推荐答案

实际上，我已经弄清楚了:

Actually, I've figured it out:

count_categories = db_session.query(
        Category.name, func.count(Item.id)).outerjoin(
        Item).group_by(Category.id).all()

请参见有关联接的SQLAlchemy文档

这篇关于计算sqlalchemy模型中的相关项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！