[Dbix-class] convention for naming primary keys to avoidambiguousselects

Octavian Rasnita 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"?
> ~Noel

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 mailing list