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@lists.scsys.co.uk