Kenneth S Mclane wrote:
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 <= ?
I think this is the result of a bug that I am pretty sure is
fixed in the last CPAN version. What version are you using?
In any case as you correctly noticed you want a subquery not a
bunch of crazy group bys.
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"
Correct. Then you were told that this is a thing that only became
*easy* recently. You were given links to the helper component
implementing precisely what you need. If your development model
prevents you from using such components you will have a *very* hard
time working with DBIx::Class, Catalyst and many other current CPAN
libraries.
I have exhausted all the docs I can find
I replied with the actual link to an email while I was typing up this
reply. I would still like to make a point that if you continue to try
to model specific SQL queries using DBIC, you will have a very very
hard time going forward. This is not entirely a deficiency of DBIC,
mostly it is the complexity of the task of *reliably* abstracting all
the vagaries of SQL into a coherent, incrementally buildable API. So
start small by getting back the data you want, and worrying about the
resulting queries on a later stage, when you have an actual feel of
the tools you are using. Expecting to jump into a set of libraries and
be able to make them to behave *exactly* like you want is only going
to set your stage for many bitter disappointments.
Hope this helps.
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/[email protected]
