SQLAlchemy: list of objects, preserve reference if they're deleted

Question

I'm trying to implement a user-facing PreviewList of Articles, which will keep its size even if an Article is deleted. So if the list has four objects [1, 2, 3, 4] and one is deleted, I want it to contain [1, 2, None, 4].

I'm using a relationship with a secondary table. Currently, deleting either Article or PreviewList will delete the row in that table. I've experimented with cascade options, but they seem to affect the related items directly, not the contents of the secondary table.

The snippet below tests for the desired behaviour: deleting an Article should preserve the row in ArticlePreviewListAssociation, but deleting a PreviewList should delete it (and not the Article).

In the code below, deleting the Article will preserve the ArticlePreviewListAssociation, but pl.articles does not treat that as a list entry.

from db import DbSession, Base, init_db
from sqlalchemy import Column, String, Integer, ForeignKey
from sqlalchemy.orm import relationship

session = DbSession()


class Article(Base):
    __tablename__ = 'articles'
    id = Column(Integer, primary_key=True)
    title = Column(String)


class PreviewList(Base):
    __tablename__ = 'preview_lists'
    id = Column(Integer, primary_key=True)
    articles = relationship('Article', secondary='associations')


class ArticlePreviewListAssociation(Base):
    __tablename__ = 'associations'
    article_id = Column(Integer, ForeignKey('articles.id'), nullable=True)
    previewlist_id = Column(Integer, ForeignKey('preview_lists.id'), primary_key=True)

    article = relationship('Article')
    preview_list = relationship('PreviewList')


init_db()

print(f"Creating test data")
a = Article(title="StackOverflow: 'Foo' not setting 'Bar'?")
pl = PreviewList(articles=[a])
session.add(a)
session.add(pl)
session.commit()

print(f"ArticlePreviewListAssociations: {session.query(ArticlePreviewListAssociation).all()}")

print(f"Deleting PreviewList")
session.delete(pl)
associations = session.query(ArticlePreviewListAssociation).all()
print(f"ArticlePreviewListAssociations: should be empty: {associations}")
if len(associations) > 0:
    print("FAIL")

print("Reverting transaction")
session.rollback()

print("Deleting article")
session.delete(a)
articles_in_list = pl.articles
associations = session.query(ArticlePreviewListAssociation).all()
print(f"ArticlePreviewListAssociations: should not be empty: {associations}")
if len(associations) == 0:
    print("FAIL")
print(f"Articles in PreviewList: should not be empty: {articles_in_list}")
if len(articles_in_list) == 0:
    print("FAIL")
# desired outcome: pl.articles should be [None], not []

print("Reverting transaction")
session.rollback()

This may come down to "How can you make a many-to-many relationship where pk_A == 1 and pk_B == NULL include the None in A's list?"

Answer 1

The given examples would seem to assume that the order of related articles is preserved, even upon deletion. There are multiple approaches to that, for example the Ordering List extension, but it is easier to first solve the problem of preserving associations to deleted articles. This seems like a use case for an association object and proxy.

The Article class gets a new relationship so that deletions cascade in a session. The default ORM-level cascading behavior is to set the foreign key to NULL, but if the related association object is not loaded, we want to let the DB do it, so passive_deletes=True is used:

class Article(Base):
    __tablename__ = 'articles'

    id = Column(Integer, primary_key=True)
    title = Column(String)

    previewlist_associations = relationship(
        'ArticlePreviewListAssociation', back_populates='article',
        passive_deletes=True)

Instead of a many to many relationship PreviewList uses the association object pattern, along with an association proxy that replaces the many to many relationship. This time the cascades are a bit different, since the association object should be deleted, if the parent PreviewList is deleted:

class PreviewList(Base):
    __tablename__ = 'preview_lists'

    id = Column(Integer, primary_key=True)

    article_associations = relationship(
        'ArticlePreviewListAssociation', back_populates='preview_list',
        cascade='all, delete-orphan', passive_deletes=True)

    articles = association_proxy(
        'article_associations', 'article',
        creator=lambda a: ArticlePreviewListAssociation(article=a))

Originally the association object used previewlist_id as the primary key, but then a PreviewList could contain a single Article only. A surrogate key solves that. The foreign key configurations include the DB level cascades. These are the reason for using passive deletes:

class ArticlePreviewListAssociation(Base):
    __tablename__ = 'associations'

    id = Column(Integer, primary_key=True)
    article_id = Column(
        Integer, ForeignKey('articles.id', ondelete='SET NULL'))
    previewlist_id = Column(
        Integer, ForeignKey('preview_lists.id', ondelete='CASCADE'),
        nullable=False)

    # Using a unique constraint on a nullable column is a bit ugly, but
    # at least this prevents inserting an Article multiple times to a
    # PreviewList.
    __table_args__ = (UniqueConstraint(article_id, previewlist_id), )

    article = relationship(
        'Article', back_populates='previewlist_associations')
    preview_list = relationship(
        'PreviewList', back_populates='article_associations')

With these changes in place no "FAIL" is printed.