[Dbix-class] Query problem

[Kenneth S Mclane]

Yes, the helper looked very promising, but there is little to no 
documentation as to what and how it does it's thing. I tried getting it to 
work but to no avail as there is no indication of how to deal with 
multiple things. It's example, (and I find this endemic in DBIx docs) 
refers to a scalar "$schema" with no explanation as to what this contains 
or what it should be set to. Can I substitute "$c->model('xxx::yyy') 
syntax for my code? No answer. where do I put the example files it talks 
of? What governs the naming, is it even important? I tried to get some 
answers from the author and got no response, so I gave up.




From:
Peter Rabbitson <rabbit+dbic at rabbit.us>
To:
"DBIx::Class user and developer list" <dbix-class at lists.scsys.co.uk>
Date:
05/14/2012 02:23 PM
Subject:
Re: [Dbix-class] Query problem



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



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