I ran into an issue today due to the following scenario: table foo (model class Foo) has a column "type_id", which refers to the "id" column of table foo_type (model FooType)
session.query(Foo, FooType), since the generated labels alias both "foo.type_id" and "foo_type.id" to "foo_type_id", and thus the generated SQL duplicates the column labels and causes an exception similar to : InvalidRequestError: Ambiguous column name foo_type.id' in result set! try 'use_labels' option on select statement There's two possible workarounds for this that I can see: 1. Alias one of the tables to something else so there is no conflict (e.g. "foo_type" to "ft". Drawback: This needs to happen in all situations where this might happen 2. Refactor the database in some form such that the resulting column name combinations don't happen. Drawback: Altering the existing db schema may not be feasible. It would be handy, however, to be able to do one of these options instead to avoid the problem from even happening: 1. Set a 'default alias' for a table or column at the mapper/model level. Maybe class FooType can be configured with a default alias of "ft", or Foo.id can have a default alias of "foo_type_id" (which would then make it foo_foo_type_id after applying the table labelling) 2. Set/change the default separator between a table name and a column name when generating column aliases. I could work around this if, as an application-level default, all column labels were "table__column" (two underscores) instead of "table_column". Maybe the separator can be a callable -- (lambda tablename, columnname: tablename + "__" + columnname) Is there some other way to solve this that I might be missing? If not, might I humbly submit this as a feature request for an upcoming sqlalchemy version? ;) -- Daniel -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
