Hi,
Using:
Perl 5.10.
DBIC 0.08108.
1. My result source tables with relationships are:
Event.pm:
__PACKAGE__->set_primary_key('id');
__PACKAGE__->belongs_to( 'leader' => 'Trainer' ) ;
__PACKAGE__->has_many( 'assistingtrainers' => 'AssistingTrainer', 'event' );
__PACKAGE__->many_to_many('assistants' => 'assistingtrainers', 'trainers' );
AssistingTrainer.pm:
__PACKAGE__->set_primary_key('event','trainer');
__PACKAGE__->belongs_to( 'events' => 'Event', 'event' );
__PACKAGE__->belongs_to( 'trainers' => 'Trainer', 'trainer' );
Trainer.pm:
__PACKAGE__->set_primary_key('id');
2. My ResultSet::Event.pm methods:
sub goEvents {
my $self = shift;
my $where = {};
$where->{status} = { '&' => 2 };
return $self->search($where);
}
sub events_during_lookback_period {
my $self = shift;
my $default_lookback_period = DateTime::Duration->new( years => 3 );
my $lookback_period = shift || $default_lookback_period;
my $earliest_date = DateTime->now() - $lookback_period;
return $self->goEvents()->search_rs(
{
-and => [
start => { '<', DateTime->now() },
start => { '>', $earliest_date },
],
}
);
}
sub distinct_assistants {
my $self = shift;
my $assistants = [];
my $rs = $self->search_rs(
undef,
{
columns => [ qw/me.id/ ],
distinct => 1,
prefetch => [ { 'assistingtrainers' => 'trainers' } ],
join => [ { 'assistingtrainers' => 'trainers' } ],
},
);
while ( my $e = $rs->next() ) {
foreach my $at ( $e->assistingtrainers() ) {
push( @$assistants, $at->trainers );
}
}
return $assistants;
}
3. Test case producing error:
$trainers =
$schema->resultset('Event')->events_during_lookback_period($lookback_period)->distinct_assistants();
The "distinct_assistants()" chained method is producing error:
DBIx::Class::ResultSet::next(): DBI Exception: DBD::mysql::st execute failed:
Unknown column 'assistingtrainers.event' in 'group statement' [for Statement
"SELECT me.id, assistingtrainers.event, assistingtrainers.trainer, trainers.id,
trainers.name, trainers.email, trainers.phone, trainers.initials,
trainers.loginId, trainers.password FROM (SELECT me.id FROM event me WHERE ( (
( start < ? AND start > ? ) AND status & ? ) ) GROUP BY me.id,
assistingtrainers.event, assistingtrainers.trainer, trainers.id, trainers.name,
trainers.email, trainers.phone, trainers.initials, trainers.loginId,
trainers.password) me LEFT JOIN trainer_assist assistingtrainers ON
assistingtrainers.event = me.id LEFT JOIN trainer trainers ON trainers.id =
assistingtrainers.trainer WHERE ( ( ( start < ? AND start > ? ) AND status & ?
) ) ORDER BY assistingtrainers.event" with ParamValues:
0='2009-07-23T15:10:02', 1='2006-07-23T15:10:02', 2=2, 3='2009-07-23T15:10:02',
4='2006-07-23T15:10:02', 5=2]
Can anyone see if any schema updates/tweaks are required to make this work?
Or, if I'm missing anything else or if I'm using incorrect syntax?
Or confirm if this is a bug?
Thanks in advance,
Anthony
_______________________________________________
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/[email protected]
