[Dbix-class] New user with JOIN problem

[Matt S Trout]

On Tue, May 12, 2009 at 12:10:53PM +0100, Chris Cole wrote:
> 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"

You're confusing table names and relationship names.

The params to the join are the joins you've predefeined as relationships
via belongs_to, has_many etc. in your result class files.

Suggest fixing your database to be all InnoDB with proper foreign keys so
MySQL doesn't silently drop them, then using DBIx::Class::Schema::Loader
to generate skeleton files and have a look at what's in there.

Then we can make a start at helping you - currently your database is too
broken.

-- 
        Matt S Trout         Catalyst and DBIx::Class consultancy with a clue
     Technical Director      and a commit bit: http://shadowcat.co.uk/catalyst/
 Shadowcat Systems Limited
  mst (@) shadowcat.co.uk        http://shadowcat.co.uk/blog/matt-s-trout/