On Mon, Feb 12, 2018 at 5:14 AM, Stanislav Lobanov <n10101...@gmail.com> wrote:
> Hello, i'm supporting legacy database, and using such models (code contains
> examples of my problem, keep reading):
>
> class Product: # This table is never used straightforward in queries
>
> type = Column(db.Integer)
> name = Column(db.String)
>
> TYPE_GUITARS = 1
> TYPE_DRUMS = 2
>
> __tablename__ = 'product'
> __mapper_args__ = {
> 'polymorphic_on': type,
> }
>
>
> class Guitars(Product):
> __mapper_args__ = {
> 'polymorphic_identity': Product.TYPE_GUITARS, # Guitars entity
> }
>
>
> class Drums(Product):
> __mapper_args__ = {
> 'polymorphic_identity': Product.TYPE_DRUMS, # Drums entity
> }
>
>
> # This query returns nothing.
>
> query1 = session.query(Musician).filter(
> Musician.guitar == Guitars.id,
> Musician.drum == Drums.id,
> )
>
> # Because SQL query built is wrong:
>
> # select * ... from product where ... => no rows returned
>
> # To fix his i use aliased() function:
>
> guitars = aliased(Guitars, name='guitars')
> drums = aliased(Drums, name='drums')
>
> query2 = session.query(Musician).filter(
> Musician.guitar == guitars.id,
> Musician.drum == drums.id,
> )
>
> # select * ... from product as guitars, product as drums where ... =>
> working fine
>
>
>
> My problem is that when i'm using two Polymorphic Models (with one database
> table) i always must use aliased() function. This is demonstrated in example
> higher.
>
> As you can see, query1 produces no results, because it is selecting from
> products table, and applies two filters to it. Instead it should use two
> product table aliases. It can be achieved with using aliased() function.
>
> So, finally, my question is:
>
> Are there any mechanism in sqlalchemy that can automatically apply aliases
> to such tables, if it sees their combination in query?
There are ways but they are not supported publicly, here is likely the simplest:
query1 = session.query(Musician)
from sqlalchemy.orm import util
query1._polymorphic_adapters.update(
{
inspect(Drums): util.ORMAdapter(aliased(Drums, name="drums")),
inspect(Guitars): util.ORMAdapter(aliased(Guitars, name="guitars"))
}
)
query1 = query1.filter(
Musician.guitar == Guitars.id,
Musician.drum == Drums.id,
)
the _polymorphic_adapters have to be applied before you call upon
filter(), as they modify what filter() does.
to make that happen automatically you probably have to subclass Query.
If you are subclassing Query, you can also override _adapt_clause() to
do this, again, you're tinkering with internals so you need to watch
that change across major releases.
The custom query subclass can be associated with sessions using
sessionmaker = sessionmaker(query_cls=MyQueryClass).
>
> --
> 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.
--
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.
