Thanks again for your reply at the core is that when you remove a child from the parent in the > _remove_pets event, you want to prevent the _remove_children() event > from actually happening, I think. >
Yes, since it is a different usage case or flavour. I was trying to pass kwargs to the event listener directly, but it does not work. If I remove a pet from a parent, then we remove the child from the > parent, and *only* that pet. we dont remove other pets that might be > associated with that child. > Correct, this would be case A and the idea behind it is that, if a User has *access* to a Child, it should also have access to all of the Child's pets. If for some reason Parent does not have access to all of them, means it does not have access to the Child either. Removing a pet that has a Child that is also present in user.children should trigger this, leaving the parent with, as you say, only pets minus the one that was removed. It would also remove the child from the parent/children relationship in the association table. if I remove a child from the parent, then we remove *all* pets > associated with the child from that parent. > Correct, and this would be case B This seems like it's a contradiction. I have parent p1, not referring > to child c1, but it refers to pet p1 which *does* refer to child c1, > and that is valid. There's basically two flavors of "remove child > from parent", is that right? > Yes, both relationships -parent/pet and parent/child- are not seeing each other, and the only binding is their relationship between child and pets. Thus, the two flavors are depending on where is that listener being called from. For the case A, this is called inside the *if* statement in _remove_pets. Outside of this it should be always case B. I tried to work on an implementation here which would also have to be > extremely clever but I realized I don't actually understand what this > is supposed to do. if "remove child from parent" has two different > flavors then there needs to be all kinds of trickery to protect the > events from each other. > I understand that it requires a lot of fiddling. I was trying to pass kwargs to the listener directly, and parse them inside the other listener, but the other listener is not receiving them. I was seeing this is on purpose here <https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=4&ved=0ahUKEwjBl6T5uenaAhVHx1kKHZfjCQUQFghEMAM&url=https%3A%2F%2Fgroups.google.com%2Fd%2Ftopic%2Fsqlalchemy%2Fg8rP_7qErOk&usg=AOvVaw3_M_B49Zqukg5Fk2WOqJ_a> , since accepting kwargs would pollute the API. Perhaps a custom *event* implementation? Thanks again for your time Diego Alexandro Quintana Valenzuela Ingeniero Civil Electricista Universidad de la Frontera - Chile +56 9 7965 3455 IEEE PES & CIS Member [email protected] LinkedIn https://www.linkedin.com/in/diego-quintana-valenzuela/ 2018-05-02 18:46 GMT-03:00 Mike Bayer <[email protected]>: > On Wed, May 2, 2018 at 12:22 PM, Diego Quintana <[email protected]> > wrote: > > 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. > > So this is great, and shows the problem. but what you are trying to > do here is deeply complicated. I was going to just type out > everything I did to figure this out but this was way too long a > process. > > at the core is that when you remove a child from the parent in the > _remove_pets event, you want to prevent the _remove_children() event > from actually happening, I think. > > If I remove a pet from a parent, then we remove the child from the > parent, and *only* that pet. we dont remove other pets that might be > associated with that child. > > if I remove a child from the parent, then we remove *all* pets > associated with the child from that parent. > > This seems like it's a contradiction. I have parent p1, not referring > to child c1, but it refers to pet p1 which *does* refer to child c1, > and that is valid. There's basically two flavors of "remove child > from parent", is that right? > > I tried to work on an implementation here which would also have to be > extremely clever but I realized I don't actually understand what this > is supposed to do. if "remove child from parent" has two different > flavors then there needs to be all kinds of trickery to protect the > events from each other. > > > > > > > > > > > > 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 <[email protected]> > >> 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 <[email protected] > > > >> >> 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 [email protected]. > >> >> > To post to this group, send email to [email protected]. > >> >> > 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 [email protected]. > >> > To post to this group, send email to [email protected]. > >> > 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 [email protected]. > > To post to this group, send email to [email protected]. > > 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 a topic in the > Google Groups "sqlalchemy" group. > To unsubscribe from this topic, visit https://groups.google.com/d/ > topic/sqlalchemy/jgKgv5zQT7E/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > [email protected]. > To post to this group, send email to [email protected]. > 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
