[Dbix-class] Query translation

[Kenneth S Mclane]

I am trying to port this query to Catalyst:

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

This is what I have so far:

sub list :Local {
        my ($self, $c, $page) = @_;
        $page = $c->req->param('page') || 1;
        my $rs = $c->model('ORANGES::Server')->search_rs(undef, { 
                prefetch => ['account','subs'],
                rows => 15,
                page => $page,
                '+select' => [{count => 'subs.subsystem.id'}],
                '+as' => [qw/num_subs/], 
                order_by => ['account.account_code', 'server_name'],
         });
        $c->stash(rows => $rs);
        $c->stash(pager => $rs->pager());
        $c->stash->{'template'}=>'server/list';
}

I haven't figured out the count statement as yet. I am currently getting 
my "as" added to the sql twice, i.e. "AS num_subs AS num_subs".

I can only find examples counting one field, let alone 2 tables and since 
I can't get it to return any results I can't even tell if it is counting 
correctly.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20120510/7c64595b/attachment.htm