[Dbix-class] Multi-table join problems

[Dan Horne]

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'
     }
);

which gives the error: Can't locate object method "config_id"

I tried changing the condition to specify the table_name

'ep_config.config_name' => $config_name

DBIx::Class::ResultSet::next(): DBI Exception: DBD::Oracle::db  
prepare_cached failed: ORA-00904: "EP_CONFIG"."CONFIG_NAME": invalid  
identifier (DBD ERROR: error possibly near <*> indicator at char 301  
in 'SELECT me.process_id, me.section_id, me.status_id, me.session_id,  
me.message, me.os_process_id, me.date_created, me.date_updated FROM  
ep_process_run me  JOIN ep_section section_id ON section_id.section_id  
= me.section_id  JOIN ep_config config_id ON config_id.config_id =  
section_id.config_id WHERE ( <*>ep_config.config_name = :p1 ) ORDER BY  
config_name') [for Statement "SELECT me.process_id, me.section_id,  
me.status_id, me.session_id, me.message, me.os_process_id,  
me.date_created, me.date_updated FROM ep_process_run me  JOIN  
ep_section section_id ON section_id.section_id = me.section_id  JOIN  
ep_config config_id ON config_id.config_id = section_id.config_id  
WHERE ( ep_config.config_name = ? ) ORDER BY config_name"] at /home/ 
dhorne/adhoc/test.pl line 33


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

The database is Oracle 11

Any help appreciated

Dan





-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20090710/781ec378/attachment.htm