flask - Sqlalchemy - 加入后分组

Question

我有以下模型。

class Customer(db.Model):
id = db.Column(db.Integer, db.ForeignKey('accounts.id'), primary_key=True)
firstname = db.Column(db.Unicode(128), index=True)
lastname = db.Column(db.Unicode(128), index=True)
account = db.relationship('Account',
                backref=db.backref('customer',
                            uselist=False,
                            cascade=DEFAULT_WITH_DELETE_CASCADE),
                single_parent=True,
                cascade=DEFAULT_WITH_DELETE_CASCADE,
                foreign_keys=[id])

我想在加载客户时对帐户表进行 eagarloading。还有一些过滤器和连接可应用于查询，然后希望按 Customer.id 对结果进行分组，以便为​​客户获得唯一的结果。

query = Customer.query.join(Customer.account).options(joinedload(Customer.account))
query = query.order_by(Account.registered_on.desc()).group_by(Customer.id, Account.id)

但是当我在 joinloaded 后使用 group_by 子句时，我得到错误。执行joinloaded后如何使用group_by？

ProgrammingError: (ProgrammingError) column "accounts_1.id" must appear in the GROUP BY clause or be used in an aggregate function

Answer 1

我认为您不能按您加入的表中的列进行分组(使用 joinedload )。来自 SQLAlchemy 文档:The joins produced by orm.joinedload() are anonymously aliased. The criteria by which the join proceeds cannot be modified, nor can the Query refer to these joins in any way, including ordering. ( http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html )

您需要使用常规 join功能在这里然后group_by