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

[Denis BAURAIN]

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.

The query uses four tables with the following relevant relationships:

1. miRNA table

__PACKAGE__->belongs_to(
  "species",
  "Bio::DB::miRBase::Result::species",
  { auto_id => "auto_species" },
);

__PACKAGE__->has_many(
  "miRNA_matures",
  "Bio::DB::miRBase::Result::miRNA_mature",
  { "foreign.auto_mirna" => "self.auto_mirna" },
);


2. species table

__PACKAGE__->has_many(
  "miRNAs",
  "Bio::DB::miRBase::Result::miRNA",
  { "foreign.auto_species" => "self.auto_id" },
);


3. miRNA_mature (many-to-many bridging table)

__PACKAGE__->belongs_to(
  "mature",
  "Bio::DB::miRBase::Result::mature",
  { auto_mature => "auto_mature" },
);

__PACKAGE__->belongs_to(
  "miRNA",
  "Bio::DB::miRBase::Result::miRNA",
  { auto_mirna => "auto_mirna" },
);


4. mature table

__PACKAGE__->has_many(
  "miRNA_matures",
  "Bio::DB::miRBase::Result::miRNA_mature",
  { "foreign.auto_mature" => "self.auto_mature" },
);


I want to start from miRNA table, then filter rows based on a column in
species table, and finally return two columns from mature table (through
the miRNA_mature bridging table). I also want to avoid duplicate rows
arising from the many-to-many relationship. My code is as follows:

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

DBIC_TRACE=1 time perl -Ilib t/scratchpad.t 
ok 1 - database connection
SELECT mature.mature_acc, mature.mature_name FROM mirna me JOIN
mirna_species species ON species.auto_id = me.auto_species LEFT JOIN
mirna_pre_mature miRNA_matures ON miRNA_matures.auto_mirna =
me.auto_mirna LEFT JOIN mirna_mature mature ON mature.auto_mature =
miRNA_matures.auto_mature WHERE ( species.name = ? ) GROUP BY
mature.mature_acc, mature.mature_name: 'Homo sapiens'
No such relationship 'mature' on miRNA at /Users/denis/perl5/perlbrew/
perls/perl-5.10.1/lib/site_perl/5.10.1/DBIx/Class/Schema.pm line 1037
	DBIx::Class::Schema::throw_exception('Bio::DB::miRBase=HASH(0x8f3350)',
'No such relationship \'mature\' on miRNA') called at /Users/denis/perl5/
perlbrew/perls/perl-5.10.1/lib/site_perl/5.10.1/DBIx/Class/
ResultSource.pm line 1562
	DBIx::Class::ResultSource::throw_exc
eption('DBIx::Class::ResultSource::Table=HASH(0x8137f0)', 'No such
relationship \'mature\' on miRNA') called at /Users/denis/perl5/perlbrew/
perls/perl-5.10.1/lib/site_perl/5.10.1/DBIx/Class/ResultSource.pm line 1511
	DBIx::Class::ResultSource::related_s
ource('DBIx::Class::ResultSource::Table=HASH(0x8137f0)', 'mature')
called at /Users/denis/perl5/perlbrew/perls/perl-5.10.1/lib/site_perl/
5.10.1/DBIx/Class/Row.pm line 1142
	DBIx::Class::Row::inflate_result('Bio::DB::miRBase::Result::miRNA',
'DBIx::Class::ResultSource::Table=HASH(0x8137f0)', undef,
'HASH(0xf40a70)') called at /Users/denis/perl5/perlbrew/perls/
perl-5.10.1/lib/site_perl/5.10.1/DBIx/Class/ResultSet.pm line 976
	DBIx::Class::ResultSet::_construct_o
bject('Bio::DB::miRBase::ResultSet::miRNA=HASH(0xba2a50)',
'MIMAT0000062', 'hsa-let-7a') called at /Users/denis/perl5/perlbrew/
perls/perl-5.10.1/lib/site_perl/5.10.1/DBIx/Class/ResultSet.pm line 1372
	DBIx::Class::ResultSet::all('Bio::DB
::miRBase::ResultSet::miRNA=HASH(0xba2a50)') called at t/scratchpad.t line 15
1..1
# Looks like your test exited with 2 just after 1.
        0.90 real         0.81 user         0.07 sys
leela:Bio-DB-miRBase denis$ 

leela:Bio-DB-miRBase denis$ sqlite3 database/database.sqlite 
SQLite version 3.4.0
Enter ".help" for instructions
sqlite> SELECT mature.mature_acc, mature.mature_name FROM mirna me JOIN
mirna_species species ON species.auto_id = me.auto_species LEFT JOIN
mirna_pre_mature miRNA_matures ON miRNA_matures.auto_mirna =
me.auto_mirna LEFT JOIN mirna_mature mature ON mature.auto_mature =
miRNA_matures.auto_mature WHERE ( species.name = 'Homo sapiens' ) GROUP
BY mature.mature_acc, mature.mature_name;
MIMAT0000062|hsa-let-7a
MIMAT0000063|hsa-let-7b
MIMAT0000064|hsa-let-7c
MIMAT0000065|hsa-let-7d
MIMAT0000066|hsa-let-7e
MIMAT0000067|hsa-let-7f
MIMAT0000068|hsa-miR-15a
MIMAT0000069|hsa-miR-16
MIMAT0000070|hsa-miR-17
MIMAT0000071|hsa-miR-17*
MIMAT0000072|hsa-miR-18a
MIMAT0000073|hsa-miR-19a
MIMAT0000074|hsa-miR-19b
MIMAT0000075|hsa-miR-20a
MIMAT0000076|hsa-miR-21
[SNIP]

Bonus question: I also have accessors for computed attributes in
Result::mature. Is it possible to use them on a variant of the previous
resultset without re-finding each row using the primary key?

Thanks,
Denis

A+
Denis