Thanks for the help! I was able to get this working with select([Series.type]).where(Series.id == series_id).as_scalar()
I have a follow up question though, when initializing these objects, it seems I have to do something like this session.add(Publication(series=session.query(Series).filter(Series.type == 'book').one())) This becomes strange when I already know the subclass of the publication session.add(Book(series=session.query(Series).filter(Series.type == 'book').one())) Is there a way to setup the relationship in such a way that I can initialize it with a column value, so that the underlying query is performed during the INSERT i.e. session.add(Publication(series='book')) # Creates the subquery during INSERT or session.add(Book()) This would make the superfluous querying for the appropriate row in the Series table go away, perhaps there is a keyword on the relationship which would allow for this? On Friday, January 27, 2017 at 5:34:30 PM UTC-6, Mike Bayer wrote: > > There's examples of custom SQL expressions at: > > > http://docs.sqlalchemy.org/en/latest/orm/mapping_api.html#sqlalchemy.orm.mapper.params.polymorphic_on > > > These examples don't currently illustrate a subquery (which would be a > good idea), but the idea is you make a scalar correlated subquery (a > Core example is at > > http://docs.sqlalchemy.org/en/latest/core/tutorial.html#correlated-subqueries) > > > and you can stick that right into polymorphic_on, or send it as a > column_property. > > > class Publication(db.Model): > __tablename__ = 'publication' > id = db.Column(db.Integer, primary_key=True) > series_id = db.Column(db.Integer, db.ForeignKey('series.id'), > nullable=False) > series = db.relationship(Series, backref='publications') > __mapper_args__ = { > 'polymorphic_on': select([series.type]).where(series_id == > Series.id).as_scalar(), > 'polymorphic_identity': None > } > > > > > On 01/27/2017 06:20 PM, Shane Carey wrote: > > Hi, I want to use a discriminator based upon a column of a related > table. > > > > I found the relevant place in the > > docs > http://docs.sqlalchemy.org/en/latest/orm/inheritance.html#joined-table-inheritance > > > > > "The discriminator column is only needed if polymorphic loading is > > desired, as is usually the case. It is not strictly necessary that it be > > present directly on the base mapped table, and can instead be defined on > > a derived select statement that’s used when the class is queried; > > however, this is a much more sophisticated configuration scenario" > > > > However this is not working for me, additionally I found this > > stackoverflow question which also encounters this problem to a > > tee. > http://stackoverflow.com/questions/40862634/sqlalchemy-orm-polymorphic-on-from-relationship > > > > > What is the correct method to have a discriminator based on a more > > complex select statement? Thank you! > > > > -- > > 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 [email protected] <javascript:> > > <mailto:[email protected] <javascript:>>. > > To post to this group, send email to [email protected] > <javascript:> > > <mailto:[email protected] <javascript:>>. > > 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
