override relationship behaviour in sqlalchemy

Question

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 relationship with Pet

The code for them is (using Flask-SQLAlchemy, although I believe the solution lives in the realm of SQLAlchemy rather than in Flask).

class Parent(db.Model):
    __tablename__ = 'parents'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64))

    # many to many relationship between parent and children
    # my case allows for a children to have many parents. Don't ask.
    children = db.relationship('Child',
                           secondary=parents_children_relationship,
                           backref=db.backref('parents', lazy='dynamic'),
                           lazy='dynamic')

    # many to many relationship between parents and pets
    pets = db.relationship('Pet',
                             secondary=users_pets_relationship,
                             backref=db.backref('parents', lazy='dynamic'), #
                             lazy='dynamic')

# many to many relationship between parents and children
parents_children_relationship = db.Table('parents_children_relationship',
    db.Column('parent_id', db.Integer, db.ForeignKey('parents.id')),
    db.Column('child_id', db.Integer, db.ForeignKey('children.id')),
    UniqueConstraint('parent_id', 'child_id'))

# many to many relationship between User and Pet 
users_pets_relationship = db.Table('users_pets_relationship', 
    db.Column('parent_id', db.Integer, db.ForeignKey('parents.id')), 
    db.Column('pet_id', db.Integer, db.ForeignKey('pets.id')),
    UniqueConstraint('parent_id', 'pet_id'))

class Child(db.Model):
    __tablename__ = 'children'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64))
    # parents = <backref relationship with User model>

    # one to many relationship with pets
    pets = db.relationship('Pet', backref='child', lazy='dynamic')


class Pet(db.Model):
    __tablename__ = 'pets'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64))
    # child = backref relationship with cities
    child_id = db.Column(db.Integer, db.ForeignKey('children.id'), nullable=True)
    # parents = <relationship backref from User>

I would like to do something like this

parent_a = Parent()    
child_a = Child()
pet_a = Pet()

I can then do this

parent_a.children.append(child_a)
# commit/persist data
parent_a.children.all() # [child_a]

I would like to achieve something like this

child_a.pets.append(pet_a)
parent_a.children.append(child_a)
# commit/persist data
parent_a.children.all() # [child_a]
parent_a.pets.all() # [pet_a], because pet_a gets 
                    # automatically added to parent using some sorcery
                    # like for child in parent_a.children.all():
                    #     parent.pets.append(child.pets.all())
                    # or something like that.

I can achieve this with a method in the Parent object like add_child_and_its_pets(), but I would like to override the way relationship works, so I don't need to override other modules that may benefit from this behaviour, like Flask-Admin for instance.

Basically how should I override the backref.append method or the relationship.append method to also append other objects from other relationships at call time i.e. on the python side? How should I override the remove methods as well?

Answer 1

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)