Thanks to everyone who offered advice, I took a couple of days working on other things and then revisited this. This is what I did.
I created a Sub.pm in my lib/myApp/Scema/Result folder containing this:
se utf8;
package myApp::Schema::Result::Sub;
=head1 NAME
myApp::Schema::Result::Sub
=cut
use strict;
use warnings;
use Moose;
use MooseX::NonMoose;
use MooseX::MarkAsMethods autoclean => 1;
extends 'DBIx::Class::Core';
__PACKAGE__->load_components("InflateColumn::DateTime");
__PACKAGE__->table("SUBS");
__PACKAGE__->add_columns(qw/server_id account_code server_name server_type
os_name num_subs/);
__PACKAGE__->result_source_instance
->name(\'(SELECT
s.server_id,
a.account_code,
s.server_name,
s.server_type,
s.os_name,
(select count(*) from hcdb_test.server ss,
hcdb_test.subsystem sb
where ss.server_id = sb.server_id
and ss.server_id = s.server_id) as num_subs
FROM
hcdb_test.account a,
hcdb_test.server s
WHERE a.account_id = s.account_id
order by a.account_code, s.server_name)');
1;
This basically throws the raw SQL at the db and elinates the need for
joins, prefetching etc. I then modified my list sub to access this, which
simplified it even more:
sub list :Local {
my ($self, $c, $page) = @_;
$page = $c->req->param('page') || 1;
my $account = $c->req->param('account') || "%";
my $rows = $c->req->param('rows') || 15;
my $base_rs = $c->model('ORANGES::Sub');
my $rs = $base_rs->search_rs({ 'account_code' => { 'like',
$account }}, {
rows => $rows,
page => $page,
order_by => ['account_code', 'server_name'],
});
$c->stash(cows => $rs);
$c->stash(pager => $rs->pager());
$c->stash->{'template'}=>'server/list';
}
This solved my problem with getting the count I need. One day I'll
probably be able to do it the other way, but this app will have at most 4
users at a time, so I don't think it will suffer from this one bit of SQL.
Regards
Kenneth McLane
700 Locust St
Systems Compliance Services
Dubuque, 52001-6838
I1OB
USA
GTS Services Delivery
Phone:
+1-563-845-4674
Tie-Line:
946-4674
Mobile:
+1-563-940-7147
e-mail:
[email protected]
"Ideas come from everything" -- Alfred Hitchcock
<<image/gif>>
_______________________________________________ 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/[email protected]
