On Sun, Nov 26, 2017 at 6:19 AM, Mischa S <mis...@jetbridge.com> wrote:
> I want to have a polymorphic table that gets its identity from a column in a
> "type" table. I have a "task" table and a "task_type" table. I want to be
> able to create task subclasses that say something like:
>
> __mapper_args__ = {
> 'polymorphic_identity': 'do_stuff_task',
> }
>
> (Assuming that a row with do_stuff_task is created in the task_type table)
>
>
> Currently in my base Task class I do:
>
> task_type_name =
> column_property(select([TaskType.task_type]).where(TaskType.id ==
> task_type_id).as_scalar())
>
> __mapper_args__ = {
> 'polymorphic_on': task_type_name,
> }
this looks like you got this from
https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/PolymorphicOnAnotherTable,
yup
>
>
> If I try to get say, a user.do_stuff_tasks relationship it emits the
> following SQL:
>
> SELECT
> (SELECT task_type.task_type
> FROM task_type
> WHERE task_type.id = task.task_type_id)
> FROM task
> WHERE 123 = task.user_id
> AND
> (SELECT task_type.task_type
> FROM task_type
> WHERE task_type.id = task.task_type_id) IN ('do_stuff_task');
>
>
> This is technically correct, however this query is highly problematic for
> us. It does a scan on task in the subquery, which is extremely unpleasant
> and basically takes forever.
> What I want is something more like:
>
> SELECT *
> FROM task st
> JOIN task_type stt ON st.task_type_id=stt.id
> WHERE stt.task_type='do_stuff_task'
> AND st.user_id=123;
>
> Is there some way to rewrite the column_property or polymorphic identity
> condition to combine the WHERE conditions in the JOIN instead of doing two
> subselects?
this would be very difficult to generalize. the
polymorphic_discriminator is only specified as a column expression and
in SQLAlchemy a column expression never implies a JOIN in the query, a
JOIN is always an explicit add on Query. You could potentially
implement the before_compile() event for Query and add this JOIN
manually, but this would be very difficult to generalize to all cases.
Here's how to do it for the demo on the wiki mentioned above:
from sqlalchemy import event
from sqlalchemy.orm.query import Query
@event.listens_for(Query, "before_compile", retval=True)
def setup_query_for_poly(query):
entities = set(d['entity'] for d in query.column_descriptions)
if A in entities:
query = query.join(AType)
return query
with the mapping like:
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
data = Column(String)
type_id = Column(ForeignKey('atype.id'))
type_name = column_property(AType.name)
type = relationship(AType)
__mapper_args__ = {
"polymorphic_on": type_name,
"polymorphic_identity": "a"
}
then you get a query like this:
SELECT atype.name AS atype_name, a.id AS a_id, a.data AS a_data,
a.type_id AS a_type_id
FROM a JOIN atype ON atype.id = a.type_id
but....if your query refers to "AType" elsewhere, or multiple times,
or you refer to "A" in different contexts, adding a JOIN like this
could get in the way. Or if "A" is represented using aliasing, you'd
need to dig more carefully into query.column_descriptions and apply
the join() more carefully.
The other way to do this is just to put the polymorphic identities
into the mapper._polymorphic_map:
A.__mapper__.polymorphic_map.update(
(key, A.__mapper__.polymorphic_map[value])
for (key, value) in sess.query(AType.id, AType.name)
)
with mapping like:
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
data = Column(String)
type_id = Column(ForeignKey('atype.id'))
type = relationship(AType)
__mapper_args__ = {
"polymorphic_on": type_id,
"polymorphic_identity": "a"
}
>
>
> Thanks!
>
> --
> 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.
