[Dbix-class] convention for naming primary keys to avoidambiguousselects

[Noel Burton-Krahn]

On Tue, Nov 25, 2008 at 2:06 PM, Peter Rabbitson <rabbit+list at rabbit.us> wrote:
> Octavian Rasnita wrote:
>> From: "Noel Burton-Krahn" <noel at burton-krahn.com>
>>
>> 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 <noel at burton-krahn.com>

=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 2>&1

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: $@");