Trouble with Join/Prefetch in DBIx::Class

phildeman has asked for the wisdom of the Perl Monks concerning the following question:

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 },
);
[download]

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";
}
[download]

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" },
);
[download]

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";
}
[download]

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

Comment on Trouble with Join/Prefetch in DBIx::Class Select or Download Code

Replies are listed 'Best First'.
Re: Trouble with Join/Prefetch in DBIx::Class by duff (Parson) on Jan 09, 2018 at 23:57 UTC
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) duff	[reply] [d/l] [select]
Re: Trouble with Join/Prefetch in DBIx::Class by Mr. Muskrat (Canon) on Jan 10, 2018 at 16:56 UTC
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.	[reply]
Re: Trouble with Join/Prefetch in DBIx::Class by poj (Abbot) on Jan 10, 2018 at 20:12 UTC
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; } }` [download] poj	[reply] [d/l]


Your skill will accomplish what the force of many cannot
	PerlMonks