Override Relationship Behaviour In Sqlalchemy

Say I have three tables in a declarative fashion, Parent, Child, and Pet, in such way that Parent has a many-to-many relationship with both Child and Pet Child has a one-to-many r

Solution 1:

For parent.pets.all(), I think you could use children as a secondary join condition, and treat it as an associative entity or junction table.

It depends on your tables but that would look something like:

Parent.pets = relationship(
    Pet,
    backref='parent'
    primaryjoin=Pet.child_id == Child.id,
    secondaryjoin=Child.parent_id == Parent.id
)

You could also pretty reasonably make a backref parent if you so choose - this would let you access both parent_a.pets as well as pet_a.parent.

Solution 2:

Using the same answer from the sqlalchemy mailing list, this can be achieved using event listeners, which are called before an append or remove is called on the object in the first parameter.

@db.event.listens_for(Parent.children, 'append')def_append_children(parent, child, initiator):
    # appends also the pets bound to the child that the # is being appended to the Parent

    parent.pets.extend(child.pets.all())

    # at the end of this call, executes# parent.children.append(child)