I guess the simplest solutions are often the best… Turns out this actually works flawlessly (from what I’ve been able to tell so far), I didn’t expect SQLAlchemy to be smart enough to figure it out so I never even tried it this simple…
Thanks! > On 23 Mar 2019, at 20:04, Mike Bayer <mike...@zzzcomputing.com> wrote: > > On Sat, Mar 23, 2019 at 12:39 PM 02JanDal <j...@dalheimer.de > <mailto:j...@dalheimer.de>> wrote: >> >> Hello, >> >> I have two simple inheritance trees using joined table inheritance, see code >> below. In short, MembershipType inherits BaseShopItem and Membership >> inherits TransactionItem and there is a one-to-many relationship between >> BaseShopItem and TransactionItem. >> >> This works well, but now I want to be able to get the membership type for a >> certain membership, as well as all memberships for a membership type. Since >> this information already is present (through TransactionItem.shop_item_id, >> thus between the parent classes) I do not want to introduce another foreign >> key between the subclasses. What is the most elegant way to represent this >> as a relationship? > > Are you looking just to have Membership.transaction_items with the > TransactionItem objects filtered out and vice versa ? I think you > can use relationship() directly, Membership.membership_types = > relationship("MembershipType") / MembershipType.membership = > relationship("Membership") . You just need to be careful not to > modify both levels of relationship at once so you'd probably make > these viewonly. Alternatively, just build accessor methods with > @property that iterate Membership.transaction_items and filter out all > objects that are are not a subclass of MembershipType. > > >> >> I believe that composite secondary joins are most likely what I need, but do >> not fully understand it and have therefore not been able to adapt it. >> >> I'm using SQLAlchemy 1.3.1 with both SQLite and Postgres. >> >> Thanks for your time, >> Jan Dalheimer >> >> Code: >> >> class BaseShopItem(db.Model): >> __tablename__ = 'shop_item' >> id = db.Column(db.Integer, primary_key=True) >> type = db.Column(db.String) >> >> transaction_item = db.relationship('TransactionItem', >> back_populates='transaction_items') >> >> __mapper_args__ = { >> 'polymorphic_identity': 'shop_item', >> 'polymorphic_on': type >> } >> >> class MembershipType(ShopItem): >> __tablename__ = 'membership_type' >> id = db.Column(db.Integer, db.ForeignKey('shop_item.id'), >> primary_key=True) >> >> __mapper_args__ = { >> 'polymorphic_identity': 'membership_type' >> } >> >> class TransactionItem(db.Model): >> __tablename__ = 'transaction_item' >> id = db.Column(db.Integer, primary_key=True) >> type = db.Column(db.String) >> shop_item_id = db.Column(db.ForeignKey('shop_item.id')) >> >> shop_item = db.relationship('ShopItem', >> back_populates='transaction_items') >> >> __mapper_args__ = { >> 'polymorphic_identity': 'transaction_item', >> 'polymorphic_on': type >> } >> >> class Membership(TransactionItem): >> __tablename__ = 'membership' >> id = db.Column(db.Integer, db.ForeignKey('shop_item.id'), >> primary_key=True) >> >> __mapper_args__ = { >> 'polymorphic_identity': 'membership' >> } >> >> -- >> 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 sqlalchemy+unsubscr...@googlegroups.com >> <mailto:sqlalchemy+unsubscr...@googlegroups.com>. >> To post to this group, send email to sqlalchemy@googlegroups.com >> <mailto:sqlalchemy@googlegroups.com>. >> Visit this group at https://groups.google.com/group/sqlalchemy >> <https://groups.google.com/group/sqlalchemy>. >> For more options, visit https://groups.google.com/d/optout >> <https://groups.google.com/d/optout>. > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ <http://www.sqlalchemy.org/> > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve > <http://stackoverflow.com/help/mcve> for a full description. > --- > You received this message because you are subscribed to a topic in the Google > Groups "sqlalchemy" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/sqlalchemy/uij20f53dMg/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
