This worked.
I'm trying to achieve some rather tricky behaviour, where
1. Adding a children to some parent will also add the child's pets to
the parent
2. Removing a children from some parent will also remove every current
relationship that the Parent has with such pet
3. 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.
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]
> <javascript:>> 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] <javascript:>.
> > To post to this group, send email to [email protected]
> <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 [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.
