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 [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
<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 [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
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.
