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.
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
RE: [Dbix-class] Unknown column in 'group statement'
Hi,
-Original Message-
From: Peter Rabbitson [mailto:rabbit+d...@rabbit.us]
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
Re: [Dbix-class] Unknown column in 'group statement'
Anthony Gladdish wrote:
Hi,
-Original Message-
From: Peter Rabbitson [mailto:rabbit+d...@rabbit.us]
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
RE: [Dbix-class] Unknown column in 'group statement'
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]
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@lists.scsys.co.uk
Re: [Dbix-class] Unknown column in 'group statement'
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
[Dbix-class] Unknown column in 'group statement'
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@lists.scsys.co.uk
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
___
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
