[Dbix-class] Ambiguous column references when doing search_related

[Jason Galea]

Hi,

can you use..

my $a_rs = $schema->resultset('B')->search( { 'me.name' => { like =>
'sometext%' } } );

cheers,

J

Daniel Austin wrote:
> Hi
> 
> I have two tables that both have a column called "name". Table A has a
> foreign key "fkey" which is the primary key in table B.  I generate a
> result set of rows from table B, and then perform related search to
> get associated records from table A:
> 
>    my $a_rs = $schema->resultset('B')->search( { name => { like =>
> 'sometext%' } } );
>    [...]
>    $a_rs->search_related('A')->all;
> 
> This throws an exception: the generated SQL contains an ambiguous
> column reference because both tables have a column called "name".
> 
> The generated SQL from the error message is:
> 
>> DBIx::Class::ResultSet::all(): Error executing 'SELECT A.id, A.name,
>> A.description, A.fkey,
>> FROM
>> B me LEFT JOIN A A ON ( A.fkey = me.id ) WHERE (
>> name LIKE ? )': ERROR:  column reference "name" is ambiguous
>> LINE 1: ...A ON ( A.fkey = me.id ) WHERE ( name LIKE ...
> 
> 
> If I run this SQL manually but correct the WHERE clause to be "WHERE (
> me.name like ?)" then the SQL returns the results I was expecting.
> 
> DBIx::Class seems to have carefully disambiguated column names in the
> SELECT part of the query but not in the WHERE part. I can't see a way
> to coax DBIx::Class into fixing that issue. I suppose I could rename
> all the columns so that they're unique across all tables but that
> seems like a weird thing to do and I figure I must be missing
> something else.
> 
> I've compared my hand-written DBIx::Class classes with generated
> classes from DBIx::Class::Schema::Loader since I thought I made an
> error describing the classes but I can't see any significant
> differences.
> 
> Any ideas? Hope someone can help! Thanks.
> 
> [FYI: Using PostgreSQL 8.2.3, DBIx::Class 0.07005, Perl 5.8.6, DBI
> 1.54, DBD::Pg 1.49.]
> 
> Dan
> 
> _______________________________________________
> List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
> Wiki: http://dbix-class.shadowcatsystems.co.uk/
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
> Searchable Archive: 
> http://www.mail-archive.com/dbix-class@lists.rawmode.org/
>