[Dbix-class] Query problem

[Peter Rabbitson]

Daniel J. Luke wrote:
> 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...

Except when there is:
https://metacpan.org/module/DBIx::Class::Manual::Cookbook#Correlated-subqueries