How can I add a column with add_column to a subselect?
Consider the following table schema:
+------------------+ +------------------+
|p2_fieldtype | n---1 | p2_span |
+------------------+ +-------------------+
^
| joined table
| inheritance
+------------------------+
| p2_alphanumeric |
+------------------------+
Querying the above schema happens in this way:
query = session.query(p2_fieldtype).outerjoin(p2_alphanumeric)
query = query.add_column(case([(some_condition, 'false')],
else_='true').label('linked'))
which results in a SQL statement roughly like this:
SELECT [..],
CASE WHEN (p2_span_alphanumeric.fk_field_type IS NULL) THEN
'false' ELSE 'true' END AS linked
FROM p2_span_alphanumeric,
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
The problem with the above is the cross join between
p2_span_alphanumeric and the result of the left outer join. This
happens as soon as the add_column method is applied to the query. This
leads to too many records in the result. What I'd like to do is to
move the case statement to the subselect (and along with carve out the
p2_span_alphanumeric cross join). The result would be something like
this:
SELECT [..], linked
FROM p2_fieldtype
LEFT OUTER JOIN (
SELECT [..],
CASE WHEN (p2_span_alphanumeric.fk_field_type IS NULL)
THEN 'false' ELSE 'true' END AS linked
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
I can't find a way how to accomplish this. Can anybody help me please?
Kind 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.
