Use column from other table as discriminator?

[studioph]

Not sure if what I'm asking is possible, or considered bad practice, but here goes:

I'm trying to model a media manager type application with Users that can have multiple Collection[T]s which contain Media items, where T is a subclass of MediaItem. Collections have a media_type column indicating what type of items they contain - is there a way to use this column as the discriminator for the child classes instead of duplicating it as a column in the base MediaItem class?

The closest info I've been able to find is #10063, which is asking how to avoid a discriminator column entirely, which is not what I'm after. I also found this question, which implies it should be possible to simply reference the column object from the parent class (single side of 1-to-many relationship) if it has been defined already (which it has in my example below). The linked question is using an enum table of sorts - since using Python enums with SQLAlchemy doesn't need a separate enum table, I was hoping to avoid having to add one. However, the example I have below results in the following error:

sqlalchemy.exc.InvalidRequestError: Could not map polymorphic_on column 'media_type' to the mapped table - polymorphic loads will not function properly

I actually do not need polymorphic loading in this case because every item for a given collection will be of the same type, and come from the same table. Is it possible to disable this, or would that still not solve whatever the actual underlying problem is?

from enum import Enum
from typing import Annotated, Generic, Optional, TypeVar

from sqlalchemy import BigInteger, ForeignKey, String, UniqueConstraint, create_engine
from sqlalchemy.orm import (
    DeclarativeBase,
    Mapped,
    MappedAsDataclass,
    Session,
    mapped_column,
    relationship,
)

str256 = Annotated[str, mapped_column(String(256))]
intpk = Annotated[int, mapped_column(primary_key=True)]


class MediaType(Enum):
    AUDIO = "audio"
    IMAGE = "image"
    VIDEO = "video"


class Entity(MappedAsDataclass, DeclarativeBase, kw_only=True):
    """subclasses will be converted to dataclasses"""


class User(Entity):
    __tablename__ = "user"

    id: Mapped[intpk] = mapped_column(init=False)
    name: Mapped[str256]
    folder: Mapped[str256] = mapped_column(unique=True, index=True)

    collections: Mapped[list["MediaCollection"]] = relationship(
        back_populates="user", default_factory=list, lazy="selectin"
    )

T = TypeVar("T", bound="MediaItem")

class MediaCollection(Entity, Generic[T]):
    __tablename__ = "collection"
    __table_args__ = (UniqueConstraint("name", "user_id"),)

    id: Mapped[intpk] = mapped_column(init=False)
    name: Mapped[str256] = mapped_column(index=True)
    media_type: Mapped[MediaType]

    user_id: Mapped[int] = mapped_column(ForeignKey("user.id"), init=False)
    user: Mapped[User] = relationship(
        back_populates="collections", lazy="joined", innerjoin=True
    )
    items: Mapped[list["MediaItem"]] = relationship(
        back_populates="collection",
        default_factory=list,
        lazy="selectin",
    )

class MediaItem(Entity):
    __tablename__ = "mediaitem"
    __table_args__ = (UniqueConstraint("filename", "collection_id"),)

    id: Mapped[intpk] = mapped_column(init=False)
    filename: Mapped[str256]
    filesize: Mapped[int] = mapped_column(BigInteger)
    checksum: Mapped[Optional[bytes]] = mapped_column(index=True, default=None)
    collection_id: Mapped[int] = mapped_column(ForeignKey("collection.id"), init=False)
    collection: Mapped["MediaCollection"] = relationship(back_populates="items", lazy="joined", innerjoin=True)  # type: ignore

    __mapper_args__ = {
        "polymorphic_on": MediaCollection.media_type,
    }

class Video(MediaItem):
    __tablename__ = MediaType.VIDEO.value

    id: Mapped[intpk] = mapped_column(ForeignKey("mediaitem.id"), init=False)
    height: Mapped[int]
    width: Mapped[int]
    length: Mapped[int]
    bitrate: Mapped[int]

    __mapper_args__ = {
        "polymorphic_identity": MediaType.VIDEO,
    }

class Image(MediaItem):
    __tablename__ = MediaType.IMAGE.value

    id: Mapped[intpk] = mapped_column(ForeignKey("mediaitem.id"), init=False)
    height: Mapped[int]
    width: Mapped[int]

    __mapper_args__ = {
        "polymorphic_identity": MediaType.IMAGE,
    }

class Audio(MediaItem):
    __tablename__ = MediaType.AUDIO.value

    id: Mapped[intpk] = mapped_column(ForeignKey("mediaitem.id"), init=False)
    length: Mapped[int]
    bitrate: Mapped[int]
    sample_rate: Mapped[int]
    bit_depth: Mapped[int]

    __mapper_args__ = {
        "polymorphic_identity": MediaType.AUDIO,
    }

engine = create_engine("sqlite://", echo=True)
DeclarativeBase.metadata.create_all(engine)

with Session(engine) as session:
    user = User(name="test", folder="test")
    icoll = MediaCollection(name="img", media_type=MediaType.IMAGE, user=user)
    img = Image(
        filename="test.jpg",
        filesize=1_000_000,
        width=1920,
        height=1080,
        collection=icoll,
    )
    session.add(img)
    session.add(icoll)
    session.add(user)
    session.commit()

As an aside, it's not possible to use Mapped[list[T]] on the MediaCollection.items column, is there any way to declare such that if I query a collection, its items will be recognized as list[Video] for example instead of simply list[MediaItem]?

[CaselIT]

Hi,

Your description does not follow the example. MediaCollection is not the parent of MediaItem, since MediaItem does not derive from it.

You need a way of discriminating the single MediaItem row, so just indicating a column of an unrelated table is not gonna work. I'm not sure if a scalar subquery can be used by sqlalchemy as the discriminator, if so you could use that, but I'm not sure how efficient would that be

[studioph]

Ah, I can see I used some misleading or incorrect terminology there, my apologies. I was thinking in terms of the 1 to many relationship between MediaCollection and the videos/images as "parent-child" but since we're also talking about OOP inheritance you're correct that the collection is not at all related to the others.

As far as using an unrelated table goes, why does the solution from the linked SO answer work? Is it solely because it's a foreign key relationship as opposed to a column that's not a foreign key?

[CaselIT]

the SO solution seems to be:
mapper(Parent, parentsTable, polymorphic_on=parentsTable.c.parentTypeId)

Parent uses a column of it's table as polymorphic discriminator. In your example you are trying to use a column of another table

[studioph]

I see it now, it appears my mis-thinking of parent-child clouded my reading of that post as well.