I think I may have just found a solution? Not sure if this is correct but it looks like it worked when i changed the "extra" relationship to passive_deletes=True instead of cascade
class ChildClass(XPressoBase): __tablename__ = "child_class" id = Column("id", UUID(as_uuid=True), primary_key=True, default=uuid4()) parents = relationship("ParentClass", secondary="mix_parent_class_child_class", passive_deletes=True) children = relationship("ChildChildClass", secondary="mix_child_class_child_child_class", cascade="all, delete") children2 = relationship("ChildChildClass", secondary="mix_child_class_child_child_class", passive_deletes=True) On Thursday, December 17, 2020 at 5:50:06 PM UTC-5 maqui...@gmail.com wrote: > I have a polymorphic data model where association tables are to base > classes and some relationships that link to different child classes to > filter out the non-matching base classes, however these seem to result in > sqlalchemy being unable to delete cascade properly > > In a real case lets say i have > class Base(): > children = relationship("Base", secondary="mix_base_base", > cascade="all, delete") > someTypes = relationship("SomeType", secondary="mix_base_base", > cascade="all, delete") > other_types = relationship("OtherType", secondary="mix_base_base", > cascade="all") > > class SomeType(Base): > parents = relationship("Base", secondary="mix_base_base", > passive_deletes=True) > > class OtherType(Base): > parents = relationship("Base", secondary="mix_base_base", > passive_deletes=True) > > > if I delete a base that doesn't have relationships to SomeType and > OtherType, then everything works great. However once I add those extra > relationships sqlalchemy no longer deletes the children relationships. > This can be overcome by using lazy="dynamic" on the relationships, but at > GREAT performance cost (about 100%), which isn't good. > > Does anyone know how to get around this without using dynamic loading? The > specific relationships are important for front end pagination of data, but > the performance cost is too great for the amount of data in this system to > double the query times. > > a simplified and stupid workable example where I just add a redundant > children2 relationship to ChildClass, resulting in the same problem: > > from uuid import uuid4 > > from sqlalchemy import Column > from sqlalchemy.dialects.postgresql.base import UUID > from sqlalchemy.orm import relationship > > from webapp.database.orm.base import XPressoBase > > class ChildClass(Base): > __tablename__ = "child_class" > id = Column("id", UUID(as_uuid=True), primary_key=True, default=uuid4()) > parents = relationship("ParentClass", > secondary="mix_parent_class_child_class", passive_deletes=True) > children = relationship("ChildChildClass", > secondary="mix_child_class_child_child_class", cascade="all, delete") > children2 = relationship("ChildChildClass", > secondary="mix_child_class_child_child_class", cascade="all, delete") > > class ChildChildClass(Base): > __tablename__ = "child_child_class" > id = Column("id", UUID(as_uuid=True), primary_key=True, default=uuid4()) > parents = relationship("ChildClass", > secondary="mix_child_class_child_child_class", passive_deletes=True) > > class ParentClass(Base): > __tablename__ = "parent_class" > id = Column("id", UUID(as_uuid=True), primary_key=True, default=uuid4()) > children = relationship("ChildClass", > secondary="mix_parent_class_child_class", cascade="all, delete") > > from sqlalchemy.sql.schema import Table, ForeignKey, Column, > UniqueConstraint > > from webapp.database.orm.base import XPressoBase > > mix_parent_class_child_class = Table( > "mix_parent_class_child_class", > XPressoBase.metadata, > Column("parent_class_id", ForeignKey("parent_class.id", > ondelete="CASCADE"), nullable=False), > Column("child_class_id", ForeignKey("child_class.id"), nullable=False), > UniqueConstraint( > "parent_class_id", "child_class_id", name="uix_parent_class_child_class" > ), > ) > > mix_child_class_child_child_class = Table( > "mix_child_class_child_child_class", > XPressoBase.metadata, > Column("child_class_id", ForeignKey("child_class.id", > ondelete="CASCADE"), nullable=False), > Column("child_child_class_id", ForeignKey("child_child_class.id"), > nullable=False), > UniqueConstraint( > "child_class_id", "child_child_class_id", > name="uix_child_class_child_child_class" > ), > ) > > -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/a172d1cf-7105-48d8-a1ea-50889fa6e5c7n%40googlegroups.com.