On Jul 27, 2011, at 1:14 PM, Moshe C. wrote:
> 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.
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.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
