Re: [Dbix-class] convention for naming primary keys to avoidambiguousselects
From: Noel Burton-Krahn [EMAIL PROTECTED] 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 me? 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 avoidambiguousselects
Octavian Rasnita wrote: From: Noel Burton-Krahn [EMAIL PROTECTED] 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 me? The name of the table is irrelevant as they are all aliased anyway. If you have a relationship called 'me' (hence JOIN tblname me), then I believe DBIC will switch it to me2 (just like it does with stacked joins over the same relationship[1]). If not - it might very well be a bug, tests/patches welcome :) Cheers [1] http://search.cpan.org/~ash/DBIx-Class-0.08010/lib/DBIx/Class/ResultSet.pm#join ___ 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 avoidambiguousselects
On Tue, Nov 25, 2008 at 2:06 PM, Peter Rabbitson [EMAIL PROTECTED] wrote:
Octavian Rasnita wrote:
From: Noel Burton-Krahn [EMAIL PROTECTED]
By the way, does anyone know what happends if a secondary table is named
me?
The name of the table is irrelevant as they are all aliased anyway. If
you have a relationship called 'me' (hence JOIN tblname me), then I
believe DBIC will switch it to me2 (just like it does with stacked joins
over the same relationship[1]). If not - it might very well be a bug,
tests/patches welcome :)
Not exactly irrelevant. If you have a table named me, then you get
an ambiguous table reference. In theis schema I have a table named
person and another named me.
$rs = $schema-resultset('Person')-search({ 'id' = $person_id},
{prefetch = [ qw(me) ]});
SELECT COUNT( * ) FROM person me LEFT JOIN me me ON ( me.person_id =
me.id ) WHERE ( id = ? ): '1'
DBIx::Class::ResultSet::count(): DBI Exception: DBD::mysql::st execute
failed: Not unique table/alias: 'me' [for Statement SELECT COUNT( * )
FROM person me LEFT JOIN me me ON ( me.person_id = me.id ) WHERE ( id
= ? ) with ParamValues: 0='1'] at t/dbix_unique_prefix_where.t line
114
My test script is below.
~Noel
#! /usr/bin/perl -w
=head1 NAME
dbix_unique_prefix_where.t - reproduce DBIx's ambiguous select
=head1 DESCRIPTION
DBIx::Class::ResultSet::select does not always qualify column names in
a join, resulting in ambiguous selects.
=head1 AUTHOR
Noel Burton-Krahn [EMAIL PROTECTED]
=cut
use strict;
use warnings;
#
package My::DBIx::Class;
use base qw/DBIx::Class/;
__PACKAGE__-load_components(qw/PK::Auto Core/);
use overload '' = 'dump';
sub dump {
my($self) = shift;
return join( , map { $_= . $self-get_column($_) } $self-columns);
}
#
package MySchema::Person;
use base qw/My::DBIx::Class/;
__PACKAGE__-table('person');
__PACKAGE__-add_columns(qw(id name));
__PACKAGE__-set_primary_key('id');
__PACKAGE__-has_many(me = 'MySchema::Me', 'person_id');
sub delete {
my $self = shift;
$_-delete for $self-me;
$self-SUPER::delete(@_);
}
#
package MySchema::Me;
use base qw/My::DBIx::Class/;
__PACKAGE__-table('me');
__PACKAGE__-add_columns(qw(id person_id me));
__PACKAGE__-set_primary_key('id');
__PACKAGE__-belongs_to(person = 'MySchema::Person', 'person_id');
#
package MySchema;
use base qw/DBIx::Class::Schema/;
__PACKAGE__-load_classes({
'MySchema' = [ qw(Person Me) ],
});
#
package Test::DbixCascaseDelete;
use Test::More tests = 5;
# create a mysql database to test with
system('EOS');
mysqladmin -f drop mytest /dev/null 21
mysqladmin create mytest
mysql mytest ESQL
create table person (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,name VARCHAR(1024) NOT NULL
) ENGINE=INNODB;
create table me (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,person_id INT NOT NULL
,me VARCHAR(1024) NOT NULL
,FOREIGN KEY (person_id) REFERENCES person (id)
) ENGINE=INNODB;
ESQL
#mysql mytest ESQL
#show tables;
#show create table person;
#show create table me;
#ESQL
EOS
;
is($?, 0, create database);
# connect
my $schema = MySchema-connect(dbi:mysql:mytest, '', '') or
die(connect: $!);
ok($schema, connect to db);
$schema-storage-debug(1);
my $rs;
my $person;
$person = $schema-resultset('Person')-create({ name = 'fred'});
ok($person, create Person: $person);
my $person_id = $person-id;
# this is ok, but a hack: I'm counting on DBIx to name person.id as me.id
$rs = $schema-resultset('Person')-search({ 'id' = $person_id},
{prefetch = [ qw(me) ]});
is($rs-count, 1, joined with me on me.id);
# this will fail: id in ambiguous with me.id
eval {
$rs = $schema-resultset('Person')-search({ id = $person_id},
{prefetch = [ qw(me) ]});
$rs-count;
};
ok($@, joined with me on id = ambiguous select: $@);
___
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
