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?
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 <daqui...@gmail.com>
>> 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
>> > diego.q...@ieee.org
>> > LinkedIn
>> > https://www.linkedin.com/in/diego-quintana-valenzuela/
>> >
>> > 2018-05-02 18:46 GMT-03:00 Mike Bayer <mik...@zzzcomputing.com>:
>> >>
>> >> On Wed, May 2, 2018 at 12:22 PM, Diego Quintana <daqui...@gmail.com>
>> >> 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 <daqui...@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+...@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.
>> >> > 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 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
>> >> 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.
>> > 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.
