[Dbix-class] Complex table joins

[Richard Jones]

Matt S Trout wrote:
>> Here's what I have:
>>
>> Schema::Foo
>> __PACKAGE__->belongs_to( 'bar', 'Schema::Bar', 'bar_id',
>>   { join_type => 'left' } );
>>
>> Schema::Bar
>> __PACKAGE__->has_many( 'foos', 'Schema::Foo', 'bar_id',
>>   { join_type => 'left' } );
>> __PACKAGE__->belongs_to( 'baz', 'Schema::Baz', 'baz_id',
>>   { join_type => 'left' } );
>>
>> Schema::Baz
>> __PACKAGE__->has_many( 'bars', 'Schema::Bar', 'baz_id',
>>   { join_type => 'left' });
>>
>>> You don't. You define a rel in bar and do
>>>
>>> join => { name_of_foo_to_bar_rel => 'name_of_bar_to_baz_rel' }
>> OK, so:
>> name_of_foo_to_bar_rel = foos (has_many rel. in Schema::Bar)
>> name_of_bar_to_baz_rel = bars (has_many rel. in Schema::Baz)
> 
> No. Schema::Foo defines a belongs_to 'bar'
> 
> Schema::Bar defines a belongs_to 'baz'
>  
>> In Controller:Foo:
>>
>> join => [ { foos => 'bars' }, @other_relationship_names ],
> 
> SO
> 
> { bar => 'baz' }
> 
>> Here's what I get:
>> Caught exception in MyApp::Controller::Search->db_lookup 
>> "DBIx::Class::ResultSet::pager(): No such relationship foos at ...
> 
> You're using the names backwards. Of course it doesn't work.
> 

{ bar => 'baz' }. Right! It's nearly working now. The table joins are 
correct, and providing I don't request a field which needs this 
relationship, it's fine. So this works:

my $rs = $c->model('Schema::Foo')->search( $search_criteria_href,
   $select_href );

The table join defined by the { bar => 'baz' } relationship is generated 
correctly in the sql, but is not actually required for this search.

But this one still gives the 'no such relationship foo' error:
$c->stash->{results} =
   $c->model('Schema::Foo')->search($search_criteria_href,
   $select_href)->single;

The only difference is that search->()->single is called when $rs->count 
== 1, and more fields are retrieved ($select_href contains the offending 
field in the select => \@fields).

What I don't understand is that the sql generated by the 
search->()->single is valid, contains the correct table join syntax, and 
retrieves the correct data when run manually. So why is it still 
throwing the 'no such relationship' error?
-- 
Richard Jones