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

我有以下 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

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

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

谢谢,

250109 次浏览
小开

试试这个

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

一种好的风格是为权限设置一些关系和主键(实际上，通常为所有内容设置 整数主键是一种好的风格，但无论如何) :

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)
小开

扩展 Abdul 的答案，你可以得到一个 KeyedTuple，而不是一个离散的行集合，通过连接列:

q = Session.query(*User.__table__.columns + Document.__table__.columns).\
select_from(User).\
join(Document, User.email == Document.author).\
filter(User.email == 'someemail').all()
小开

此函数将生成所需的表作为元组列表。

def get_documents_by_user_email(email):
query = session.query(
User.email,
User.name,
Document.name,
DocumentsPermissions.readAllowed,
DocumentsPermissions.writeAllowed,
)
join_query = query.join(Document).join(DocumentsPermissions)


return join_query.filter(User.email == email).all()


user_docs = get_documents_by_user_email(email)
小开

正如@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

欲了解更多信息，请访问 医生。


提交答案