[Dbix-class] Query translation

[Frank Schwach]

Hi Kenneth,

ResultSet classes go into the ResultSet folder so DBIC can find them 
when it loads its namespaces.
So you should have a directory structure like this:
dbms/
   Schema/
     Result/
       Account.pm
     ResultSet/
       Account.pm

in the end.
And your ResultSet::Account file starts with

package dbms::Schema::ResultSet::Account;

I haven't tried frew's new helper yet, so can't comment on that (but 
will try it, sounds great!), but this will at least point you in the 
right direction I hope.
There is also still a little bit of confusion about Catalyst and DBIC: 
it doesn't matter what you do with the Schema in Catalyst. Catalyst 
simply consumes your Schema class and the helper script just sets this 
up for you so you can access it as a Model but it's still plain old DBIC.
My advice would be to write some unit tests for the new ResultSet 
functionality in your DBIC Schema class. forget about Catalyst for the 
moment and get this to work with DBIC. Once this is working you can 
simply use it in Catalyst in exactly the same way. Getting a test to 
work is also the best way of documenting your Schema (or any) class 
because you have working code for your future reference.

Frank



On 11/05/12 17:13, Kenneth S Mclane wrote:
> 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_ 
> <http://search.cpan.org/%7Efrew/DBIx-Class-Helpers-2.008000/lib/DBIx/Class/Helper/ResultSet/CorrelateRelationship.pm>
> -- 
> fREW Schmidt_
> __http://blog.afoolishmanifesto.com_ 
> <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
>
>
>
> _______________________________________________
> 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


-- 
 The Wellcome Trust Sanger Institute is operated by Genome Research 
 Limited, a charity registered in England with number 1021457 and a 
 company registered in England with number 2742969, whose registered 
 office is 215 Euston Road, London, NW1 2BE.