[Melbourne-pm] DBIx::Class join errors ...

[Toby Corkindale]

On 07/03/11 22:59, Malcolm Herbert wrote:
> On Mon, Mar 07, 2011 at 09:23:56AM +1100, Simon Taylor wrote:
> thanks for the tip, unfortunately it didn't pan out - changed all the primary
> key fields to 'id' across the board and made sure each reference restriction
> named the primary key field explicitly, as in:
>
>    create table channel (
>      id uuid not null primary key,
>      :
>      :
>    );
>
>    create table show (
>      id uuid not null primary key,
>      channel uuid not null references channel(id),
>      :
>      :
>    );
>
> ... but I wound up with the same error:
>
> DBIx::Class::ResultSet::all(): DBI Exception: DBD::Pg::st execute failed: ERROR: column reference "id" is ambiguous
> LINE 1: ...e JOIN show shows ON shows.channel = me.id WHERE ( id = $1 )
>                                                                ^ [for Statement "SELECT shows.id, shows.channel, shows.name, shows.sane, shows.description, shows.url, shows.logo, shows.hosts FROM channel me JOIN show shows ON shows.channel = me.id WHERE ( id = ? )" with ParamValues: 1='db9c299c-04e0-11e0-81e8-8fcb8e6c1511'] at exp/test.pl line 28
>
> looks like I'll just have to use<table>_id for my primary keys ... :(

Hi Malcolm,

I have two methods for you, which both work fine for me here (on 
DBIx::Class 0.08127).

However I think it would be good if you could post your original query 
on the DBIx::Class mailing list, since it does seem like they could do 
better.

The output (including SQL) from my program below is:
SELECT COUNT( * ) FROM channel me:
SELECT COUNT( * ) FROM channel me WHERE ( me.name = ? ): 'BBC 2'
Found 1 channel(s).
SELECT COUNT( * ) FROM channel me JOIN show shows ON shows.channel = 
me.id WHERE ( me.name = ? ): 'BBC 2'
Found 3 shows.
SELECT COUNT( * ) FROM channel me WHERE ( name = ? ): 'BBC 2'
Found 1 channel(s).
SELECT COUNT( * ) FROM (SELECT me.id, me.name FROM channel me WHERE ( 
name = ? )) me JOIN show shows ON shows.channel = me.id: 'BBC 2'
Found 3 shows.




#!/usr/bin/env perl
use 5.12.0;
use warnings;
use Malcolm::Schema;
use autodie;

my $schema = Malcolm::Schema->connect( 'dbi:Pg:dbname=malcolm' );
initial_data($schema) unless $schema->resultset('Channel')->search->count;

# Method one (that works)
{
     my $chan_rs = $schema->resultset('Channel')->search(
         { 'me.name' => 'BBC 2' }
     );
     say "Found " . $chan_rs->count. " channel(s).";

     my $show_rs = $chan_rs->search_related('shows');
     say "Found " . $show_rs->count . " shows.";
}

# Method two (that works)
{
     my $chan_rs = $schema->resultset('Channel')->search(
         { name => 'BBC 2' }
     );
     say "Found " . $chan_rs->count. " channel(s).";

     my $show_rs = $chan_rs->as_subselect_rs->search_related('shows');
     say "Found " . $show_rs->count . " shows.";
}


sub initial_data {
     my $schema = shift;

     my $chan = $schema->resultset('Channel')->create(
         {
             id => new_uuid(),
             name => 'BBC 2',
         }
     );

     map {
         $schema->resultset('Show')->create(
             {
                 id => new_uuid(),
                 name => $_,
                 channel => $chan->id,
             }
         );
     } ('Top Gear', 'Never mind the Buzzcocks', 'QI');

     return;
}

sub new_uuid {
     open my $fh, '/proc/sys/kernel/random/uuid';
     my $uuid = <$fh>;
     close $fh;
     chomp $uuid;
     return $uuid;
}