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

[Peter Rabbitson]

On Fri, Jul 24, 2009 at 11:00:50AM +0100, Anthony Gladdish wrote:
> Hi Peter,
> 
> >-----Original Message-----
> >From: Peter Rabbitson [mailto:rabbit+dbic at rabbit.us]
> >Sent: 23 July 2009 22:45
> >To: DBIx::Class user and developer list
> >Subject: Re: [Dbix-class] Unknown column in 'group statement'
> >
> >On Thu, Jul 23, 2009 at 04:19:01PM +0100, 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;
> >> }
> >>
> >
> >This is a design lapse - we married distinct with group_by too early
> >in the code and what you are seeing is the fallout (i.e. distinct
> >applies to all columns not just the ones you are trying to select).
> >I will have to discuss this with the rest of the developers before
> >we solve it, but for the time being simply change
> >
> >        distinct => 1
> >
> >to
> >
> >        group_by => [ qw/me.id/ ]
> >
> >The prefetch will keep working as expected.
> >
> >Cheers
> 
> This works, but only if I omit "columns =>" too, i.e.:
> 
> 	{
>  		group_by => [ qw/me.id/ ],
> 		prefetch => [ { 'assistingtrainers' => 'trainers' } ],
> 		join => [ { 'assistingtrainers' => 'trainers' } ],
> 	},
> 
> I'm assuming this is ok, although I would have thought "columns" and "group_by" would be needed together (correct me if I'm wrong), and doing this:
> 
> 	{
>  		columns => [ qw/me.id/ ],
> 		group_by => [ qw/me.id/ ],
> 		prefetch => [ { 'assistingtrainers' => 'trainers' } ],
> 		join => [ { 'assistingtrainers' => 'trainers' } ],
> 	},
> 
> 
> ... produces error:
> 
> DBIx::Class::ResultSet::next(): DBI Exception: DBD::mysql::st execute failed: Unknown column 'start' in 'where clause' [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) 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-24T09:46:14', 1='2006-07-24T09:46:14', 2=2, 3='2009-07-24T09:46:14', 4='2006-07-24T09:46:14', 5=2]
> 

This unfortunately is a limitation which can not be worked around easily.
The problem is simple - there is no usable introspection of the WHERE
condition, thus there is no sane way to tell *what* the WHERE limits
on. In your case WHERE is necessary only on the inner query, as it works
on me.* columns only. However since I have no reliable way of determining
this, I err on the safe side, and include the WHERE in the inner and
outer query[1]. The right way to solve this is to bug ash to finish
SQLA2 :) However in the meantime you can trick this with distinct as
such:

    {
        select => [ { distinct => 'me.id' } ],
        as => [qw/id/],
        prefetch => [ { 'assistingtrainers' => 'trainers' } ],
        join => [ { 'assistingtrainers' => 'trainers' } ],
    },

HTH

[1] The reason the double WHERE is needed is as follows:

SELECT artist.*, cd.* 
    FROM (
        SELECT artist.*
            FROM artist
            JOIN cd ON <some condition>
        WHERE XXX
    ) artist
    JOIN cd ON <some condition>
WHERE XXX

In the above XXX could contain
1) only artist column restrictions
2) only cd column restrictions
3) both

If I could reliable determine this I would (in each of the cases):

1) Keep only the inner WHERE
2) Keep only the outer WHERE (and maybe even remove the inner join
if it was of type LEFT)
3) Separate the cd from the artist conditions and apply th where on
the relevant sides

Cheers