Dfyjkt

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