On 4/7/15 4:43 PM, Jonathan Gordon wrote:
How can I cascade delete from a many to one only when deleting the
many means this one has no other children?
Imagine the following mapping:
class Container(Base):
__tablename__ = 'container'
pk = sa.Column(sa.Integer, primary_key=True)
entries = relationship('Entry', backref='container', lazy='dynamic',
cascade="all, delete, delete-orphan")
class Entry(Base):
__tablename__ = 'entry'
pk = sa.Column(sa.Integer, primary_key=True)
container_pk = sa.Column(sa.Integer, sa.ForeignKey('container.pk'),
tag_pk = sa.Column(sa.Integer, sa.ForeignKey('tag.pk'))
class Tag(Base):
__tablename__ = 'tag'
pk = sa.Column(sa.Integer, primary_key=True)
description = sa.Column(sa.String, nullable=False)
entries = relationship("Entry", backref='tag')
A Container has zero or more Entry objects. An Entry may be grouped
with other Entry objects with a Tag. A Tag is never shared with Entry
instances from more than one Container
If I delete a Container instance, all of its Entry instances get
deleted via cascade. What I'd also like to happen is to cascade the
delete to all of the Tags as well.
While there is a limited form of "many-to-one cascade" supported, it
requires that Tag is only linked to a single Entry, which I'm gathering
is not the case here.
Typically events are used to intercept when a flush occurs so that
additional steps can be taken to delete the extra objects.
One event approach would be the before_flush() event, you search through
session.deleted for all Container objects; within Container, locate all
Tag entries with that Container as the ultimate parent and mark them all
as deleted as well. With the before_flush() approach, you can use the
Session and your objects in the usual way.
Another event approach would be to use the before_delete event on the
Container mapping. When the Container row is marked for deletion,
perform a Core level DELETE on the given connection that deletes all Tag
rows from the "tag" table. With the before_delete() approach, the
event occurs deep within the flush process, so it's not safe to
manipulate the state of the Session; instead, you emit SQL directly on
the given connection.
Still a third approach is to run a simple query at the end of the flush,
where you search for Tag objects that have no Entry objects associated,
and delete them. I came up with that for this stackoverflow answer:
http://stackoverflow.com/questions/9234082/setting-delete-orphan-on-sqlalchemy-relationship-causes-assertionerror-this-att/9264556#9264556.
You can probably just adapt that one.
Most of the documentation I've seen for cascading deletes talks about
deleting parents cascading to their children. In this case, it seems
like I want to delete the parent (Tag) if I'm the last child (Entry).
I'd like to avoid putting the Container pk on the Tag since it's
already on the Entry and that seems like it's not properly normalized.
My example seems close to the "Many-to-many orphan deletion" given in
sqlalchemy-utils, except it's many-to-one.
http://sqlalchemy-utils.readthedocs.org/en/latest/listeners.html#many-to-many-orphan-deletion
Any ideas?
--
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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
