On Sun, Nov 26, 2017 at 11: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,
> }
>
>
> 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?
>
>
> Thanks!
Is it strictly necessary for your Task class to be polymorphic? Could
you make TaskType polymorphic instead, and then delegate from Task to
TaskType? Something like this:
class Task(Base):
def execute(self):
self.task_type.execute(self)
class TaskType(Base):
name = sa.Column(sa.String(16))
__mapper_args__ = {
'polymorphic_on': name,
}
def execute(self, task):
raise NotImplemented
class DoStuffTaskType(TaskType):
__mapper_args__ = {
'polymorphic_on': 'do_stuff_task',
}
def execute(self, task):
# do stuff
Simon
--
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.
