[Dbix-class] Unknown column in 'group statement'

[Peter Rabbitson]

Anthony Gladdish wrote:
> 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?
> 

The above should now work _unmodified_ with the current trunk (i.e. using
distinct => 1). Please test and report your findings.

Cheers