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" <[email protected]> To: "DBIx::Class user and developer list" <[email protected]> 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 +========================================================+ | *---------------- [email protected] ----------------* | | *-------------- 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/[email protected]
_______________________________________________ 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/[email protected]
