On Wed, May 2, 2018 at 10:14 AM, Diego Quintana <daquinta...@gmail.com> 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+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.
--
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.
