[Dbix-class] convention for naming primary keys to avoid ambiguousselects

[Noel Burton-Krahn]

On Tue, Nov 25, 2008 at 10:22 AM, Peter Rabbitson <rabbit+list at rabbit.us> wrote:
> Noel Burton-Krahn wrote:
>>
>> The problem is that DBIx has the "smarts" to prefix column names in
>> the select clause, like "select me.id, cds.id" but not in the where
>> clause "where id=?".
>
> How does DBIC know if the user meant 'where me.id = ?' and not 'where
> cds.id = ?'. Blindly prefixing stuff with 'me' is plain wrong.
>

Consider the following search from a schema where person and address
both have a column named 'id':

$schema->resultset('Person')->search({ id => $person_id}, { prefetch
=> [ 'address' ] });

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