[Dbix-class] Query problem

[Kenneth S Mclane]

I have gone through all that, tried a bunch of different ways of going at 
it and still can't get the results I need. If these docs had actual 
explanations of what it is doing and why and how I would have a much 
better time of it. There are so many huge areas where they just drop code 
on the page and walk away expecting that you already understand what they 
were thinking when they wrote it. While the correlated subqueries section 
seems to be on the right track, the example doesn't do what I need and 
there is no real expansion of the idea/methodology. It amazes me that 
nobody has ever needed a total of rows in one table based on a key from 
another table. It is very simple in raw sql, but I cannot even get that to 
work in DBIx::Class. 



From:
"Daniel J. Luke" <dluke at geeklair.net>
To:
"DBIx::Class user and developer list" <dbix-class at lists.scsys.co.uk>
Date:
05/14/2012 02:14 PM
Subject:
Re: [Dbix-class] Query problem



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.          |  
+========================================================+




_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: 
http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk



-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20120514/f4f47528/attachment-0001.htm