Agfdhyk

My abstraction is: Book has only one User (as author), and a many to many relationship with User (as collaborators):

class UserBook(Base):

    __tablename__ = 'user_book'

    user_id = Column(String(512), ForeignKey('user.id'), primary_key=True)

    book_id = Column(String(512), ForeignKey('book.id'), primary_key=True)

    collaboration_type = Column(Integer, default=1)

    book = relationship('Book', back_populates='users')

    user = relationship('User', back_populates='books')





class User(Base):

    __tablename__ = 'user'



    id = Column(String(512), primary_key=True)

    books = relationship('UserBook', back_populates='user')





class Book(Base):

    __tablename__ = 'book'



    id = Column(String(512), primary_key=True)

    author = Column(String(512), ForeignKey('user.id'), nullable=False)

    collaborators = relationship('UserBook', back_populates='book')

    name = Column(String(512))

    description = Column(String(512), default='')

I'm looking to select all books where I'm an author or I'm a collaborator using SQLAlchemy declarative queries if possible.

I was able to make the join with collaborator, but I'm not sure how to or with the author filter.

You can use the or_ operator with your filter to get all the books where the current user is an author or a collaborator. For example:

from sqlalchemy import or_





curr_id = 'current_user_id'



session.query(Book).

    outerjoin(UserBook).

    filter(or_(Book.author == curr_id, UserBook.user_id == curr_id)).

    all()