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 [email protected] 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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/a172d1cf-7105-48d8-a1ea-50889fa6e5c7n%40googlegroups.com.
