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!
--
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.
