Hello, all, I've got a strange result when I wrote a script, that joins a single table twice under the same alias. Though I've managed to avoid this problem, having assigned another alias for one of relationships, however I think this is a bug and needs to be fixed. There is an sample code:
8<--------------------------ifomichev_test.sql----------------------------- DROP TABLE IF EXISTS `accounts`; CREATE TABLE `accounts` ( `account_id` int(10) unsigned NOT NULL auto_increment, `username` varchar(255) NOT NULL, `person_id` int(10) unsigned default NULL, PRIMARY KEY (`account_id`), KEY `person_id` (`person_id`) ) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; INSERT INTO `accounts` VALUES (1,'barney',3),(2,'fred',1); DROP TABLE IF EXISTS `groups`; CREATE TABLE `groups` ( `group_id` int(10) unsigned NOT NULL auto_increment, `name` varchar(255) NOT NULL, `account_id` int(10) unsigned default NULL, PRIMARY KEY (`group_id`), KEY `account_id` (`account_id`) ) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; INSERT INTO `groups` VALUES (1,'foo',1),(2,'bar',2),(3,'baz',NULL); DROP TABLE IF EXISTS `people`; CREATE TABLE `people` ( `person_id` int(10) unsigned NOT NULL auto_increment, `first_name` varchar(255) NOT NULL, `group_id` int(10) unsigned default NULL, PRIMARY KEY (`person_id`) ) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; INSERT INTO `people` VALUES (1,'Fred',1),(2,'Wilma',1),(3,'Barney',2); 8<-------------------------------Schema.pm--------------------------------- package Schema; use base qw/DBIx::Class::Schema/; __PACKAGE__->load_classes(); 1; 8<---------------------------Schema/Account.pm----------------------------- package Schema::Account; use base qw/DBIx::Class/; __PACKAGE__->load_components( qw/ Core PK::Auto / ); __PACKAGE__->table( 'accounts' ); __PACKAGE__->add_columns( qw/ account_id username person_id / ); __PACKAGE__->set_primary_key( qw/ account_id / ); __PACKAGE__->belongs_to( person => 'Schema::Person', 'person_id' ); __PACKAGE__->has_many( administered_groups => 'Schema::Group', 'account_id' ); 1; 8<----------------------------Schema/Group.pm------------------------------ package Schema::Group; use base qw/DBIx::Class/; __PACKAGE__->load_components( qw/ Core PK::Auto / ); __PACKAGE__->table( 'groups' ); __PACKAGE__->add_columns( qw/ group_id name account_id / ); __PACKAGE__->set_primary_key( qw/ group_id / ); __PACKAGE__->mk_group_accessors( column => 'member_count' ); __PACKAGE__->belongs_to( account => 'Schema::Account', 'account_id' ); __PACKAGE__->has_many( person => 'Schema::Person', 'group_id' ); #__PACKAGE__->has_many( members => 'Schema::Person', 'group_id' ); 1; 8<---------------------------Schema/Person.pm------------------------------ package Schema::Person; use base qw/DBIx::Class/; __PACKAGE__->load_components( qw/ Core PK::Auto / ); __PACKAGE__->table( 'people' ); __PACKAGE__->add_columns( qw/ person_id first_name group_id / ); __PACKAGE__->set_primary_key( qw/ person_id / ); __PACKAGE__->belongs_to( 'group', 'Schema::Group', 'group_id' ); 1; 8<-----------------------------test_dbic.pl-------------------------------- #!/usr/bin/perl use strict; use warnings; use Schema; my $schema = Schema->connect( 'DBI:mysql:ifomichev_test', 'root' ); $schema->storage()->debug( 1 ); my $rs = $schema->resultset('Group')->search( undef, { '+select' => [ { COUNT => 'person.person_id' } ], # '+select' => [ { COUNT => 'members.person_id' } ], '+as' => [ qw/ member_count / ], join => [ 'person' ], # join => [ 'members' ], group_by => [ qw/ me.group_id / ], } ); $rs = $rs->search( undef, { prefetch => { account => 'person' }, }, ); foreach ( $rs->all() ) { print "name: " . $_->name() . "\n"; print "owner: " . $_->account()->person()->first_name() . "\n"; print "members: " . $_->member_count() . "\n"; print "\n"; } 8<----------------------------------END------------------------------------ Result: 8<---------------------------------BEGIN----------------------------------- SELECT me.group_id, me.name, me.account_id, COUNT( person.person_id ), account.account_id, account.username, account.person_id, person.person_id, person.first_name, person.group_id FROM groups me LEFT JOIN people person ON ( person.group_id = me.group_id ) JOIN accounts account ON ( account.account_id = me.account_id ) JOIN people person_2 ON ( person_2.person_id = account.person_id ) GROUP BY me.group_id: name: foo owner: Fred members: 2 name: bar owner: Barney members: 1 8<----------------------------------END------------------------------------ We see, that $_->account()->person()->first_name() is taken from the wrong relationship (person.firstname instead of person_2.firstname). If we change alias for relationship "Schema::Group->has_many" from 'person' to e. g. 'members' (see commented lines), the code works all right: 8<---------------------------------BEGIN----------------------------------- SELECT me.group_id, me.name, me.account_id, COUNT( members.person_id ), account.account_id, account.username, account.person_id, person.person_id, person.first_name, person.group_id FROM groups me LEFT JOIN people members ON ( members.group_id = me.group_id ) JOIN accounts account ON ( account.account_id = me.account_id ) JOIN people person ON ( person.person_id = account.person_id ) GROUP BY me.group_id: name: foo owner: Barney members: 2 name: bar owner: Fred members: 1 8<----------------------------------END------------------------------------ Regards, Ivan _______________________________________________ List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class Wiki: http://dbix-class.shadowcatsystems.co.uk/ IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/ Searchable Archive: http://www.mail-archive.com/dbix-class@lists.rawmode.org/