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

[Peter Rabbitson]

Anthony Gladdish wrote:
> Hi,
> 
>> -----Original Message-----
>> From: Peter Rabbitson [mailto:rabbit+dbic at rabbit.us]
>> Sent: 05 August 2009 08:40
>> To: DBIx::Class user and developer list
>> Subject: Re: [Dbix-class] Unknown column in 'group statement'
>>
>> 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.
>>
> 
> This fixes the error (thanks), but I'm now getting an error from a similar ResultSet::Event.pm distinct method and test case to my original problem:
> 
> sub distinct_leaders {
> 	my $self  = shift;
> 	my $leaders = [];
> 	my $rs = $self->search_rs(
> 		undef,
> 		{
>             		columns => [ qw/me.leader/ ],
>             		distinct => 1,
> 			prefetch => 'leader',
> 			join => 'leader',			
> 		}
> 	);
> 	while ( my $e = $rs->next() ) {	
> 		push( @$leaders, $e->leader() );
> 	}
> 	return $leaders;	
> }
> 
> ... produces error:
> 
> DBIx::Class::ResultSet::next(): DBI Exception: DBD::mysql::st execute failed: Unknown column 'start' in 'where clause' [for Statement "SELECT me.leader, leader.id, leader.name, leader.email, leader.phone, leader.initials, leader.loginId, leader.password FROM (SELECT me.leader FROM event me  JOIN trainer leader ON leader.id = me.leader WHERE ( ( ( start < ? AND start > ? ) AND status & ? ) ) GROUP BY me.leader) me  JOIN trainer leader ON leader.id = me.leader WHERE ( ( ( start < ? AND start > ? ) AND status & ? ) )" with ParamValues: 0='2009-08-05T11:57:45', 1='2006-08-05T11:57:45', 2=2, 3='2009-08-05T11:57:45', 4='2006-08-05T11:57:45', 5=2]
> 
> ... using similar test case:
> 
> $trainers = $schema->resultset('Event')->events_during_lookback_period($lookback_period)->distinct_leaders();
> 

If you carefully read the generated SQL you will see that by using columns =>
you forcibly exclude the columns in your WHERE, so there is nothing to apply
the limit to, once you get to the outer select. There is some planned work
on retiring the "implicit prefetch limit" behavior (which is currently the
only mode of operation), but that's not going to be around for a while.

In short - if you want to have both prefetch AND where AND group_by (distinct)
you need to select all columns that will be a part of the where.