Multi-table join problems

[Dan Horne]

Some javascript/style in this post has been disabled (why?)

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

 

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[Wallace Reis]

On Thu, Jul 9, 2009 at 8:19 PM, Dan Horne<[hidden email]> 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

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[Dan Horne]

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

> On Thu, Jul 9, 2009 at 8:19 PM, Dan Horne<[hidden email]>  
> 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

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[Wallace Reis]

On Fri, Jul 10, 2009 at 1:06 PM, Dan Horne<[hidden email]> wrote:
> 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.

You can use DBIC::Schema::Loader => 0.04999_06. The '_id' part was
stripped in that version, but be aware that it's a DEV RELEASE.

--
     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

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...