[Dbix-class] Query translation

[Kenneth S Mclane]

No, I used static, dynamic has been deprecated per the docs. I just can't 
figure out the correct syntax to get the count I need. The original sql is 
using a subselect I think, but everything I try is coming back either with 
the extra AS clause or I get "error in assignment".

This code works for everything except the count of subsytems:

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', 'server'],
                rows => 15,
                page => $page,
                order_by => ['account.account_code', 'me.server_name'],
        });
        $c->stash(rows => $rs);
        $c->stash(pager => $rs->pager());
        $c->stash->{'template'}=>'server/list';
}

This is the original sql:

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 
             department d,
             account a,
             server s
            WHERE d.department_id = a.department_id
            and a.account_id = s.account_id
            and d.department_code like ?
            and a.account_code like ?
            order by a.account_code, s.server_name"

Which has references to the department table which isn't used in the 
query, so I left it out. The part I can't get working is:

 (select count(*) from server ss, subsystem sb 
                    where ss.server_id = sb.server_id 
                    and ss.server_id = s.server_id) as num_subsystems

I have scoured the docs and the web and cannot figure out how to get this 
working. 




From:
Frank Schwach <fs5 at sanger.ac.uk>
To:
"DBIx::Class user and developer list" <dbix-class at lists.scsys.co.uk>
Date:
05/11/2012 02:16 AM
Subject:
Re: [Dbix-class] Query translation



just to add to that: I got a suspicion that you may have used a Catalyst 
helper to create the database model with DBIC Schema Loader in *dynamic 
mode*, which is probably what you mean with porting this to Catalyst(?)
If so: you need to change that to static mode and let the Schema::Loader 
actually write the Schema files and work with those. It's better to work 
with a static schema anyway.
Once you have your Schema files you can add ResultSet classes and use 
this new helper as described. You can still re-generate the Schema 
classes as and when you make changes to the schema.
Hope this helps a little bit
Frank


On 10/05/12 23:25, fREW Schmidt wrote:
>
>     Ok, I understand how that works I think, but how/where do I
>     integrate it into my code as I am using Catalyst models and I'm
>     very confused on this one.
>
>
> I don't completely have enough information to answer, but you need to 
> make a base resultset and use that in your schema, and put the 
> correlate helper in the base resultset.  "Catalyst models" are just 
> DBIx::Class results.
> -- 
> fREW Schmidt
> http://blog.afoolishmanifesto.com
>
>
> _______________________________________________
> 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. 

_______________________________________________
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



-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20120511/05a66889/attachment.htm