CODEWITHSUNDEEP
pythonsqlalchemyflask-sqlalchemy
frnsys
frnsys

Reputation: 2454

Polymorphic belongs-to relationships in SQLAlchemy?

I'm working with a database from a Rails project and am trying to build a Flask application on top of it.

There are some polymorphic associations in there which I can't figure out how to replicate using Flask-SQLAlchemy.

For example, there is a "Photo" model with the attributes id, url, photoable_id, photoable_type, and it may belong to an Artist, Organization, or an Event. In the database I have, the Artist, Organization, and Event tables have no references to the photos.

I found this approach for polymorphic associations in SQLAlchemy but it requires creating an association table - is there a way to do it with the schema as-is?

Upvotes: 2

Views: 1684

Answers (1)

frnsys
frnsys

Reputation: 2454

What I was looking for is outlined here.

Here's an example for a one-to-many relationship:

from app import db
from datetime import datetime
from sqlalchemy import event, and_
from sqlalchemy.orm import relationship, foreign, remote, backref


class HasPhotos():
    pass

class Photo(db.Model):
    __tablename__   = 'photos'
    id              = db.Column(db.Integer(), primary_key=True)
    image           = db.Column(db.String())
    photoable_id    = db.Column(db.Integer())
    photoable_type  = db.Column(db.String(50))
    created_at      = db.Column(db.DateTime(), default=datetime.utcnow)
    updated_at      = db.Column(db.DateTime(), default=datetime.utcnow)

    @property
    def parent(self):
        return getattr(self, 'parent_{}'.format(self.photoable_type))



@event.listens_for(HasPhotos, 'mapper_configured', propagate=True)
def setup_listener(mapper, class_):
    name = class_.__name__
    type = name.lower()
    class_.photos = relationship(Photo,
                        primaryjoin=and_(
                                        class_.id == foreign(remote(Photo.photoable_id)),
                                        Photo.photoable_type == type
                                    ),
                        backref=backref(
                                'parent_{}'.format(type),
                                primaryjoin=remote(class_.id) == foreign(Photo.photoable_id)
                                )
                        )
    @event.listens_for(class_.photos, 'append')
    def append_photo(target, value, initiator):
        value.photoable_type = type

Then when defining a class to have this relationship, just inherit from HasPhotos, e.g.

class Artist(HasPhotos, db.Model):
    pass

To do a one-to-one relationship, just add the keyword argument uselist=False to the relationship() call, and instead of listening for the append event, listen for the set event, e.g.:

@event.listens_for(class_.photo, 'set')
def set_photo(target, value, old_value, initiator):
    value.photoable_type = type

Upvotes: 8

Related Questions