On Fri, Jul 24, 2009 at 11:00:50AM +0100, Anthony Gladdish wrote: > Hi Peter, > > >-----Original Message----- > >From: Peter Rabbitson [mailto:rabbit+d...@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 _______________________________________________ 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/dbix-class@lists.scsys.co.uk