(Yet Another) DBIx::Class Joins question

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

I have (courtesy of DBIx::Class::Loader a schema which describes (correctly) the following database structure:

  TSeminar --+
             +--TMarket    
             +--TProgram--TFees
[download]

In other words, in TSeminar.pm I see:

__PACKAGE__->belongs_to(
  "marketcode",
  "FOO::Schema::Scheme::TMarket",
  { citycode => "marketcode" },
);
__PACKAGE__->belongs_to(
  "programcode",
  "FOO::Schema::Scheme::TProgram",
  { programcode => "programcode" },
);
[download]

and in TProgram.pm:

__PACKAGE__->belongs_to(
  "progfee",
  "FOO::Schema::Scheme::TFees",
  { feeid => "progfee" },
);
[download]

A simple two-way join that considers only the leftmost three tables works:

    my $rs= $c->schema->resultset("TSeminar")->search(
        { 'semcode' => $code },
        { "join" =>  ['programcode', 'marketcode'],
        "prefetch" => [qw/programcode marketcode/], 
        "order_by"    => [qw/semcode/], 
        "rows"        => 30});
[download]

Now, I want to apply the example from DBIx::Class::Manual::Joining:

Or combine the two:
join => { room => [ 'chair', { table => 'leg' } ]

I cannot, for the life of me, make it work! Here's what I am trying:

join => [ 'marketcode', {'programcode'=>'progfee'} ]

and at runtime I get this error:
Can't locate object method "progfee" via package "FOO::Schema::Scheme::TSeminar"

I emphasized the two words because it seems to me that it's looking for progfee, which (I am trying to tell it...) is accessed from TProgram not TSeminar. I think that I am following the cookbook example rather strenuously, although I am fighting the fact that ... :-< ... the POD author resorted to “trying to be funny” instead of trying to be clear. Instead of sticking to the actual example he had previously been using in the document, he wandered off into the land of the completely irrelevant.

I piece-together from his example that he is searching for a (single...) related room from whence you can find a chair and a table that has legs. Well... I'm trying to create the case where the base-table of the query is room.

Replies are listed 'Best First'.
Re: (Yet Another) DBIx::Class Joins question by pileofrogs (Priest) on Jan 28, 2009 at 23:23 UTC
Since no one with a real answer has posted, I'll give my vague proto-answer. If my boss handed me your code and said "Fix it", first I'd set: `BEGIN { use Carp; } $SIG{__DIE__} = sub { Carp::Confess(@_) }; $SIG{__WARN__} = sub { Carp::Cluck(@_) };` [download] .. before loading the troublesome module. Then I could figure out where it's breaking. I might then go read through the modules where it broke and see if I could figure out what it wants that it's not getting. If that didn't immediately solve my problem, I'd build the example with the chairs, room, legs etc.. See if it really works. Maybe it doesn't. Maybe there's problem with your system, or some other installed module is out of date or who know what? Then I'd alter the example a piece at a time to make it into the thing I wanted until it broke, then I'd know what was really breaking it. (Or, as often happens, it doesn't break and you have no idea why, when seemingly identical code broke only moments before... ) Hope that's helpful --Pileofrogs	[reply] [d/l]
Re: (Yet Another) DBIx::Class Joins question by dragonchild (Archbishop) on Feb 03, 2009 at 18:17 UTC
Try `join => { marketcode => { programcode => 'progfee' } }` - note the hashref, not the arrayref. My criteria for good software: Does it work? Can someone else come in, make a change, and be reasonably certain no bugs were introduced?	[reply] [d/l]
Re^2: (Yet Another) DBIx::Class Joins question by locked_user sundialsvc4 (Abbot) on Feb 04, 2009 at 00:16 UTC
I wonder if the problem is `prefetch` ... yes, I think it is. This seems to work: `my $rs= $c->schema->resultset("TSeminar")->search( { 'semcode' => $code }, { "join" => [ 'marketcode', {'programcode'=>'progfee'} ], "prefetch" => [ 'marketcode', {'programcode'=>'progfee'} ], "order_by" => [qw/semcode/], "rows" => 1});` [download] In DBIx::Class::Manual there is a section called “multi-step prefetch” which I had failed to notice before. It says: From 0.04999_05 onwards, `prefetch` can be nested more than one relationship deep using the same syntax as a multi-step join ... And so it appears that this is indeed the syntax that you have to use. I'm writing this because it appears to work (that is to say, “it does not croak anymore”), and I'll now verify that it does work. (Unless I write to say otherwise, “it did.”)