Hi SA community
This issue has been bugging me for a while. Hopefully someone has some
input, since this issue is really impeding my work at this point.
Using the following code
query_object = select([id_column] + columns,
and_(*where_clauses),
from_parts,
use_labels=True,
correlate=False,
distinct=True)
I am able to produce the valid query:
SELECT DISTINCT accard."ESOPH_REGISTRY"."PTID" AS
"accard_ESOPH_REGISTRY_PTID",
accard."ESOPH_REGISTRY_VW"."GENDER" AS
"accard_ESOPH_REGISTRY_VW_GENDER",
accard."ESOPH_REGISTRY_VW"."LAST_NAME" AS
"accard_ESOPH_REGISTRY_VW_LAST_NAME",
accard."ESOPH_REGISTRY_VW"."FIRST_NAME" AS
"accard_ESOPH_REGISTRY_VW_FIRST_NAME",
accard."ESOPH_REGISTRY"."RACE" AS
"accard_ESOPH_REGISTRY_RACE",
accard."ESOPH_BIOPSY"."SPECIMEN_NO" AS
"accard_ESOPH_BIOPSY_SPECIMEN_NO",
accard."ESOPH_BIOPSY"."SITE_DISTANCE_CM" AS
"accard_ESOPH_BIOPSY_SITE_DISTANCE_CM",
accard."ESOPH_BIOPSY"."PATHOLOGY_CODE" AS
"accard_ESOPH_BIOPSY_PATHOLOGY_CODE"
FROM accard."ESOPH_REGISTRY",
accard."ESOPH_REGISTRY_VW"
LEFT OUTER JOIN accard."MASTER_REGISTRY" ON
accard."ESOPH_REGISTRY_VW"."UUID" = accard."MASTER_REGISTRY"."UUID",
accard."ESOPH_BIOPSY"
LEFT OUTER JOIN accard."ESOPH_ENDOSCOPY" ON
accard."ESOPH_BIOPSY"."FK_ENDOSCOPY_ID" =
accard."ESOPH_ENDOSCOPY"."PK_ENDOSCOPY_ID"
WHERE accard."ESOPH_ENDOSCOPY"."PTID" = accard."ESOPH_REGISTRY"."PTID"
AND accard."MASTER_REGISTRY"."UUID" = accard."ESOPH_REGISTRY"."UUID"
AND accard."ESOPH_REGISTRY"."PTID" = accard."ESOPH_REGISTRY_VW"."PTID"
AND upper(accard."ESOPH_ENDOSCOPY"."DISPLAY") = 'Y'
AND upper(accard."ESOPH_BIOPSY"."DISPLAY") = 'Y'
AND upper(accard."ESOPH_REGISTRY"."DISPLAY") = 'Y'
AND upper(accard."MASTER_REGISTRY"."DISPLAY") = 'Y' LIMIT 250
All is good and right with the world.
However, once I add a new where clause, an invalid query is produced, and
the source of my frustration begins.
For example, the following statement:
query_object.append_whereclause(esoph_biopsy.c['site_distance_cm'] > 5.0)
produces the query:
SELECT DISTINCT accard."ESOPH_REGISTRY"."PTID" AS
"accard_ESOPH_REGISTRY_PTID",
accard."ESOPH_REGISTRY_VW"."GENDER" AS
"accard_ESOPH_REGISTRY_VW_GENDER",
accard."ESOPH_REGISTRY_VW"."LAST_NAME" AS
"accard_ESOPH_REGISTRY_VW_LAST_NAME",
accard."ESOPH_REGISTRY_VW"."FIRST_NAME" AS
"accard_ESOPH_REGISTRY_VW_FIRST_NAME",
accard."ESOPH_REGISTRY"."RACE" AS
"accard_ESOPH_REGISTRY_RACE",
accard."ESOPH_BIOPSY"."SPECIMEN_NO" AS
"accard_ESOPH_BIOPSY_SPECIMEN_NO",
accard."ESOPH_BIOPSY"."SITE_DISTANCE_CM" AS
"accard_ESOPH_BIOPSY_SITE_DISTANCE_CM",
accard."ESOPH_BIOPSY"."PATHOLOGY_CODE" AS
"accard_ESOPH_BIOPSY_PATHOLOGY_CODE"
FROM accard."ESOPH_REGISTRY",
accard."ESOPH_BIOPSY",
accard."ESOPH_REGISTRY_VW"
LEFT OUTER JOIN accard."MASTER_REGISTRY" ON
accard."ESOPH_REGISTRY_VW"."UUID" = accard."MASTER_REGISTRY"."UUID",
accard."ESOPH_BIOPSY"
LEFT OUTER JOIN accard."ESOPH_ENDOSCOPY" ON
accard."ESOPH_BIOPSY"."FK_ENDOSCOPY_ID" =
accard."ESOPH_ENDOSCOPY"."PK_ENDOSCOPY_ID"
WHERE accard."ESOPH_ENDOSCOPY"."PTID" = accard."ESOPH_REGISTRY"."PTID"
AND accard."MASTER_REGISTRY"."UUID" = accard."ESOPH_REGISTRY"."UUID"
AND accard."ESOPH_REGISTRY"."PTID" = accard."ESOPH_REGISTRY_VW"."PTID"
AND upper(accard."ESOPH_ENDOSCOPY"."DISPLAY") = 'Y'
AND upper(accard."ESOPH_BIOPSY"."DISPLAY") = 'Y'
AND upper(accard."ESOPH_REGISTRY"."DISPLAY") = 'Y'
AND upper(accard."MASTER_REGISTRY"."DISPLAY") = 'Y'
AND accard."ESOPH_BIOPSY"."SITE_DISTANCE_CM" > 5.0 LIMIT 250
When run, it produces the error <OperationalError> ambiguous column name:
accard.ESOPH_BIOPSY.SPECIMEN_NO, which is understandable, considering the
table ESOPH_BIOPSY appears twice in the FROM clause--once as a standalone
table, and once in a join:
accard."ESOPH_BIOPSY" LEFT OUTER JOIN accard."ESOPH_ENDOSCOPY" ON
accard."ESOPH_BIOPSY"."FK_ENDOSCOPY_ID" =
accard."ESOPH_ENDOSCOPY"."PK_ENDOSCOPY_ID"
I'm not doing anything strange in the construction of the Select object via
select(). The value of from_parts contains 5 items: the 2 standalone tables
(accard."ESOPH_REGISTRY" and accard."ESOPH_REGISTRY_VW"), and the 2 joins.
This is confusing behavior, as it contradicts the SQLAlchemy documentation
for select(), mainly the from_obj argument:
- *from_obj* – A list of
ClauseElement<http://docs.sqlalchemy.org/en/rel_0_8/core/sqlelement.html#sqlalchemy.sql.expression.ClauseElement>
objects
which will be added to the FROM clause of the resulting statement. Note
that “from” objects are automatically located within the columns and
whereclause ClauseElements. Use this parameter to explicitly specify “from”
objects which are not automatically locatable. This could include
Table<http://docs.sqlalchemy.org/en/rel_0_8/core/metadata.html#sqlalchemy.schema.Table>
objects
that aren’t otherwise present, or
Join<http://docs.sqlalchemy.org/en/rel_0_8/core/selectable.html?highlight=join#sqlalchemy.sql.expression.Join>
objects
whose presence will supercede that of the
Table<http://docs.sqlalchemy.org/en/rel_0_8/core/metadata.html#sqlalchemy.schema.Table>
objects
already located in the other clauses.
My understanding of the above is that if I add a column to the WHERE clause
that references a table T, first all present FROM clause objects will be
checked for T first. If T appears in a join, T will not be added to the
FROM clause objects list. Likewise, if T already appears explictly in the
FROM clause it will not be added either.
Am I missing something?
Help! ANY help would be appreciated
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
