Trouble with Join/Prefetch in DBIx::Class

[phildeman]

I am having difficulty trying to retrieve rows of data when using prefetch or join.

PERSON TABLE PACKAGE CONTAINS:

__PACKAGE__->has_many(
  "tbl_requests",
  "DB::MyDBS::Result::TblRequests",
  { "foreign.PID" => "self.PID" },
  { cascade_copy => 0, cascade_delete => 0 },
);

When making the query from the app:

my @requests = $schema->resultset( 'TblPerson' )->search({
                                                   LastName => 'Smith'
+,
                                                   FirstName => 'John'
+ },
                { prefetch => 'tbl_requests' }
        );

foreach my $request (@requests) {
    print "Name: " . $request->LastName . ", " .$request->FirstName . 
+"\n";
    print "SternID: " . $request->username . "\n";
    print "PrefName: " . $request->PrefName . "\n";
    print "Status: " . $request->tbl_requests->status . " . "\n";
}

This produces the following error:

Name: Smith, John
Username: js12345
Can't locate object method "status" via package "DBIx::Class::ResultSet" at test_requests.pl line 18

Line 18 in the test script is: print "Status: " . $request->tbl_requests->status . " . "\n";

Now this works fine when I do the reverse:

__PACKAGE__->belongs_to(
  "p",
  "DB::MyDBS::Result::TblPerson",
  { PID => "PID" },
  { is_deferrable => 1, on_delete => "NO ACTION", on_update => "NO ACT
+ION" },
);

When making the query from the app:

        my @requests = $schema->resultset( 'TblRequests' )->search({
                              'p.LastName' => 'Smith',
                              'p.FirstName' => 'John' },
                { prefetch => 'p' }
        );

foreach my $request (@requests) {
    print "Name: " . $request->p->LastName . ", ". $request->p->FirstN
+ame . "\n";
    print "SternID: " . $request->p->username . "\n";
    print "Status: " . $request->status . " . "\n";
}

Any thoughts why the first scenario gives the error and the second does not?

[duff]

Because of your has_many relationship, there are many tbl_requests and you haven't specified which of the many you'd like to see the status. Whereas, your second bit of code starts with individual TblRequests and gets the status of each.

(Sorry for the brevity, as I'm "out the door" so to speak. I'm sure someone else will elaborate if I can't get back to do so)

[Mr. Muskrat]

duff++

To expand upon his answer, you need a one-to-one relationship or at least search conditions that result in only one TblRequest per TblPerson if you want it work the way you have it written. You could also loop through each of the tbl_requests to get the status.

[poj]

As Mr. Muskrat said you can interate over the many records

my @persons  = $schema->resultset( 'TblPerson' )
     ->search( { LastName  => 'Smith',
                 FirstName => 'John' },
     { prefetch  => ['tbl_requests'] }
);

foreach my $p (@persons) {
  printf "
Name    : %s 
SternID : %s 
PrefName: %s 
", $p->LastName,
   $p->username,
   $p->PrefName; 
   for ($p->tbl_requests){
     printf " RID = %s  Status = %s\n",$_->RID,$_->status; 
   }
}

poj