sqlalchemy : search in a complicated relational postgresql database

79 Views Asked by At

I have 3 tables

class Users(base):
    __tablename__ = 'users'
    id = Column('id', BIGINT, primary_key=True, autoincrement=False)
    firstname = Column('firstname', VARCHAR(32))

class Blocks(base):
    __tablename__ = 'blocks'
    id = Column('id', INTEGER, primary_key=True)
    blocker = Column(BIGINT, ForeignKey("users.id"))
    blocked = Column(BIGINT, ForeignKey("users.id"))
    BlockerRel = relationship("Users", foreign_keys=[blocker])
    BlockedRel = relationship("Users", foreign_keys=[blocked])

class Tods(base):
    __tablename__ = 'tods'
    id = Column('id', INTEGER, primary_key=True)
    fu_id = Column(BIGINT, ForeignKey("users.id"))
    su_id = Column(BIGINT, ForeignKey("users.id"), default=None)
    FuId = relationship("Users", foreign_keys=[fu_id])
    SuId = relationship("Users", foreign_keys=[su_id])

First I insert user id in Tods.fu_id and Tods.su_id is empty.

And the second user (Tods.su_id) has to select a row where he/she didn't block the Tods.fu_id user. like a row below:

id | blocker | blocked
---------------------
 1 | su_id   |  fu_id

or Tods.fu_id didn't block the second user:

id | blocker | blocked
----------------------
 1 |  su_id  |  fu_id

how can i do it in one request? (I think I need to use join but I don't know how to do it in this situation)

0

There are 0 best solutions below