On Fri, May 18, 2018 at 4:44 PM, Diego Quintana <[email protected]> wrote: > So I'm back to this, and I wonder about something you said: >> >> >> the main complication here is that those "dynamic" relationships >> require that a query runs for everything, which means everything has >> to be in the database, which means it flushes the session very >> aggressively (and also disabling the flush, another thing I tried, >> means it doesn't read the contents of the collections accurately), and >> all of that makes an already tricky operation nearly impossible >> without it barreling into itself. >> >> If you are committed to using the "dynamic" relationships, you can >> always rely on emitting SQL to read from those association tables, and >> there's a completely unorthodox way to do this which would be way more >> efficient in most cases, and is extremely simple, just emit the DELETE >> statements: > > > So, I don't know actually if I have to be committed to one approach over the > other. I was trying to leverage the orm layer for consistency, but if there > is another approach to achieve the same thing I would like to know. Also, > you mentioned a test you've added, do you mind to share it? > > BTW, the code bit you changed makes it work, thanks!. There are a lot of > things I don't understand. But first, why does I need to use > `session_object`? to expose the `execute` method? What is that I am > "bypassing" by using this over the orm?
object_session() gives you the Session that has loaded / persisted that object for you, assuming that Session is still around. This is important because when you deal with the object you are really dealing with a proxy for a row in the database, local to a specific transaction. Other transactions might see a totally different row. So you want to stick to that one transaction when you emit SQL. not sure if you watched my video on this, I try to go into it pretty deep: http://www.sqlalchemy.org/library.html#thesqlalchemysessionindepth > > > Am Freitag, 4. Mai 2018 11:40:34 UTC-3 schrieb Diego Quintana: >> >> At the moment I've moved to other features, but I should be back to this >> somewhere in the near future. I will let you know the results. >> >> I really appreciate your time, thanks again. >> >> Best, >> >> Am Donnerstag, 3. Mai 2018 10:10:47 UTC-3 schrieb Mike Bayer: >>> >>> On Thu, May 3, 2018 at 7:39 AM, Diego Quintana <[email protected]> >>> wrote: >>> > 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 , >>> > since accepting kwargs would pollute the API. Perhaps a custom event >>> > implementation? >>> >>> I slowly realized it looked like you hoped that flag would pass >>> through but there's too many layers of indirection for it to work that >>> way. the main complication here is that those "dynamic" relationships >>> require that a query runs for everything, which means everything has >>> to be in the database, which means it flushes the session very >>> aggressively (and also disabling the flush, another thing I tried, >>> means it doesn't read the contents of the collections accurately), and >>> all of that makes an already tricky operation nearly impossible >>> without it barreling into itself. >>> >>> If you are committed to using the "dynamic" relationships, you can >>> always rely on emitting SQL to read from those association tables, and >>> there's a completely unorthodox way to do this which would be way more >>> efficient in most cases, and is extremely simple, just emit the DELETE >>> statements: >>> >>> from sqlalchemy.orm import object_session >>> >>> @sa.event.listens_for(Parent.children, 'remove') >>> def _remove_children(parent, child, initiator, *args, **kwargs): >>> object_session(parent).execute( >>> "delete from parents_pets_relationship where " >>> "parent_id=:parent_id and pet_id=:pet_id", >>> [ >>> {"parent_id": parent.id, "pet_id": pet.id} >>> for pet in child.pets >>> ] >>> ) >>> >>> >>> @sa.event.listens_for(Parent.pets, 'remove') >>> def _remove_pets(parent, pet, initiator, *args, **kwargs): >>> object_session(parent).execute( >>> "delete from parents_children_relationship where " >>> "parent_id=:parent_id and child_id=:child_id", >>> {"parent_id": parent.id, "child_id": pet.child.id} >>> ) >>> >>> I added a second test for the other case and this works as far as I've >>> gotten it. since we are only dealing with these standalone >>> association tables the above is pretty simple, and now you only emit >>> one query (albeit a DML query, not a SELECT) rather than lots of >>> SELECT statements. The above is at least very simple to work >>> with since the ORM has no involvement past getting you those events. >>> >>> if the above does what you need then it's probably worth doing that way. >>> >>> >>> >>> > >>> > 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. > > -- > 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.
