Thanks for you help, Mike! On Monday, January 30, 2017 at 10:36:06 AM UTC-6, Mike Bayer wrote: > > > > On 01/30/2017 11:06 AM, Shane Carey wrote: > > 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? > > So the most direct way to do this is in your constructor, and to assign > to the SQL expression instead of the result, so that the SQL expression > runs inline. But also we would skip using the relationship here, we'd > go to the FK directly: > > class Book(...): > def __init__(self, **kw): > self.series_id = select([Series.id]).where(Series.type == 'book') > > > the docs mentioned before illustrate using an event listener for this > purpose - as well as using mapper.polymorphic_identity to genericize it: > > from sqlalchemy import event > from sqlalchemy import inspect > > @event.listens_for(Publication, "init", propagate=True) > def set_identity(instance, *arg, **kw): > mapper = inspect(instance) > instance.series_id = select([Series.id]).where(Series.type == > mapper.polymorphic_identity) > > > These snippets are all good material for a new sub-section underneath > http://docs.sqlalchemy.org/en/latest/orm/inheritance.html . > > > > > > > > > > > > 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 > > > < > 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 > > < > 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 > > <http://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 > > > < > 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 > > > < > 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 > > <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+...@googlegroups.com <javascript:> > > > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:> > <javascript:>>. > > > To post to this group, send email to sqlal...@googlegroups.com > > <javascript:> > > > <mailto:sqlal...@googlegroups.com <javascript:>>. > > > 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/ > > > > 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+...@googlegroups.com <javascript:> > > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>. > > To post to this group, send email to sqlal...@googlegroups.com > <javascript:> > > <mailto:sqlal...@googlegroups.com <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 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.