Hi Kenneth,
this has nothing to do with Catalyst but it is a tricky one that stumped
me too. To get the count out of your subquery and let DBIC take care of
properly aliasing everything, try method
'as_subselect_rs'
(CPAN seems to be down at the moment but it is in DBIx::Class::ResultSet)
In brief, you do something like this:
my $final_rs = my $schema->resultset('whatever')->search(
$inner_query_where_condition,
$inner_query_options)->as_subselect_rs->search($outer_query_conditions,
$outer_query_opts);
In your case the outer query conditions would be undef but you could use
that to only retrieve records with a minimum count.
takes some playing around with and I'm afraid I don't have much time
right now but do give it a go and feel free to ask if it isn't working
for you - solved my problem for sure (very similar query).
Frank
On 10/05/12 16:55, Kenneth S Mclane wrote:
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.
_______________________________________________
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]
--
The Wellcome Trust Sanger Institute is operated by Genome Research
Limited, a charity registered in England with number 1021457 and a
company registered in England with number 2742969, whose registered
office is 215 Euston Road, London, NW1 2BE.
_______________________________________________
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]
