Selecting the right table instance in a self referential join

[Moshe C.]

I have the following mapper:
        orm.mapper(Xxx,xxx_table, inherits=Resource, polymorphic_identity=u'xxx',
                  properties={'children' : orm.relation(Xxx,
                                                        backref=orm.backref('parent', remote_side=[Xxx.c.id]),
                                                        primaryjoin=xxx_table.c.rid==xxx_table.c.parent_id)})

When I issue the following join, I get as the selected entity the parent side rather than the child side of the join.
                query = sqlalchemy.orm.query(Xxx)
                query = query.join('parent', aliased=True)
                query = query.filter(<some criterion>)

The SQL that is generated is as follows:
SELECT anon_1.resource_id AS anon_1_resource_id
FROM resource INNER JOIN xxx ON resource.id = xxx.id INNER JOIN (SELECT resource.id AS resource_id
                          FROM resource INNER JOIN xxx ON resource.id = xxx.id) AS anon_1 ON anon_1.xxx_id = xxx.parent_id
WHERE anon_1.resource_name  .....

What I really want is
SELECT resource_id AS resource_id
FROM resource INNER JOIN xxx ON resource.id = xxx.id INNER JOIN (SELECT resource.id AS resource_id
                          FROM resource INNER JOIN xxx ON resource.id = xxx.id) AS anon_1 ON anon_1.xxx_id = xxx.parent_id
WHERE anon_1.resource_name  .....

Any help is appreciated.

[Moshe C.]

I seem to have solved it by aliasing the first instance too
                query = sqlalchemy.orm.query(Xxx)
                alias = SA.orm.aliased(Xxx)
                query = query.join((alias,'parent'), aliased=True)

                query = query.filter(<some criterion>)     

But this basically succeeded "by magic" when I just tried all sorts of stuff.
I can't really understand why an addition of an alias caused the SQL not have an additional alias.

Is there a place in the doc that explains this?
I don't feel safe with these "magical" solutions, they tend to break on SA upgrades.

[Michael Bayer]

Let me note the mailing list posting guidelines at http://www.sqlalchemy.org/support.html#mailinglist .   The above snippets are out of context, incomplete and inaccurate, forcing me to guess and spend time reproducing a test, which in this case is a SQLAlchemy bug - a self-referential join between the child table of a joined-table inheriting mapper to itself is an extremely complicated scenario.   The project moves forward with user input of course so your cooperation is appreciated !

Your issue is #2234 at http://www.sqlalchemy.org/trac/ticket/2234 and a one line patch is attached to it.  It may go to 0.7.3 because 0.7.2 is very delayed and backlogged with a lot of small issues that need tests completed.

aliased=True applies an "adapter" to the query which is being inappropriately extrapolated to the lead entity here - it is ordinarily applied to all occurrences of the target class subsequent to the join(), but in the case of a join to a joined table inh, its getting stuck in the "polymorphic on" list as well which is inappropriate.

Usage of the alias() function here excludes the target of the join from the "polymorphic on" list so the adaptation of the parent is not applied in that case.