[Dbix-class] Query problem

[Kenneth S Mclane]

I have this almost working (Ithink).

sub list :Local {
        my ($self, $c, $page) = @_;
        $page = $c->req->param('page') || 1;
        my $rs = $c->model('ORANGES::Account')->search_rs(undef, { 
                join => { servers => 'subs' },
                select => [  { count => 'subsystem_id' } ],
                as => [ qw/ num_subs / ],
                group_by => [ qw/ account_code subs.server_id server_name 
server_type os_name   / ],
                rows => 15,
                page => $page,
                order_by => ['account_code', 'server_name'],
        });
        $c->stash(rows => $rs);
        $c->stash(pager => $rs->pager());
        $c->stash->{'template'}=>'server/list';
}

This gives me an error:

Couldn't render template "server/list.tt2: undef error - DBI Exception: 
DBD::DB2::db prepare_cached failed: [IBM][CLI Driver][DB2/AIX64] SQL0206N 
"NUM_SUBS" is not valid in the context where it is used.  SQLSTATE=42703
 [for Statement "
SELECT num_subs FROM (
  SELECT num_subs, ORDER__BY__1, ORDER__BY__2, ROW_NUMBER() OVER(  ORDER 
BY ORDER__BY__1, ORDER__BY__2 ) AS rno__row__index FROM (
    SELECT COUNT( subsystem_id ), account_code AS ORDER__BY__1, 
server_name AS ORDER__BY__2  FROM HCDB_TEST.ACCOUNT me LEFT JOIN 
HCDB_TEST.SERVER servers ON servers.account_id = me.account_id LEFT JOIN 
HCDB_TEST.SUBSYSTEM subs ON subs.server_id = servers.server_id GROUP BY 
account_code, subs.server_id, server_name, server_type, os_name
  ) me
) me WHERE rno__row__index >= ? AND rno__row__index <= ?


But the SQL looks like it is getting closer to what I need:

SELECT
             s.server_id, 
             a.account_code,
             s.server_name,
             s.server_type,
             s.os_name,
             (select count(*) from server ss, subsserystem sb 
                    where ss.server_id = sb.server_id 
                    and ss.server_id = s.server_id) as num_subsystems   
            FROM 
             account a,
             server s
            WHERE 
            and a.account_id = s.account_id
            order by a.account_code, s.server_name" 

I have exhausted all the docs I can find and if I can't get this count of 
subsystems relater to servers to work I will have to dump this as a 
feasible development platform. I'm really hoping someone can come up with 
a concrete answer to do this. This is one of those things never addressed 
directly in any of the docs. If I had a example to work with I could 
figure it out, but I'm flying blind here.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20120514/19495c1b/attachment.htm