Hi Peter,
>-----Original Message-----
>From: Peter Rabbitson [mailto:[email protected]]
>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]
Thanks for your help,
Anthony
_______________________________________________
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/[email protected]
