[Dbix-class] Query translation

[Kenneth S Mclane]

Ok, I created this file as a Resultset and put it in a Resultset folder 
next to my result folder.

package dbms::Schema::Result::Account;

 use base 'DBIx::Class::ResultSet';

 
__PACKAGE__->load_components(qw(Helper::ResultSet::CorrelateRelationship));

 sub sub_count {
   my $self = shift;

   $self->search(undef, {
     '+columns' => {
       num_subs => $self->correlate('subs')->count_rs->as_query
     }
   });
 }

 1;

Then I created a controller just to see if I can get any data back from 
this.

package dbms::Controller::subcount;
use Moose;
use namespace::autoclean;

BEGIN {extends 'Catalyst::Controller'; }

=head1 NAME

dbms::Controller::subcount - Catalyst Controller

=head1 DESCRIPTION

Catalyst Controller.

=head1 METHODS

=cut


=head2 index

=cut

sub index :Path :Args(0) {
    my ( $self, $c ) = @_;

    $c->response->body('Matched dbms::Controller::subcount in subcount.');
}

sub list :Local {
        my ($self, $c) = @_;
        my $rows = $c->model('ORANGES::Subcount')->sub_count->all;
        $c->stash(rows => $rows);
        $c->stash->{'template'}=>'subcount/list';
}

=head1 AUTHOR

root

=head1 LICENSE

This library is free software. You can redistribute it and/or modify
it under the same terms as Perl itself.

=cut

__PACKAGE__->meta->make_immutable;

1;

Your example shows doing this: 

 my $rows = $schema->resultset('Author')->with_book_count->all;

but it bombs out because $schema is not declared, and I don't know what 
value it should hold. I have been trying to reference it in my code above 
but nothing I have tried has been successful. Please remember that I am 
using the Catalyst helper script to create my model for the database, so I 
am coming at this from a different direction than someone who built their 
own model.



From:
fREW Schmidt <frioux at gmail.com>
To:
"DBIx::Class user and developer list" <dbix-class at lists.scsys.co.uk>
Date:
05/10/2012 12:08 PM
Subject:
Re: [Dbix-class] Query translation




SELECT
            s.server_id,
            a.account_code,
            s.server_name,
            s.server_type,
            s.os_name,
            (select count(*) from server ss, subsystem sb
                   where ss.server_id = sb.server_id
                   and ss.server_id = s.server_id) as num_subsystems
           FROM
            account a,
            server s
           WHERE a.account_id = s.account_id
           order by a.account_code, s.server_name

Actually, I just released a helper that will do the correlated subquery 
for you.  Check it out here: 
http://search.cpan.org/~frew/DBIx-Class-Helpers-2.008000/lib/DBIx/Class/Helper/ResultSet/CorrelateRelationship.pm 

-- 
fREW Schmidt
http://blog.afoolishmanifesto.com
_______________________________________________
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/20120511/2decef24/attachment-0001.htm