[Dbix-class] Unknown column in 'group statement'

2009-07-23 Thread 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@lists.scsys.co.uk

[Dbix-class] Relations for multi-class object inflation from one table

2009-07-23 Thread Oleg Kostyuk 
Hello all,

I read about Dynamic Sub-classing DBIx::Class proxy classes in
DBIx::Class::Manual::Cookbook, and have question.

Consider we have following definitions (simplified):

package My::Schema;
use base qw/DBIx::Class::Schema/;
__PACKAGE__-load_namespaces;

package My::Schema::Result::User;
use base qw/DBIx::Class/;
__PACKAGE__-load_components(qw/Core/);
__PACKAGE__-table('users');
__PACKAGE__-add_columns(qw/user_id ../);
__PACKAGE__-set_primary_key('user_id');
sub inflate_result {
# same as in DBIx::Class::Manual::Cookbook
}

package My::Schema::Result::User::Admin;
use base qw/My::Schema::Result::User/;
__PACKAGE__-table('users');

I want to have Company object, that should have 3 accessors, to access
plain users, admins, or all of them:

package My::Schema::Result::Company;
use base qw/DBIx::Class/;
__PACKAGE__-load_components(qw/Core/);
__PACKAGE__-table('companies');
__PACKAGE__-add_columns(qw/company_id ../);
__PACKAGE__-set_primary_key('company_id');
__PACKAGE__-has_many('admins', 'My::Schema::Result::User::Admin', );
__PACKAGE__-has_many('users', 'My::Schema::Result::User', );
__PACKAGE__-has_many('all_users', 'My::Schema::Result::User', );

My questions is:

1) what should be instead of '' in has_many() calls above? I
think, this should be something like this (for first has_many): {
'foreign.company_id' = 'self.company_id', 'foreign.admin' = 1 } - is
this correct?

2) my answer in (1) use details from User class (like constant values
- zero in previous question) - is it possible to avoid this, and
encapsulate all such details into one place (for example, User class)?

3) do I need write belongs_to(company) in User::Admin too, or only in
User will be enough?

4) do I need to use 'My::Schema::Result::User::Admin' in first
has_many, or this is incorrect, and only 'My::Schema::Result::User' is
acceptable?

Thanks.

-- 
Sincerely yours,
Oleg Kostyuk (CUB-UANIC)

___
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'

2009-07-23 Thread Peter Rabbitson 
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

Re: [Dbix-class] subquery insert for MSSQL

2009-07-23 Thread Peter Rabbitson 
On Wed, Jul 22, 2009 at 09:30:19AM -0500, fREW Schmidt wrote:
 
 Indeed.  The following works:
 
 
  INSERT INTO WorkScopeOperations (
 department,
 description,
 id,
 signature_required,
 work_order_id,
 work_scope_id
  ) SELECT
 ?
 '?,
 MAX(id) + 1,
 ?,
 ?,
 ?
FROM WorkScopeOperations me
WHERE ( ( work_order_id = ? AND work_scope_id = ? ) )
 
 
 

I don't quite follow... What are the bind values that the top placeholders
take? Also '?, seems to be a typo... I think.

___
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] subquery insert for MSSQL

2009-07-23 Thread fREW Schmidt 
On Thu, Jul 23, 2009 at 4:48 PM, Peter Rabbitson
rabbit+d...@rabbit.usrabbit%2bd...@rabbit.us
 wrote:

 On Wed, Jul 22, 2009 at 09:30:19AM -0500, fREW Schmidt wrote:
 
  Indeed.  The following works:
 
 
   INSERT INTO WorkScopeOperations (
  department,
  description,
  id,
  signature_required,
  work_order_id,
  work_scope_id
   ) SELECT
  ?
  '?,
  MAX(id) + 1,
  ?,
  ?,
  ?
 FROM WorkScopeOperations me
 WHERE ( ( work_order_id = ? AND work_scope_id = ? ) )
 
 
 

 I don't quite follow... What are the bind values that the top placeholders
 take? Also '?, seems to be a typo... I think.


yeah, '? is a typo.  Sorry about that.

I'm not really sure what you don't understand about the above.  I'll retype
it without the placeholders I guess:

INSERT INTO WorkScopeOperations (
department,
description,
id,
signature_required,
work_order_id,
work_scope_id
 ) SELECT
'department foo',
'description blah',
MAX(id) + 1,
'mechanics sig required',
1121, -- work_order_id
2  -- work_scope_id
   FROM WorkScopeOperations me
   WHERE ( ( work_order_id = 1121 AND work_scope_id = 1 ) )


 Does that answer the question?
-- 
fREW Schmidt
http://blog.afoolishmanifesto.com
___
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