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

[Peter Rabbitson]

David Schmidt wrote:
> On Tue, Nov 25, 2008 at 5:31 PM, Octavian Rasnita <orasnita at gmail.com> wrote:
>> From: "David Schmidt" <davewood at gmx.at>
>>> Hello List,
>>>
>>> I used to have an "id INTEGER PRIMARY KEY" column in every table.
>>> Now I am using DBIC and repeatedly run into this kind of error.
>>>
>>> SELECT me.id, me.title, me.artist FROM cds me WHERE ( me.artist = ? ): '2'
>>> SELECT me.id, me.title, me.artist, artist.id, artist.name FROM cds me
>>> JOIN artists artist ON ( artist.id = me.artist ) WHERE ( id = ? ): '1'
>>> DBI Exception: DBD::SQLite::db prepare_cached failed: ambiguous column
>>> name: id(1) at dbdimp.c line 271 [for Statement "SELECT me.id,
>>> me.title, me.artist, artist.id, artist.name FROM cds me  JOIN artists
>>> artist ON ( artist.id = me.artist ) WHERE ( id = ? )"] at
>>> /usr/local/share/perl/5.8.8/DBIx/Class/Schema.pm line 954
>>>
>>> is it best(-DBIC)-practice to name primary keys <tablename>_id to
>>> avoid these errors or is there some other solution?
>>>
>>> thanks in advance
>>>
>>> David
>> It is better to use table_name.id in the DBIC code that wants to access the
>> column id of that table, of course, only in case you need to use 2 or more
>> tables in the query.
>>
>> $c->model('DB::TableName')->search({
>>  'the_table.id' => 1,
>> }
>> ...
>>
>> Octavian
>>
>>
>>
>> _______________________________________________
>> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
>> IRC: irc.perl.org#dbix-class
>> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
>> Searchable Archive:
>> http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
>>
> 
> Hello Octavian, thanks for your reply.
> 
> Your propsal didnt fix my problem because:
> 
> my $rs = $schema->resultset('Cd')->search( { 'cds.id' => 1 });
> 
> causes this error
> 
> DBI Exception: DBD::SQLite::db prepare_cached failed: no such column:
> cds.id(1) at dbdimp.c line 271 [for Statement "SELECT me.id, me.title,
> me.artist FROM cds me WHERE ( cds.id = ? )"] at
> /usr/local/share/perl/5.8.8/DBIx/Class/Schema.pm line 954
> 
> I really think there is a way to do this, because repeating the
> tablename in the columnname doesnt seem very DRY (dont repeat
> yourself) to me
> 

Did you notice that the columns in the select statement start with me.
not with cds. ? Every table in a DBIC query is given an alias. 'me' is
used for the main table, and the related tables are aliased to the
relationship names registered for the rowclass of 'me'.

As far as the don't repeat yourself - what do you propose? Your query
(the join-ed one) selects from two tables that have the same column.
Unless you specify which column you are interested in, it is not safe
for DBIC to assume something on its own. Either rename your columns to
be globally unique (ugly), or qualify your column names when necessary
(smart).

Cheers

Peter