Re: [sqlalchemy] polymorphic_on using relationship discriminator

[Shane Carey]

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 
> > '), 
> > 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 

Re: [sqlalchemy] polymorphic_on using relationship discriminator

[mike bayer]

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




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 

Re: [sqlalchemy] polymorphic_on using relationship discriminator

[Shane Carey]

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 sqlalchemy+...@googlegroups.com  
> > . 
> > To post to this group, send email to sqlal...@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.