从SQLAlchemy关系获取特定列

Question

我需要允许用户在我的应用程序中阻止其他用户。当我想检查某个用户是否被当前（已登录用户）阻止时，就会出现问题。如何检查特定用户是否在当前用户的阻止列表中？我的模型如下：

class User(db.Model):
__tablename__ = 'users'

id = Column(Integer, primary_key=True)
urid = Column(String(50), unique=True)
full_name = Column(String(100))
...

blockedlist = relationship('Blacklist', primaryjoin='Blacklist.user_id==User.id', back_populates='owner')

class Blacklist(db.Model):
__tablename__ = 'blacklist'

id = Column(Integer, primary_key=True)
user_id = Column(Integer, ForeignKey('users.id'))
blocked_id = Column(Integer, ForeignKey('users.id'))
date_blocked = Column(DateTime, default=func.now())

owner = relationship('User', primaryjoin='Blacklist.user_id==User.id', back_populates='blockedlist')
blocked = relationship('User', primaryjoin='Blacklist.blocked_id==User.id')

def __init__(self, user_id, blocked_id):
    self.user_id = user_id
    self.blocked_id = blocked_id

基本上，我想检查一个用户的id是否在当前用户的阻止id列表中

Answer 1

您可以在关系上使用.any，如下所示：

alice = User(urid='alice', full_name='Alice')
bob = User(urid='bob', full_name='Bob')

session.add(Blacklist(owner=alice, blocked=bob))
session.commit()

bob_blocked_alice = (
    session.query(User.blockedlist.any(blocked_id=alice.id))
    .filter(User.id == bob.id)
    .scalar()
)

print('Did Bob block Alice:', bob_blocked_alice)

alice_blocked_bob = (
    session.query(User.blockedlist.any(blocked_id=bob.id))
    .filter(User.id == alice.id)
    .scalar()
)

print('Did Alice block Bob:', alice_blocked_bob)

另外，可以使用foreign_keys参数简化关系：

^{pr2}$