[Dbix-class] Multi-table join problems

[wreis]

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.

-- 
     wallace reis/wreis         Catalyst and DBIx::Class consultancy with a clue
     Software Developer		and a commit bit: http://shadowcat.co.uk/catalyst/
 Shadowcat Systems Limited
 http://www.shadowcat.co.uk     http://www.linkedin.com/in/wallacereis