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

[Malcolm Herbert]

I've created a Catalyst app using database schema classes as provided by
DBIx::Class - I have a database with two tables:

  create table channel (
    channel uuid not null primary key,
    name text not null,
    description text not null,
    url text not null,
  );
  
  create table show (
    show uuid not null primary key,
    channel uuid not null references channel,
    name text not null,
    description text not null,
    url text,
  );
  
I've used the DBIx::Class automatic schema class creator to create
the appropiate result set classes, so in my test code this works:

  my $chan_rs = $schema->resultset('Channel')->search(
    { 'channel' => 'db9c299c-04e0-11e0-81e8-8fcb8e6c1511' }
  );
  
  my $chan = $chan_rs->next;
  print $chan->sane . ': ' . $chan->description . "\n";
  
However when I try the following, DBI complains:

  my $show_rs = $chan_rs->search_related('shows');

The error it spits out is:

  DBIx::Class::ResultSet::all(): DBI Exception: DBD::Pg::st execute failed: ERROR:  column reference "channel" is ambiguous
  LINE 1: ... show shows ON shows.channel = me.channel WHERE ( channel = ...
                                                               ^ [for Statement "SELECT shows.show, shows.channel, shows.name, shows.sane, shows.description, shows.url, shows.logo, shows.hosts FROM channel me JOIN show shows ON shows.channel = me.channel WHERE ( channel = ? )" with ParamValues: 1='db9c299c-04e0-11e0-81e8-8fcb8e6c1511'] at exp/test.pl line 26

I can see that the WHERE clause should read 'me.channel = ?' instead of
'channel = ?', and running the query manually with this change yields
the correct result ...

I have been able to get around the issue by modifying my schema to      
append _id to the primary key fields, but I really shouldn't have to do 
that ...                                                                

How would I go about fixing this properly? It sounds like a problem
with the SQL query generator that DBIx::Class uses but as I am working
at several layers removed from it, I'm a little at a loss as to how to
attack this problem ...

Thanks,
Malcolm

-- 
Malcolm Herbert                                This brain intentionally
mjch at mjch.net                                                left blank