<font size=2 face="sans-serif">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.<br>
</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>