[Dbix-class] Problem getting data from resultset

[Patrick Meidl]

On Thu, Jul 05 2012, Patrick Meidl <patrick at pantheon.at> wrote:

> On Thu, Jul 05 2012, Kenneth S Mclane <ksmclane at us.ibm.com> wrote:
> 
> >         my $wb = 
> > Spreadsheet::WriteExcel->new('/opt/catalyst/dbms/lib/dbms/output/output.xls');
> >         my $ws = $wb->add_worksheet('Output');
> >         my $col = 0; my $row = 0;
> >         my @fields = qw/ department.department_code account_code 
> > account_name account_policy compliance.percent_compliant 
> > metrics.num_servers metrics.num_subsystems progress.percent_complete /;
> > >This part works, I get my header row.
> >         foreach my $field (@fields) {
> >                 $ws->write($row, $col, $field);
> > 
> >                 $col++;
> >         }
> >         $col=0;
> >         $row++;
> > >This doesn't, I get an error that it can't locate the method, I tried 
> > "$ws->write($row, $col, $data->{$field)};" and I get no errors but no 
> > data. I also tried removing the arrow, but then it thinks I have an 
> > undeclared hash.
> >         foreach my $data ($sr->all) {
> >                 foreach my $field (@fields) {
> >                         $ws->write($row, $col, $data->$field);
> > 
> >                         $col++;
> >                 } 
> >                 $row++;
> >                 $col=0;
> >         }
> 
> your code will only work for fields in the main table of your resultset,
> but will break for relations. for example,
> $data->department.department_code is not a valid accessor. you will have
> to parse your field definition and convert it into
> $data->department->department_code in order to retrieve the data.

I remembered that there is a more elegant solution for this (and also
more DBIc-related; the discussion got a bit OT in the direction of
general perl problems): you can define proxies for fields in a related
table. so in your main Result class (I assume it is called something
like 'Account'), you would write something like:

__PACKAGE__->belongs_to(
    "department",
    "YourApp::Schema::Result::Department",
    { department_id => "department_id" },
    {
        is_deferrable => 1, on_delete => "CASCADE", on_update => "CASCADE",
        proxy => [ qw/department_code/ ],
    },
);

then, if you have an Account result, you can call $data->department_code
directly, rather than $data->department->department_code.

for details, see
https://metacpan.org/module/DBIx::Class::Relationship::Base#attributes

HTH

    patrick

-- 
Patrick Meidl ........................ patrick at pantheon.at
Vienna, Austria ...................... http://gplus.to/pmeidl