sqlalchemy:如何通过一个查询连接多个表? [英] sqlalchemy: how to join several tables by one query?

问题描述

我有以下 SQLAlchemy 映射类:

I have the following SQLAlchemy mapped classes:

class User(Base):
    __tablename__ = 'users'
    email = Column(String, primary_key=True)
    name = Column(String)

class Document(Base):
    __tablename__ = "documents"
    name = Column(String, primary_key=True)
    author = Column(String, ForeignKey("users.email"))

class DocumentsPermissions(Base):
    __tablename__ = "documents_permissions"
    readAllowed = Column(Boolean)
    writeAllowed = Column(Boolean)

    document = Column(String, ForeignKey("documents.name"))

我需要为 user.email = "[e-mail]" 获取一个这样的表格:

I need to get a table like this for user.email = "[e-mail]":

email | name | document_name | document_readAllowed | document_writeAllowed

如何使用 SQLAlchemy 的一个查询请求来实现?下面的代码对我不起作用:

How can it be made using one query request for SQLAlchemy? The code below does not work for me:

result = session.query(User, Document, DocumentPermission).filter_by(email = "[e-mail]").all()

谢谢，

推荐答案

试试这个

q = Session.query(
         User, Document, DocumentPermissions,
    ).filter(
         User.email == Document.author,
    ).filter(
         Document.name == DocumentPermissions.document,
    ).filter(
        User.email == 'someemail',
    ).all()

这篇关于sqlalchemy:如何通过一个查询连接多个表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！