[Dbix-class] DBIC_TRACE query OK but code throws an exception (many-to-many relationship)

[Peter Rabbitson]

Denis BAURAIN wrote:
> Hi,
> 
> I am new to DBIC and to this list. Thus, I apologise in advance if my
> first question has already been asked a thousand times.
> 
> I am struggling to convert an existing SQL query into DBIC parlance...
> Oddly, DBIC_TRACE shows that the expected query is indeed built but my
> Perl code throws an error. I would appreciate any enlightenment about
> what I am necessarily doing wrong... I provide the minimal context below.
> 
> 
> my @matures = $schema->resultset('miRNA')
> 	->search( {'species.name' => 'Homo sapiens'}, {
> 		'join' => ['species', {'miRNA_matures' => 'mature'}],
> 		'distinct' => 1,
> 		'columns' => [qw/mature.mature_acc mature.mature_name/]
> 	})->all
> ;
> 
> Here's the output with the error. If I fill the '?' placeholder and run
> the traced query against the database, it gives me what I want (see below).
> 
> No such relationship 'mature' on miRNA at /Users/denis/perl5/perlbrew/

Sorry for the late reply, I thought someone else replied already. The problem
here is that columns == select/as specifications, so what you wrote ends
up being:

as => [qw/mature.mature_acc mature.mature_name/]

which translates as:

$main_obj->mature->mature_acc (foo)
$main_obj->mature->mature_name (bar)

What you needed instead was:

as => [qw/miRNA_matures,mature.mature_acc/]
which would correctly populate
$main_obj->miRNA_matures->slice(whichever relation this is)->mature->mature_acc('foo')

I may try to add an early-barf for this but it isn't very easy. Will ponder.

Cheers