Generic relationships with polymorphism

I've been trying to map the following schema into sqlalchemy:

db schema

So basically, I need to associate one of several owners (subscriptions, orders) with one of several flavor types (typeA, typeB etc). In other words, I have several entities that can own flavors and there are several flavor types with different actual flavors (type A has flavors 1 and 2, type B flavors 3 and 4, etc..).

Using SQLAlchemy-Utils generic relationships I have managed to map this via:

class Subscription(alchemy.Model):
    __tablename__ = 'subscription'
   id = alchemy.Column(alchemy.Integer, primary_key=True)

 class Order(alchemy.Model):
    __tablename__ = '_order'
    id = alchemy.Column(alchemy.Integer, primary_key=True)

class Owner2Flavors(alchemy.Model):
    __tablename__ = 'owner2flavors'
    id = alchemy.Column(alchemy.Integer, primary_key=True)
    owner_type = alchemy.Column(alchemy.Unicode(255))
    owner_id = alchemy.Column(alchemy.Integer)
    flavors_type = alchemy.Column(alchemy.Unicode(255))
    flavors_id = alchemy.Column(alchemy.Integer)

    owner = generic_relationship(owner_type, owner_id)
    flavors = generic_relationship(flavors_type, flavors_id)

class TypeAFlavors(alchemy.Model):
    __tablename__ = 'typea_flavors'
    id = alchemy.Column(alchemy.Integer, primary_key=True)
    flavor1 = alchemy.Column(alchemy.Integer)
    flavor2 = alchemy.Column(alchemy.Integer)

class TypeBFlavors(alchemy.Model):
    __tablename__ = 'typeb_flavors'
    id = alchemy.Column(alchemy.Integer, primary_key=True)
    flavor3 = alchemy.Column(alchemy.Integer)
    flavor4 = alchemy.Column(alchemy.Integer)

Which means I can create my needed entities just like in the documentation via:

subscription = Subscription()
flavors_type_a = TypeAFlavors()

alchemy.session.add_all([subscription, flavors_type_a])
alchemy.session.commit()

owner2flavors = Owner2Flavors()
owner2flavors.owner = subscription
owner2flavors.flavors = flavors_type_a

alchemy.session.add(owner2flavors)
alchemy.session.commit()

print(alchemy.session.query(Owner2Flavors).filter_by(owner=subscription).first())

And get any owner's owner2flavors via:

Owner2Flavors.query.\
    filter(Owner2Flavors.owner_type == self.__class__.__name__,
    Owner2Flavors.owner_id == self.id).all()

(which could eg. be stored in a mixin to be used by any owner)

So far so good, but I'd like to be able to have a function that would get an owner's actual flavors, instead of its owner2flavors, and that's where the problem arises, because going from an owner to owner2flavors is easy since there's the owner_type for the join, but from owner2flavors to flavors, it would have to join with 1 or more tables depending on the value of flavors_type.

I tried achieving this via Joined Table Inheritance and so in order to avoid having to add a generic "flavors" table (which would be the parent of all typeX_flavors tables), I tried making owner2flavors that very same "parent" table, but keep hitting walls and sql alchemy errors. Happy to share code of such tries, but I think that might not be the way.

So is there a way to achieve this so that I can use sqlalchemy's relationships etc to get, add, update or remove any owner's flavors of any type? Or am I stuck doing casi-manual queries?



Read more here: https://stackoverflow.com/questions/68484802/generic-relationships-with-polymorphism

Content Attribution

This content was originally published by blink0 at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: