[Dbix-class] Query translation

[Frank Schwach]

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/dbix-class@lists.scsys.co.uk


-- 
 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.