[Python] SQLAlchemy join & filter

I am new to SQLAlchemy and develop a small FastAPI application using SQLAlchemy and SQLite. I have several tables, it's just a small example:

USERS:

class UserEntity(BaseEntity):
"""
User's table
"""
__tablename__ = "users"

id = Column(Integer, primary_key=True, autoincrement=True, unique=True, nullable=False)
username = Column(String, unique=True, index=True, nullable=False)
password = Column(String, nullable=False)


SETS:

class SetEntity(BaseEntity):
"""
Sets
"""
__tablename__ = "sets"

id = Column(Integer, primary_key=True, autoincrement=True, unique=True, nullable=False)
name = Column(String, nullable=False)
ownerId = Column("owner_id", Integer, ForeignKey("users.id"), nullable=False, comment="Creator's id")

owner = relationship("UserEntity", backref="sets")
members = relationship("UserEntity", secondary=SetMemberEntity)


And many-to-many table:

SetMemberEntity = Table(
"set_members",
BaseEntity.metadata,
Column("id", Integer, primary_key=True),
Column("set_id", Integer, ForeignKey("sets.id")),
Column("member_id", Integer, ForeignKey("users.id"))
)


So user's process is:

1. User creates a set (which inserts new row to sets)
2. User adds another users (by id) who have access to the set created in step 1.

For example:

USERS:

id username password
1 Alice 3c9...
2 Bob bb4...
3 Trent 304...

SETS:

id name owner_id
1 Alice's set 1
2 Bob's set 2

SETS_MEMBERS:

id set_id member_id
1 1 2
1 1 3
1 2 3

I need to write query filter which returns sets, corresponding conditions:

def getAvailableSets(session: Session, userId: int) -> list[SetEntity]:
return session.query(SetEntity).join(...).filter(_or(SetEntity.ownerId == userId, ...))

1. if SetEntity.ownerId == userId - OK
2. if userId in list(map(lambda member: member.id, SetEntity.members)) - I don't know how to solve it and write in filter function

For example:

• getAvailableSets(session, 1) -> [SetEntity(id=1, ...)]
• getAvailableSets(session, 3) -> [SetEntity(id=1, ...), SetEntity(id=2, ...)]

Is there any option to do this?

UPD. Something like that in SQL:

select s.* from sets s
left join set_members sm on s.id = sm.set_id
where s.owner_id = {user_id} or sm.member_id = {user_id};

Continue reading...