<html><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; "><div><font class="Apple-style-span" face="Courier"><br></font></div><div><font class="Apple-style-span" face="Courier">Hi</font></div><div><font class="Apple-style-span" face="Courier"><br></font></div><div><font class="Apple-style-span" face="Courier">I have a structure like the one below - ep_config has many ep_sections which in turn has man ep_process_runs:</font></div><div><font class="Apple-style-span" face="Courier"><br></font></div><div><font class="Apple-style-span" face="Courier">+----------------+ +-----------------+ +-----------------+ </font></div><div><font class="Apple-style-span" face="Courier">| ep_config | | ep_section | | ep_process_run |</font></div><div><font class="Apple-style-span" face="Courier">| | /| | /| |</font></div><div><font class="Apple-style-span" face="Courier">| config_id (PK) |--------|-section_id (PK) |-----| process_id (PK) |</font></div><div><font class="Apple-style-span" face="Courier">| config_name | \| config_id (FK) | \| section_id (FK) | </font></div><div><font class="Apple-style-span" face="Courier">| | | | | |</font></div><div><font class="Apple-style-span" face="Courier">+----------------+ +-----------------+ +-----------------+</font></div><div><font class="Apple-style-span" face="Courier"><br></font></div><div><font class="Apple-style-span" face="Courier">I want to find all ep_process_runs where I know the config_name. I've tried the following:</font></div><div><font class="Apple-style-span" face="Courier"><br></font></div><div><font class="Apple-style-span" face="Courier"><div>my $rs = $schema->resultset('ETL::Pipeline::Schema::EpProcessRun')->search(</div><div> {</div><div> 'config_name' => $config_name</div><div> },</div><div> {</div><div> join => {'section_id' => 'config_id'},</div><div> order_by => 'config_name'</div><div> }</div><div>);</div><div><br></div><div>which gives the error: Can't locate object method "config_id"</div><div><br></div><div>I tried changing the condition to specify the table_name </div><div><br></div><div><div>'ep_config.config_name' => $config_name</div><div><br></div><div>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</div><div><br></div><div><br></div><div>and I tried the condition</div><div><br></div><div>'config_id.config_name' => $config_name</div><div><br></div><div>which gives</div><div><br></div><div>Can't locate object method "config_id"</div><div><br></div><div>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</div><div><br></div><div>__PACKAGE__->belongs_to(</div><div> "config_id",</div><div> "ETL::Pipeline::Schema::EpConfig",</div><div> { config_id => "config_id" },</div><div>);</div><div><br></div><div>The database is Oracle 11</div><div><br></div><div>Any help appreciated</div><div><br></div><div>Dan</div><div><br></div><div><br></div><div> </div></div><div><br></div><div><br></div></font></div></body></html>