python - SQLAlchemy如何进行group_by关系？

Question

以下是模型:

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')>,])>]

换句话说，现在它可以表示为:当前:

templates = [
    {
        "id": 123,
        "name": "Test",
        "mail_pieces_count": 123,
        "user": {
            "id": 1212,
            "first_name": "Some name",
            "last_name": "Some lastname"
        }
    },
    {
        "id": 456,
        "name": "Test2",
        "mail_pieces_count": 456,
        "user": {
            "id": 1212,
            "first_name": "Some name",
            "last_name": "Some lastname"
        }
    }
]

预期:

users = [
    {
        "id": 1212,
        "first_name": "Some name",
        "last_name": "Some lastname",
        "templates": [
        {
            "id": 123,
            "name": "Test",
            "mail_pieces_count": 123
        },
        {
            "id": 456,
            "name": "Test2",
            "mail_pieces_count": 456
        },]
    },
]

Answer 1

当前输出原因:

您使用模板类构建查询:

self.session.query(Template, stmt.c.mail_pieces_count)

这是您获得 Template 实例而不是 User 的主要原因。下面只是一个如何获得预期结果的示例。

Note! I just try to explain how it works. My answer isn't related to optimization, performance etc.

# I skipped filters...
stmt = (session.query(MailPiece.template_id, func.count('*')
        .label('mail_pieces_count'))
        .group_by(MailPiece.template_id).subquery())
# to tie templates with upper subquery
stmt2 = (session.query(Template.created_by_id, stmt.c.mail_pieces_count)
         .join(stmt, Template.id == stmt.c.template_id)
         .subquery())
# User query - to tie template id with user id
query = session.query(User, stmt2.c.mail_pieces_count).join(
    stmt2,
    # you can add additional conditions into JOIN ON...
    and_(User.id == stmt2.c.created_by_id, User.receive_reports.is_(True))
)

for result in query:
    print("count: %s" % result.mail_pieces_count)
    print("user: %s" % result.User)
    print("templates: %s" % result.User.templates)

JFYI。如果我们查看控制台，您会发现 alchemy 每次迭代都会执行 1 次查询:

### one more select when you use result.User.templates
2019-02-11 13:02:24,702 INFO sqlalchemy.engine.base.Engine SELECT templates.id AS templates_id, templates.name AS templates_name, templates.created_by_id AS templates_created_by_id 
FROM templates 
WHERE %(param_1)s = templates.created_by_id

您可以将模板添加到查询中以避免这种情况，但在这种情况下，您将获得等于模板数量的记录数量(而不是用户):

stmt2 = (session.query(Template, stmt.c.mail_pieces_count)
         .join(stmt, Template.id == stmt.c.template_id)
         .subquery())
# select User and Template 
query = session.query(User, Template, stmt2.c.mail_pieces_count).join(
    stmt2,
    and_(User.id == stmt2.c.created_by_id)
)

for result in query.all():
    print("user: %s" % result.User)
    print("template: %s" % result.Template)

总结一下，如果您需要获取类的实例作为结果，则需要使用特定类:

session.query(ExpectedClass).other_methods()...

希望这有帮助。