The undesired cross join (cartesian product) happend because I
involved Column objects in a filter statement (the filter statement is
not shown in the original example).
Changing it to use the mapped attributes solved the problem in another
way:
Instead of moving the CASE to the subselect it stays where it is, but
it uses the alias anon_1 now, which does eliminate the need for the
cross join.
e.g. changing
query = session.query(p2_fieldtype).outerjoin(p2_alphanumeric)
query = query.add_column(case([(some_condition, 'false')],
else_='true').label('linked'))
query = query.filter(table_p2_fieldtype.c.name == 'text') #
table_p2_fieldtype is the table object of p2_fieldtype
to
query = session.query(p2_fieldtype).outerjoin(p2_alphanumeric)
query = query.add_column(case([(some_condition, 'false')],
else_='true').label('linked'))
query = query.filter(p2_fieldtype.name == 'text')
yields the following statement that works (WHERE CLAUSE omitted):
SELECT [..],
CASE WHEN ( anon_1.p2_span_alphanumeric_fk_field_type IS NULL)
THEN
'false' ELSE 'true' END AS linked
FROM p2_fieldtype LEFT OUTER JOIN (
SELECT [..]
FROM p2_span
INNER JOIN p2_span_alphanumeric ON p2_span.id =
p2_span_alphanumeric.id) AS anon_1
ON p2_fieldtype.id = anon_1.p2_span_alphanumeric_fk_field_type
Note: - anon_1.p2_span_alphanumeric_fk_field_type IS NULL instead of
p2_span_alphanumeric.fk_field_type
- Only p2_fieldtype in first FROM CLAUSE
Regards,
Michael
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
