<font size=2 face="sans-serif">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.</font>
<br>
<br>
<br>
<table width=100%>
<tr valign=top>
<td><font size=1 color=#5f5f5f face="sans-serif">From:</font>
<td><font size=1 face="sans-serif">Peter Rabbitson <rabbit+dbic@rabbit.us></font>
<tr valign=top>
<td><font size=1 color=#5f5f5f face="sans-serif">To:</font>
<td><font size=1 face="sans-serif">"DBIx::Class user and developer
list" <dbix-class@lists.scsys.co.uk></font>
<tr valign=top>
<td><font size=1 color=#5f5f5f face="sans-serif">Date:</font>
<td><font size=1 face="sans-serif">05/14/2012 02:23 PM</font>
<tr valign=top>
<td><font size=1 color=#5f5f5f face="sans-serif">Subject:</font>
<td><font size=1 face="sans-serif">Re: [Dbix-class] Query problem</font></table>
<br>
<hr noshade>
<br>
<br>
<br><tt><font size=2>Kenneth S Mclane wrote:<br>
> I have this almost working (Ithink).<br>
> <br>
> sub list :Local {<br>
> my ($self, $c, $page) = @_;<br>
> $page = $c->req->param('page') ||
1;<br>
> my $rs = $c->model('ORANGES::Account')->search_rs(undef,
{<br>
> join =>
{ servers => 'subs' },<br>
> select =>
[ { count => 'subsystem_id' } ],<br>
> as => [
qw/ num_subs / ],<br>
> group_by =>
[ qw/ account_code subs.server_id <br>
> server_name server_type os_name / ],<br>
> rows =>
15,<br>
> page =>
$page,<br>
> order_by =>
['account_code', 'server_name'],<br>
> });<br>
> $c->stash(rows => $rs);<br>
> $c->stash(pager => $rs->pager());<br>
> $c->stash->{'template'}=>'server/list';<br>
> }<br>
> <br>
> This gives me an error:<br>
> <br>
> Couldn't render template "server/list.tt2: undef error - DBI
Exception: <br>
> DBD::DB2::db prepare_cached failed: [IBM][CLI Driver][DB2/AIX64] <br>
> SQL0206N "NUM_SUBS" is not valid in the context where
it is used. <br>
> SQLSTATE=42703<br>
> [for Statement "<br>
> SELECT num_subs FROM (<br>
> SELECT num_subs, ORDER__BY__1, ORDER__BY__2, ROW_NUMBER() OVER(
ORDER <br>
> BY ORDER__BY__1, ORDER__BY__2 ) AS rno__row__index FROM (<br>
> SELECT COUNT( subsystem_id ), account_code AS ORDER__BY__1,
<br>
> server_name AS ORDER__BY__2 FROM HCDB_TEST.ACCOUNT me LEFT JOIN
<br>
> HCDB_TEST.SERVER servers ON servers.account_id = me.account_id LEFT
JOIN <br>
> HCDB_TEST.SUBSYSTEM subs ON subs.server_id = servers.server_id GROUP
BY <br>
> account_code, subs.server_id, server_name, server_type, os_name<br>
> ) me<br>
> ) me WHERE rno__row__index >= ? AND rno__row__index <= ?<br>
> <br>
<br>
I think this is the result of a bug that I am pretty sure is<br>
fixed in the last CPAN version. What version are you using?<br>
<br>
In any case as you correctly noticed you want a subquery not a<br>
bunch of crazy group bys.<br>
<br>
> But the SQL looks like it is getting closer to what I need:<br>
> <br>
> SELECT<br>
> s.server_id,
<br>
> a.account_code,<br>
> s.server_name,<br>
> s.server_type,<br>
> s.os_name,<br>
> (select count(*) from
server ss, subsserystem sb<br>
>
where ss.server_id = sb.server_id<br>
>
and ss.server_id = s.server_id) as num_subsystems <br>
> <br>
> FROM<br>
> account a,<br>
> server s<br>
> WHERE<br>
> and a.account_id = s.account_id<br>
> order by a.account_code,
s.server_name"<br>
> <br>
<br>
Correct. Then you were told that this is a thing that only became<br>
*easy* recently. You were given links to the helper component<br>
implementing precisely what you need. If your development model<br>
prevents you from using such components you will have a *very* hard<br>
time working with DBIx::Class, Catalyst and many other current CPAN<br>
libraries.<br>
<br>
> I have exhausted all the docs I can find <br>
<br>
I replied with the actual link to an email while I was typing up this<br>
reply. I would still like to make a point that if you continue to try<br>
to model specific SQL queries using DBIC, you will have a very very<br>
hard time going forward. This is not entirely a deficiency of DBIC,<br>
mostly it is the complexity of the task of *reliably* abstracting all<br>
the vagaries of SQL into a coherent, incrementally buildable API. So<br>
start small by getting back the data you want, and worrying about the<br>
resulting queries on a later stage, when you have an actual feel of<br>
the tools you are using. Expecting to jump into a set of libraries and<br>
be able to make them to behave *exactly* like you want is only going<br>
to set your stage for many bitter disappointments.<br>
<br>
Hope this helps.<br>
<br>
Cheers!<br>
<br>
_______________________________________________<br>
List: </font></tt><a href="http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class"><tt><font size=2>http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class</font></tt></a><tt><font size=2><br>
IRC: irc.perl.org#dbix-class<br>
SVN: </font></tt><a href="http://dev.catalyst.perl.org/repos/bast/DBIx-Class/"><tt><font size=2>http://dev.catalyst.perl.org/repos/bast/DBIx-Class/</font></tt></a><tt><font size=2><br>
Searchable Archive: </font></tt><a href="http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk"><tt><font size=2>http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk</font></tt></a><tt><font size=2><br>
<br>
</font></tt>
<br>
<br>