[Dbix-class] Query problem

[Daniel J. Luke]

On May 14, 2012, at 2:21 PM, Kenneth S Mclane wrote:
> 
>         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'], 
>         }); 
> 
> 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" 
> 
> I have exhausted all the docs I can find

Does this help?

http://search.cpan.org/dist/DBIx-Class/lib/DBIx/Class/Manual/Cookbook.pod#Subqueries

I also found reading through the actual DBIx::Class (and SQL::Abstract) code illuminating when first looking at using DBIx::Class for some stuff.

> and if I can't get this count of subsystems relater to servers to work I will have to dump this as a feasible development platform.

you know you can use strait SQL with DBIx::Class too if you need to (although it's better if you don't):

http://search.cpan.org/dist/DBIx-Class/lib/DBIx/Class/Manual/Cookbook.pod#Arbitrary_SQL_through_a_custom_ResultSource

> I'm really hoping someone can come up with a concrete answer to do this. This is one of those things never addressed directly in any of the docs. If I had a example to work with I could figure it out, but I'm flying blind here.

There's not always an easy example that just shows you what you need to do - sometimes you have to understand how things work instead of just copy+paste+modify...

--
Daniel J. Luke                                                                   
+========================================================+                        
| *---------------- dluke at geeklair.net ----------------* |                          
| *-------------- http://www.geeklair.net -------------* |                          
+========================================================+                        
|   Opinions expressed are mine and do not necessarily   |                          
|          reflect the opinions of my employer.          |                          
+========================================================+