| CC: | mst [...] shadowcatsystems.co.uk |
If there is a SELECT statement with several related tables under the
same alias, values may be taken from a wrong table.
Current 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------------------------------------
Expected result (w/o SELECT statement, apparently :-):
8<---------------------------------BEGIN-----------------------------------
name: foo
owner: Barney
members: 2
name: bar
owner: Fred
members: 1
8<----------------------------------END------------------------------------
see also
http://www.mail-archive.com/dbix-class@lists.rawmode.org/msg03970.html
| Subject: | Person.pm |
use strict;
use warnings;
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;
| Subject: | Group.pm |
use strict;
use warnings;
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;
| Subject: | Account.pm |
use strict;
use warnings;
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;
| Subject: | Schema.pm |
use strict;
use warnings;
package Schema;
use base qw/DBIx::Class::Schema/;
__PACKAGE__->load_classes();
1;
| Subject: | 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";
}
| Subject: | 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);