[Dbix-class] convention for naming primary keys to
orasnita at gmail.com
Tue Nov 25 21:54:06 GMT 2008
From: "Noel Burton-Krahn" <noel at burton-krahn.com>
> This is a reasonable query: load a person with address by the
> person's id. The 'id' column is unambiguous in the search call.
> However, DBIx generates ambiguous SQL:
> DBIx::Class::ResultSet::count(): DBI Exception: DBD::mysql::st execute
> failed: Column 'id' in where clause is ambiguous [for Statement
> "SELECT COUNT( * ) FROM person me LEFT JOIN address address ON (
> address.person_id = me.id ) WHERE ( id = ? )" with ParamValues: 0='1']
> at ./t/dbix_unique_prefix_where.t line 116
> The simple way to fix it is to prefix "id" as "me.id". I'd argue DBIx
> should prefix all unqualified column names in the first argument to
> search(). Asking users to rely on DBIx's current internal strategy of
> using "me" as a prefix is not safe. What if "me" changes to "this" in
> a future release? What if I make a table named "me"?
Yes you are right, but maybe others already tried to find a solution for
this, but couldn't do it.
If the query searches only in a single table, then the column name can be
used without the table prefix.
If the query uses 2 tables, but the column name is not found in both tables,
then the column name can be also used without a prefix.
If the query searches in 2 or more tables and the column name appears more
than in a table, then the prefix should be added.
Well, in the first 2 cases, it is not a problem if that prefix is also
added, so this solution can be possible.
I think that if the query needs to search in a table named "me" which is not
the primary table, it probably gives another name instead of "me" for the
main table, so that name shouldn't need to be hard coded in the programs.
By the way, does anyone know what happends if a secondary table is named
More information about the DBIx-Class