以下是模型:class User(Base): __tablename__ = 'users' id = Column(CHAR, primary_key=True) first_name = Column(CHAR) last_name = Column(CHAR) email = Column(CHAR) receive_reports = Column(Boolean)class MailPiece(Base): __tablename__ = 'mail_pieces' id = Column(CHAR, primary_key=True) created_at = Column(DateTime) template_id = Column(CHAR, ForeignKey('templates.id'))class Template(Base): __tablename__ = 'templates' id = Column(CHAR, primary_key=True) name = Column(CHAR) created_by_id = Column(CHAR, ForeignKey('users.id')) user = relationship(User, backref='templates')我想向用戶發送報告:他們發送了哪些模板以及為每個模板發送了多少郵件。我寫的代碼: stmt = self.session.query(MailPiece.template_id, func.count('*') .label('mail_pieces_count')).filter( MailPiece.created_at > day_ago, MailPiece.created_at < now, ).group_by(MailPiece.template_id).subquery() query = self.session.query(Template, stmt.c.mail_pieces_count).\ filter(Template.user.has(receive_reports=True)).\ join(stmt, Template.id == stmt.c.template_id)但這有點不是我想要的。我得到了按模板分組的結果,但需要按用戶分組的列表,每個用戶都有模板。因此,通過這種方式,我可以遍歷用戶列表并向每個用戶發送帶有摘要的報告。當前結果:[<Template(id='123', name='Test', mail_pieces_count='123', user=<User(id=1212, first_name='Some name', last_name='Some lastname')>)>, <Template(id='456', name='Test2', mail_pieces_count='456', user=<User(id=1212, first_name='Some name', last_name='Some lastname')>)>]預期結果:[<User(id=1212, first_name='Some name', last_name='Some lastname, templates=[<Template(id='123', name='Test', mail_pieces_count='123')>, <Template(id='456', name='Test2', mail_pieces_count='456')>,])>]
SQLAlchemy 如何分組關系?
慕田峪7331174
2021-11-02 17:15:23