<font size=2 face="sans-serif">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".</font>
<br>
<br><font size=2 face="sans-serif">This code works for everything except
the count of subsytems:</font>
<br>
<br><font size=2 face="sans-serif">sub list :Local {</font>
<br><font size=2 face="sans-serif"> my
($self, $c, $page) = @_;</font>
<br><font size=2 face="sans-serif"> $page
= $c->req->param('page') || 1;</font>
<br><font size=2 face="sans-serif"> my
$rs = $c->model('ORANGES::Server')->search_rs(undef, { </font>
<br><font size=2 face="sans-serif">
prefetch => ['account','subs', 'server'],</font>
<br><font size=2 face="sans-serif">
rows => 15,</font>
<br><font size=2 face="sans-serif">
page => $page,</font>
<br><font size=2 face="sans-serif">
order_by => ['account.account_code', 'me.server_name'],</font>
<br><font size=2 face="sans-serif"> });</font>
<br><font size=2 face="sans-serif"> $c->stash(rows
=> $rs);</font>
<br><font size=2 face="sans-serif"> $c->stash(pager
=> $rs->pager());</font>
<br><font size=2 face="sans-serif"> $c->stash->{'template'}=>'server/list';</font>
<br><font size=2 face="sans-serif">}</font>
<br>
<br><font size=2 face="sans-serif">This is the original sql:</font>
<br>
<br><font size=2 face="sans-serif">SELECT</font>
<br><font size=2 face="sans-serif">
s.server_id, </font>
<br><font size=2 face="sans-serif">
a.account_code,</font>
<br><font size=2 face="sans-serif">
s.server_name,</font>
<br><font size=2 face="sans-serif">
s.server_type,</font>
<br><font size=2 face="sans-serif">
s.os_name,</font>
<br><font size=2 face="sans-serif">
(select count(*) from server ss, subsystem sb </font>
<br><font size=2 face="sans-serif">
where ss.server_id = sb.server_id </font>
<br><font size=2 face="sans-serif">
and ss.server_id = s.server_id) as num_subsystems
</font>
<br><font size=2 face="sans-serif">
FROM </font>
<br><font size=2 face="sans-serif">
department d,</font>
<br><font size=2 face="sans-serif">
account a,</font>
<br><font size=2 face="sans-serif">
server s</font>
<br><font size=2 face="sans-serif">
WHERE d.department_id = a.department_id</font>
<br><font size=2 face="sans-serif">
and a.account_id = s.account_id</font>
<br><font size=2 face="sans-serif">
and d.department_code like ?</font>
<br><font size=2 face="sans-serif">
and a.account_code like ?</font>
<br><font size=2 face="sans-serif">
order by a.account_code, s.server_name"</font>
<br>
<br><font size=2 face="sans-serif">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:</font>
<br>
<br><font size=2 face="sans-serif"> (select count(*) from server ss,
subsystem sb </font>
<br><font size=2 face="sans-serif">
where ss.server_id = sb.server_id </font>
<br><font size=2 face="sans-serif">
and ss.server_id = s.server_id) as num_subsystems</font>
<br>
<br><font size=2 face="sans-serif">I have scoured the docs and the web
and cannot figure out how to get this working. </font>
<br>
<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">Frank Schwach <fs5@sanger.ac.uk></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/11/2012 02:16 AM</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 translation</font></table>
<br>
<hr noshade>
<br>
<br>
<br><tt><font size=2>just to add to that: I got a suspicion that you may
have used a Catalyst <br>
helper to create the database model with DBIC Schema Loader in *dynamic
<br>
mode*, which is probably what you mean with porting this to Catalyst(?)<br>
If so: you need to change that to static mode and let the Schema::Loader
<br>
actually write the Schema files and work with those. It's better to work
<br>
with a static schema anyway.<br>
Once you have your Schema files you can add ResultSet classes and use <br>
this new helper as described. You can still re-generate the Schema <br>
classes as and when you make changes to the schema.<br>
Hope this helps a little bit<br>
Frank<br>
<br>
<br>
On 10/05/12 23:25, fREW Schmidt wrote:<br>
><br>
> Ok, I understand how that works I think, but how/where
do I<br>
> integrate it into my code as I am using Catalyst models
and I'm<br>
> very confused on this one.<br>
><br>
><br>
> I don't completely have enough information to answer, but you need
to <br>
> make a base resultset and use that in your schema, and put the <br>
> correlate helper in the base resultset. "Catalyst models"
are just <br>
> DBIx::Class results.<br>
> -- <br>
> fREW Schmidt<br>
> </font></tt><a href=http://blog.afoolishmanifesto.com/><tt><font size=2>http://blog.afoolishmanifesto.com</font></tt></a><tt><font size=2><br>
><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>
<br>
-- <br>
The Wellcome Trust Sanger Institute is operated by Genome Research <br>
Limited, a charity registered in England with number 1021457 and a <br>
company registered in England with number 2742969, whose registered <br>
office is 215 Euston Road, London, NW1 2BE. <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>