Hello, thanks again for your help. I'm not sure I understand what you said totally, and I believe this is the most simple MCVE I can provide.
My local tests use postgresql, but I'm setting an in-memory sqlite3 engine here. I'm not fond of the differences between two backends, but the tests run without problems. import sqlalchemy as sa from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() # many to many relationship between parents and children parents_children_relationship = sa.Table('parents_children_relationship', Base.metadata, sa.Column('parent_id', sa.Integer, sa.ForeignKey('parents.id')), sa.Column('child_id', sa.Integer, sa.ForeignKey('children.id')), sa.UniqueConstraint('parent_id', 'child_id')) # many to many relationship between User and Pet parents_pets_relationship = sa.Table('parents_pets_relationship', Base.metadata, sa.Column('parent_id', sa.Integer, sa.ForeignKey('parents.id')), sa.Column('pet_id', sa.Integer, sa.ForeignKey('pets.id')), sa.UniqueConstraint('parent_id', 'pet_id')) class Parent(Base): __tablename__ = 'parents' id = sa.Column(sa.Integer, primary_key=True) name = sa.Column(sa.String(64)) # many to many relationship between parent and children # my case allows for a children to have many parents. Don't ask. children = sa.orm.relationship('Child', secondary=parents_children_relationship, backref=sa.orm.backref('parents', lazy='dynamic' ), lazy='dynamic') # many to many relationship between parents and pets pets = sa.orm.relationship('Pet', secondary=parents_pets_relationship, backref=sa.orm.backref('parents', lazy='dynamic' ), # lazy='dynamic') def __repr__(self): return '<Parent (name=%r)>' % (self.name) class Child(Base): __tablename__ = 'children' id = sa.Column(sa.Integer, primary_key=True) name = sa.Column(sa.String(64)) # parents = <backref relationship with User model> # one to many relationship with pets pets = sa.orm.relationship('Pet', backref='child', lazy='dynamic') def __repr__(self): return '<Child (name=%r)>' % (self.name) class Pet(Base): __tablename__ = 'pets' id = sa.Column(sa.Integer, primary_key=True) name = sa.Column(sa.String(64)) # child = backref relationship with cities child_id = sa.Column(sa.Integer, sa.ForeignKey('children.id'), nullable= True) # parents = <relationship backref from User> def __repr__(self): return '<Pet (name=%r)>' % (self.name) @sa.event.listens_for(Parent.children, 'append') def _append_children(parent, child, initiator): """ If a new child is appended to the parent, this listener will also add the pets bound to the child being bound to the parent. """ # appends also the pets bound to the child that the # parent is being appended to parent.pets.extend(child.pets.all()) @sa.event.listens_for(Parent.children, 'remove') def _remove_children(parent, child, initiator, *args, **kwargs): """ If a child is removed from the parent, this listener will also remove only remove_single_pet --> <Pet> """ remove_single_pet = kwargs.get('remove_single_pet', None) if remove_single_pet is not None: parent.pets.remove(remove_single_pet) else: # removes every pet for pet in child.pets: parent.pets.remove(pet) @sa.event.listens_for(Parent.pets, 'remove') def _remove_pets(parent, pet, initiator, *args, **kwargs): """ If a pet is removed from the parent, and the parent also is related to the child that has access to that pet, then * removes relationship with the child, and * keeps relationship with the remaining pets, except the one that was removed """ if pet.child in parent.children.all(): remove_single_pet = pet _remove_children(parent, pet.child, initiator, remove_single_pet) #### test ### import unittest from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker class BasicTestModelCase(unittest.TestCase): def setUp(self): e = create_engine("sqlite://", echo=True) Base.metadata.create_all(e) Session = sessionmaker(bind=e) self.session = Session() def tearDown(self): # Base.metadata.drop_all() pass def test_child_pet_relationship_on_parents_combined(self): """ Test that a parent can be hold children and pets that don't belong necessary to the child, given the behaviour tested in the previous test. """ # create new parent test_parent = Parent(name='test_parent') child1 = Child(id=1, name='FakeChild1') child2 = Child(id=2, name='FakeChild2') pet1 = Pet(id=1, name='FakePet1', child_id=1) pet2 = Pet(id=2, name='FakePet2', child_id=2) pet3 = Pet(id=3, name='FakePet3', child_id=1) self.session.add(test_parent) self.session.add(child1) self.session.add(child2) self.session.add(pet1) self.session.add(pet2) self.session.add(pet3) self.session.commit() # add parent to the child child1.parents.append(test_parent) self.session.add(child1) self.session.commit() # add parent to the child pet2.parents.append(test_parent) # persist changes in the db self.session.add(pet2) self.session.commit() print(test_parent.pets.all()) print(child2.pets.all()) # check that previous relationships are intact self.assertTrue(child1.pets.all() == [pet1, pet3]) self.assertTrue(child2.pets.all() == [pet2]) # resultant elements should be only child1, its pets and the single Pet self.assertTrue(test_parent.children.all() == [child1]) self.assertTrue(test_parent.pets.all() == [pet1, pet2, pet3]) # remove child from parent pet3.parents.remove(test_parent) ## ERROR here # resultant elements should be remaining pets, and no child self.assertTrue(test_parent.children.all() == []) self.assertTrue(test_parent.pets.all() == [pet1, pet2]) # pet2 was not touched, # but pet1 should remain since only # pet3 was removed # child1 should be also removed since # relationship is unbalanced, i.e. # user can't have access to a child if it # does not have access to all of the child's pets if __name__ == '__main__': # run tests unittest.main() Am Mittwoch, 2. Mai 2018 11:40:14 UTC-3 schrieb Mike Bayer: > > On Wed, May 2, 2018 at 10:14 AM, Diego Quintana <daqui...@gmail.com > <javascript:>> wrote: > > This worked. > > > > I'm trying to achieve some rather tricky behaviour, where > > > > Adding a children to some parent will also add the child's pets to the > > parent > > Removing a children from some parent will also remove every current > > relationship that the Parent has with such pet > > If upon removal of a pet from a Parent, there is a Pet.child that is > also in > > Parent.children, > > > > remove that Child from Parent, but keep existing relationships in > > Parent.pets except the pet that is being removed > > else only remove the pet from the parent > > > > > > Some code I'm using for this is > > > > @db.event.listens_for(Parent.children, 'append') > > def _append_children(parent, child, initiator): > > """ > > If a new child is appended to the parent, this listener > > will also add the pets bound to the child being bound to the parent. > > """ > > # appends also the pets bound to the child that the > > # parent is being appended to > > parent.pets.extend(child.pets.all()) > > > > @db.event.listens_for(Parent.children, 'remove') > > def _remove_children(parent, child, initiator, *args, **kwargs): > > """ > > If a child is removed from the parent, this listener > > will also remove only remove_single_pet --> <Pet> > > """ > > > > remove_single_pet = kwargs.get('remove_single_pet', None) > > > > if remove_single_pet is not None: > > parent.pets.remove(remove_single_pet) > > else: # removes every pet > > for pet in child.pets: > > parent.pets.remove(pet) > > > > > > @db.event.listens_for(Parent.pets, 'remove') > > def _remove_pets(parent, pet, initiator, *args, **kwargs): > > """ > > If a pet is removed from the parent, and the parent also is related > > to the child that has access to that pet, then > > > > * removes relationship with the child, and > > * keeps relationship with the remaining pets, except the one that > was > > removed > > """ > > > > if pet.child in parent.children.all(): > > remove_single_pet = pet > > _remove_children(parent, pet.child, initiator, > remove_single_pet) > > > > > > #### test.py > > > > def test_child_pet_relationship_on_parents(self): > > > > > > # create new parent > > test_parent = Parent(name='test_parent') > > > > # commit parent to the database > > db.session.add(test_parent) > > db.session.commit() > > > > child1 = Child(id=1, > > name='FakeChild1') > > > > child2 = Child(id=2, > > name='FakeChild2') > > > > pet1 = Pet(id=1, > > name='FakePet1', > > child_id=1) > > > > pet2 = Pet(id=2, > > name='FakePet2', > > child_id=2) > > > > pet3 = Pet(id=3, > > name='FakePet3', > > child_id=1) > > > > db.session.add(child1) > > db.session.add(child2) > > db.session.add(pet1) > > db.session.add(pet2) > > db.session.add(pet3) > > > > db.session.commit() > > > > # add parent to the child > > child1.parents.append(test_parent) > > # add parent to the child > > pet2.parents.append(test_parent) > > > > # persist changes in the db > > db.session.add(child1) > > db.session.add(pet2) > > db.session.commit() > > > > # check that previous relationships are intact > > self.assertTrue(child1.pets.all() == [pet1, pet3]) > > self.assertTrue(child2.pets.all() == [pet2]) > > > > # resultant elements should be only child1, its pets and the > single > > Pet > > self.assertTrue(test_parent.children.all() == [child1]) > > self.assertTrue(test_parent.pets.all() == [pet1, pet2, pet3]) > > > > # remove child from parent > > pet3.parents.remove(test_parent) > > > > # resultant elements should be remaining pets, and no child > > self.assertTrue(test_parent.children.all() == []) > > self.assertTrue(test_parent.pets.all() == [pet1, pet2]) # pet2 > was > > not touched, > > # but > pet1 > > should remain since only > > # pet3 > was > > removed > > # child1 > > should be also removed since > > # > > relationship is unbalanced, i.e. > > # user > can't > > have access to a child if it > > # does > not > > have access to all of the child's pets > > > > > > > > I'm having errors > > > > sqlalchemy.orm.exc.StaleDataError: DELETE statement on table > > 'parent_pets_relationship' expected to delete 1 row(s); Only 0 were > matched. > > it looks like you have an explicit association mapping on the > secondary table as described in the green box in the section > > https://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#association-object, > > > but I dont' have your complete mappings so I can't say for sure, you'd > need to provide a complete MCVE. > > > > > > > > and the logger says nothing much. I suspect I'm falling into some weird > > recursion, calling the listener from another listener, where the second > call > > can't find something that was already deleted. > > > > This might be a long shot, but I'm hoping this pattern might be solved > > already. > > > > > > Am Donnerstag, 26. April 2018 13:00:45 UTC-3 schrieb Mike Bayer: > >> > >> On Thu, Apr 26, 2018 at 11:04 AM, Diego Quintana <daqui...@gmail.com> > >> wrote: > >> > Hello. > >> > > >> > 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`, > >> > meaning that a Parent can own a Child and its pets, and also a Pet > >> > without > >> > its Child. > >> > * `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? > >> > >> this seems like coordination of two separate relationships, so use > >> attribute events for that, > >> > >> > http://docs.sqlalchemy.org/en/latest/orm/events.html?highlight=attributeevent#sqlalchemy.orm.events.AttributeEvents > > >> : > >> > >> @event.listens_for(Parent.children, "append") > >> def _append_pets(parent, child, initiator): > >> parent.pets.extend(child.pets) # or whatever it is you need > >> > >> you would need to look at the "append" and "remove" events. > >> > >> > >> > >> > > >> > > >> > I have also posted this question in Stack Overflow, in case it means > >> > something. > >> > > >> > > >> > Best! > >> > > >> > > >> > > >> > -- > >> > SQLAlchemy - > >> > The Python SQL Toolkit and Object Relational Mapper > >> > > >> > http://www.sqlalchemy.org/ > >> > > >> > To post example code, please provide an MCVE: Minimal, Complete, and > >> > Verifiable Example. See http://stackoverflow.com/help/mcve for a > full > >> > description. > >> > --- > >> > You received this message because you are subscribed to the Google > >> > Groups > >> > "sqlalchemy" group. > >> > To unsubscribe from this group and stop receiving emails from it, > send > >> > an > >> > email to sqlalchemy+...@googlegroups.com. > >> > To post to this group, send email to sqlal...@googlegroups.com. > >> > Visit this group at https://groups.google.com/group/sqlalchemy. > >> > For more options, visit https://groups.google.com/d/optout. > > > > -- > > SQLAlchemy - > > The Python SQL Toolkit and Object Relational Mapper > > > > http://www.sqlalchemy.org/ > > > > To post example code, please provide an MCVE: Minimal, Complete, and > > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > > description. > > --- > > You received this message because you are subscribed to the Google > Groups > > "sqlalchemy" group. > > To unsubscribe from this group and stop receiving emails from it, send > an > > email to sqlalchemy+...@googlegroups.com <javascript:>. > > To post to this group, send email to sqlal...@googlegroups.com > <javascript:>. > > Visit this group at https://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.