[Dbix-class] Multi-table join problems

[Dan Horne]

On 11/07/2009, at 12:29 AM, wreis wrote:

> On Thu, Jul 9, 2009 at 8:19 PM, Dan Horne<dan.horne at redbone.co.nz>  
> wrote:
>>
>> Hi
>> I have a structure like the one below - ep_config has many  
>> ep_sections which
>> in turn has man ep_process_runs:
>> +----------------+        +-----------------+     +-----------------+
>>
>> | ep_config      |        | ep_section      |     | ep_process_run  |
>> |                |       /|                 |    /|                 |
>> | config_id (PK) |--------|-section_id (PK) |-----| process_id (PK) |
>> | config_name    |       \| config_id (FK)  |    \| section_id (FK) |
>> |                |        |                 |     |                 |
>> +----------------+        +-----------------+     +-----------------+
>> I want to find all ep_process_runs where I know the config_name.  
>> I've tried
>> the following:
>> my $rs = $schema->resultset('ETL::Pipeline::Schema::EpProcessRun')- 
>> >search(
>>     {
>>         'config_name' => $config_name
>>     },
>>     {
>>         join => {'section_id' => 'config_id'},
>>         order_by => 'config_name'
>>     }
>> );
>
> You could do this as well:
>
> # if you have a unique constraint over config_name, you can use 'find'
> instead here too
> my $ep_config =
> $schema->resultset('ETL::Pipeline::Schema::EpConfig')->search_rs({
>    config_name => $config_name
> }, { rows => '1' })->single;
> my $rs = $ep_config->ep_sections- 
> >search_related_rs('ep_process_runs');
>
>> and I tried the condition
>> 'config_id.config_name' => $config_name
>> which gives
>> Can't locate object method "config_id"
>> The schema was reverse engineered sing DBIx::Class::Schema::Loader,  
>> and it
>> names all belongs_to relationships using the column name specified  
>> in the
>> FK/PK relationship. E.g. in ETL::Pipeline::Schema::EpSection it is  
>> defined
>> as
>> __PACKAGE__->belongs_to(
>>   "config_id",
>>   "ETL::Pipeline::Schema::EpConfig",
>>   { config_id => "config_id" },
>> );
>
> Change the relname "config_id" to something different like
> "ep_config", it's conflicting with 'config_id' column name.

That's what I did do in the end, but was trying to avoid this a)  
because I don't want to change what DBix::Class::Schema::Loader  
generates because then I'd need to change things every time I re- 
generate the schema, and b) I was trying to operate under the   
philosophy that I don't want to know about DBIC's implementation  
details under the covers, but in the end, I needed tobe cognizant of  
it. I don't know why DBIC::S::L names the relationships the same as  
the columns - it actually threw me for a loop when I first started  
using DBIC because none of the doc tutorials or examples did this, and  
I found it very confusing.

thanks,

Dan