我有三个被定义为以下内容的表:
class User(Base):
__tablename__ = 'users'
id = Column(Integer(10), primary_key=True)
firstname = Column(String(64))
surname = Column(String(64))
class SWMS(Base):
__tablename__ = 'swms'
id = Column(Integer(10), primary_key=True)
owner_id = Column(Integer(10), ForeignKey('users.id', ondelete='CASCADE'))
filename = Column(String(255))
swmowner = relationship('User', backref=backref('users'))
class SWM_perms(Base):
__tablename__ = 'swm_perms'
id = Column(Integer(10), primary_key=True)
swm_id = Column(Integer(10), ForeignKey('swms.id', ondelete='CASCADE'))
user_id = Column(Integer(10), ForeignKey('users.id', ondelete='CASCADE'))
swm = relationship('SWMS', backref=backref('swms'))
swmuser = relationship('User', backref=backref('swmusers'))
基本上,SWMS表是一个文档信息表,其中owner_id定义了创建文档的用户。SWM_perms是一个表,它将文档ID映射到用户ID,以定义哪些用户可以查看该文档。
为了生成一个包含所有由用户拥有或可被用户查看的文档的表,在SQL中我会这样做:
select owner_id, users.firstname, users.surname, filename
from swms, swm_perms, users
where users.id=swms.owner_id and
((swms.id=swm_perms.swm_id and swm_perms.user_id = 27) or (owner_id = 27));
你如何在SQLAlchemy中定义这个查询?我熟悉or_()函数,但我尝试的变体没有生成正确的对象。