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

Anthony Gladdish a.j.gladdish at newcastle.ac.uk
Wed Aug 5 12:08:34 GMT 2009


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();

Thanks for your help with this,
Anthony


More information about the DBIx-Class mailing list