CODEWITHSUNDEEP
pythonsqlalchemy
tipu
tipu

Reputation: 9604

Permanently filter a SQLAlchemy relationship

I have a User model that belongs to multiple Groups through a GroupMember association model. I use soft deletes, so currently user.groups can include deleted groups. Is there a permanent filter I can apply to a relationship so it does not include the deleted instances? 

class User(Base):
    # ...
    groups = relationship(
        'group',
        secondary=GroupMember.__table__,
        order_by=GroupMember.position
    )

Upvotes: 1

Views: 370

Answers (1)

davidism
davidism

Reputation: 127180

Change the join condition for the relationship by specifying primaryjoin.

groups = relationship(
    Group, GroupMemeber.__table__,
    primaryjoin=lambda: and_(not_(Group.deleted), GroupMemeber.user_id == User.id)
)

Note that this doesn't prevent you from adding deleted groups to a member. SQLAlchemy doesn't know what primaryjoin is doing, it only knows the basic relationship between the models. SQLAlchemy also won't remove associations when the primaryjoin condition is no longer true. In order to see the actual collection of groups associated with a member, it would be useful to have a second all_groups relationship.

Upvotes: 1

Related Questions