hi Michael -
note your original desired query is achievable by joining to a subquery instead
of the entity, following the style of the example at
http://www.sqlalchemy.org/docs/orm/tutorial.html#using-subqueries.
On May 3, 2011, at 6:07 AM, prinzdezibel wrote:
> 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.
>
--
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.
