SQLAlchemy alias mixup

Question

I have a problem with SQLAlchemy and aliases. I'm trying to do a self-join on a table called Task but SQLAlchemy confuses my aliased table with the non-aliased.

My query is much more complex but this is a simplified version:

baseTask = aliased(model.Task)

query = model.session.query(model.Task.name)\
       .select_from(baseTask)\
       .join((model.Task, model.Task.taskid==baseTask.taskid))

The resulting query looks like this:    
SELECT task_1.name 
FROM task AS task_1 INNER JOIN task ON task_1.taskid = task_1.taskid

As you can see it confuses the un-aliased table with the aliased task_1, both in the select and the join condition.

By using an alias on both of the tables it works:

baseTask = aliased(model.Task)
task = aliased(model.Task)

query = model.session.query(task.name)\
        .select_from(baseTask)\
        .join((task, task.taskid==baseTask.taskid))

This gives the desired result:
SELECT task_1.name 
FROM task AS task_2 INNER JOIN task AS task_1 ON task_1.taskid = task_2.taskid

When I use alias on both tables it doesn't confuse them and the resulting query is exactly what I want. The problem is that for various reasons I cannot use alias on the table I join to, this is due to the application design where a dynamically generate queries.

I'm using SQLAlchemy 0.6.8 and I've tried to upgrade to 0.7.9 but I still got this issue. Any ideas on how to solve this?

Answer 1

Unfortunately there's a bit of functional overlap between two particular things that select_from() does right now, one of which is that you can say query(Task).select_from(<some SELECT statement>), and it will re-orient "Task" in terms of <some SELECT statement>, and the other of which is you can tell it where to join FROM first.

http://www.sqlalchemy.org/trac/ticket/2635 is added where perhaps in 0.8 we can have it make better decisions with this specific scenario.

update: thanks for report on this, I've hit it in the past in my own work also but didn't manage to dig in, it was quite simple. If you'd like to use 0.8 tip for now your use case should work.

Here's some background on the fix: http://docs.sqlalchemy.org/en/latest/changelog/changelog_08.html#change-8d958f76ee47f7155365772401087d1f

potential 0.6.8 workaround:

query = session.query(Task.name)

query._from_obj += (baseTask._AliasedClass__alias, )

query = query.join((Task, Task.taskid == baseTask.taskid))