I have three tables which are defined as:

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'))

Essentially, the SWMS table is a table of document info where the owner_id defines the user who created the document. SWM_perms is a table that has a mapping of document id to user id - to define which users are allowed to see the document.

To produce a table of all documents which are either 1) owned by the user or 2) are viewable by the user, in SQL I would do:

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));

How would you define this query in sqlalchemy? I am familiar with the or_() function but the variants I am trying do not generate the correct objects.

sql sqlalchemy
Edit
Report