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/e6546493-673c-4aab-aa96-88984c1a2e1en%40googlegroups.com.
