On Tuesday, November 28, 2017 at 4:10:42 AM UTC+13, Simon King wrote:
>
> On Sun, Nov 26, 2017 at 11:19 AM, Mischa S <mis...@jetbridge.com
> <javascript:>> 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
>
We have extra columns per job type on the task table, so I don't think
that'd work well unless we always referenced the orig task from the task
type subclasses
--
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.
