Unknown column in 'group statement'
|
|
Anthony Gladdish
|
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? Thanks in advance, 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/dbix-class@... |
||||||||||||||||
Peter Rabbitson-2
|
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 _______________________________________________ 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@... |
||||||||||||||||
|
Anthony Gladdish
|
Hi Peter,
>-----Original Message----- >From: Peter Rabbitson [mailto:[hidden email]] >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/dbix-class@... |
||||||||||||||||
|
Peter Rabbitson-2
|
On Fri, Jul 24, 2009 at 11:00:50AM +0100, Anthony Gladdish wrote:
> Hi Peter, > > >-----Original Message----- > >From: Peter Rabbitson [mailto:[hidden email]] > >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@... |
||||||||||||||||
|
Peter Rabbitson-2
|
In reply to this post
by Anthony Gladdish
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. 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@... |
||||||||||||||||
|
Anthony Gladdish
|
Hi,
>-----Original Message----- >From: Peter Rabbitson [mailto:[hidden email]] >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. > 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 _______________________________________________ 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@... |
||||||||||||||||
|
Peter Rabbitson-2
|
Anthony Gladdish wrote:
> Hi, > >> -----Original Message----- >> From: Peter Rabbitson [mailto:[hidden email]] >> 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. _______________________________________________ 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@... |
||||||||||||||||
| Free Embeddable Forum Powered by Nabble | Help |
