sqlalchemy：如何通过一个查询连接多个表？

Question

我有以下用SQLAlchemy映射的类：

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 = "user@email.com"：

email | name | document_name | document_readAllowed | document_writeAllowed

我该怎么用一个查询请求来实现这个呢？下面的代码对我来说不管用：

result = session.query(User, Document, DocumentPermission).filter_by(email = "user@email.com").all()

谢谢，

Answer 1

一个好的做法是为权限设置一些关系和一个主键（实际上，通常为所有东西设置整数类型的主键都是个好主意，不过随你喜欢）：

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_email = Column(String, ForeignKey("users.email"))
    author = relation(User, backref='documents')

class DocumentsPermissions(Base):
    __tablename__ = "documents_permissions"
    id = Column(Integer, primary_key=True)
    readAllowed = Column(Boolean)
    writeAllowed = Column(Boolean)
    document_name = Column(String, ForeignKey("documents.name"))
    document = relation(Document, backref = 'permissions')

然后可以用简单的查询和连接来获取数据：

query = session.query(User, Document, DocumentsPermissions).join(Document).join(DocumentsPermissions)

Answer 2

正如@letitbee所说，给表格分配主键并正确定义它们之间的关系是个好习惯，这样可以让你在使用ORM（对象关系映射）时查询得更顺利。话说回来……

如果你想写一个类似于：

SELECT
    user.email,
    user.name,
    document.name,
    documents_permissions.readAllowed,
    documents_permissions.writeAllowed
FROM
    user, document, documents_permissions
WHERE
    user.email = "user@email.com";

的查询，那么你可以试试这样的写法：

session.query(
    User, 
    Document, 
    DocumentsPermissions
).filter(
    User.email == Document.author
).filter(
    Document.name == DocumentsPermissions.document
).filter(
    User.email == "user@email.com"
).all()

---

如果你想做的是类似于：

SELECT 'all the columns'
FROM user
JOIN document ON document.author_id = user.id AND document.author == User.email
JOIN document_permissions ON document_permissions.document_id = document.id AND document_permissions.document = document.name

的事情，那么你可以这样写：

session.query(
    User
).join(
    Document
).join(
    DocumentsPermissions
).filter(
    User.email == "user@email.com"
).all()

有一点需要注意……

query.join(Address, User.id==Address.user_id) # explicit condition
query.join(User.addresses)                    # specify relationship from left to right
query.join(Address, User.addresses)           # same, with explicit target
query.join('addresses')                       # same, using a string

想了解更多信息，可以访问 这个文档。

Answer 3

试试这个

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