Re: [Dbix-class] convention for naming primary keys to avoid ambiguousselects
From: David Schmidt [EMAIL PROTECTED]
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
Re: [Dbix-class] convention for naming primary keys to avoid ambiguousselects
On Tue, Nov 25, 2008 at 5:31 PM, Octavian Rasnita [EMAIL PROTECTED] wrote:
From: David Schmidt [EMAIL PROTECTED]
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
___
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
Re: [Dbix-class] convention for naming primary keys to avoid ambiguousselects
David Schmidt wrote:
On Tue, Nov 25, 2008 at 5:31 PM, Octavian Rasnita [EMAIL PROTECTED] wrote:
From: David Schmidt [EMAIL PROTECTED]
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
___
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
Re: [Dbix-class] convention for naming primary keys to avoid ambiguousselects
On Tue, Nov 25, 2008 at 9:45 AM, Peter Rabbitson [EMAIL PROTECTED] wrote: 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). 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=?. What does DBIx use to build its where clause? Hopefully it would be straightforward to copy the prefixes from the select to the where. ~Noel ___ 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
Re: [Dbix-class] convention for naming primary keys to avoid ambiguousselects
On Tue, Nov 25, 2008 at 10:22 AM, Peter Rabbitson [EMAIL PROTECTED] 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
___
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
Re: [Dbix-class] convention for naming primary keys to avoid ambiguousselects
prefixing the id column with the name of the relationship seems much more
obvious than prefixing the base table's column with me. This doesn't seem
like it would be too complicated to implement -- if the column given is not
already prefixed by one or more table/relationship aliases, it could be
automatically changed to me.$col.
On Tue, Nov 25, 2008 at 3:41 PM, Peter Rabbitson
[EMAIL PROTECTED][EMAIL PROTECTED]
wrote:
Noel Burton-Krahn wrote:
On Tue, Nov 25, 2008 at 10:22 AM, Peter Rabbitson [EMAIL PROTECTED][EMAIL
PROTECTED]
wrote:
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.
Excellent example. Now tell me how to express the query Get me all
Persons which have an Address with (address.)id == $address_id, while
prefetching the Address data as well.
___
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
___
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
