[Dbix-class] Query problem

[Kenneth S Mclane]

Sorry, I don't find a link to an e-mail, just another link to the 
correlated subqueries section. I decided to put this on the back burner 
for now as one column missing for now is not the end of the world. Maybe 
I'll be able to figure it out later. I don't like to lose, but I can also 
be patient. I will persevere eventually. On to other parts of the app.



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/a9af610d/attachment-0001.htm