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

[David Schmidt]

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

Sincerely