[Dbix-class] New user with JOIN problem

[Chris Cole]

Hi all,

I've been using the perl DBI for quite a while now and am quite
comfortable with it to access my MySQL databases. I thought I'd give
DBIx a go as I've heard good things about it.

However, I'm really struggling to replicate a simple SQL SELECT
statement via a JOIN.

THe two tables are:
CREATE TABLE `seq_data` (
  `id` varchar(255) NOT NULL default '',
  `seq` varchar(255) NOT NULL default '',
  `length` tinyint(3) unsigned NOT NULL default '0',
  `abundance` mediumint(8) unsigned NOT NULL default '0',
  `condensed` enum('Y','N') NOT NULL default 'N',
  `c_abundance` mediumint(8) unsigned default NULL,
  PRIMARY KEY  (`id`),
  KEY `better` (`id`(8))
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `mirbase_hairpin_match` (
  `query_id` varchar(255) NOT NULL default '',
  `miRNA_id` varchar(50) NOT NULL default '',
  `query_start` smallint(5) unsigned NOT NULL default '0',
  `match_start` smallint(5) unsigned NOT NULL default '0',
  `mismatch` tinyint(3) unsigned NOT NULL default '0',
  KEY `mirna_idx` (`miRNA_id`(9)),
  KEY `query_idx` (`query_id`(8)),
  KEY `query_id` (`query_id`),
  CONSTRAINT `mirbase_hairpin_match_ibfk_1` FOREIGN KEY (`query_id`)
REFERENCES `seq_data` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Ultimately, the SQL code I want to replicate is:

SELECT miRNA_id, sum(abundance) as sum
FROM mirbase_hairpin_match, seq_data
WHERE query_id = id
AND query_id LIKE 'mmuD%'
GROUP BY miRNA_id

I've tried breaking it down to a simpler SQL for testing:
SELECT query_id, miRNA_id, abundance
FROM mirbase_hairpin_match
JOIN seq_data ON  query_id = id
AND query_id LIKE 'mmuD%'

but the nearest I can get to is (code snippet):
my @rnas = $schema->resultset(MirbaseHairpinMatch)->search(
   {
      query_id => { -like => "$dataset%" },
   },
   {
      columns => [ qw/query_id mirna_id/ ],
      join => { seq_data => 'abundance'},
      group_by => [qw/mirna_id/],
   },
);

printf "Found %d miRNAs in dataset $dataset\n", scalar @rnas;
foreach my $mirna (@rnas) {
   printf "miRNA: %s %d\n", $mirna->mirna_id, $mirna->abundance;
}

This fails with "DBIx::Class::ResultSet::search(): No such relationship
seq_data at ./get_all_miRNA_DBIx.pl line 55"

Any help would be greatly appreciated.