[Dbix-class] New user with JOIN problem

[Chris Cole]

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

OK.

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

As this was a pre-existing DB I was already loading it with
DBIx::Class::Schema::Loader. The Schema for the MirbaseHairpinMatch.pm
is (generated with DBIx::Class::Schema::Loader=dump_to_dir):
package My::Schema::MirbaseHairpinMatch;

use strict;
use warnings;

use base 'DBIx::Class';

__PACKAGE__->load_components("Core");
__PACKAGE__->table("mirbase_hairpin_match");
__PACKAGE__->add_columns(
  "query_id",
  { data_type => "VARCHAR", default_value => "", is_nullable => 0, size
=> 255 },
  "mirna_id",
  { data_type => "VARCHAR", default_value => "", is_nullable => 0, size
=> 50 },
  "query_start",
  { data_type => "SMALLINT", default_value => 0, is_nullable => 0, size
=> 5 },
  "match_start",
  { data_type => "SMALLINT", default_value => 0, is_nullable => 0, size
=> 5 },
  "mismatch",
  { data_type => "TINYINT", default_value => 0, is_nullable => 0, size
=> 3 },
);
__PACKAGE__->belongs_to("query_id", "My::Schema::SeqData", { id =>
"query_id" });


# Created by DBIx::Class::Schema::Loader v0.04006 @ 2009-05-12 14:23:40
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:nkoo7rQ9fLJ7ZRNbIjAvgg


# You can replace this text with custom content, and it will be
preserved on regeneration
1;

I don't know if this is a 'proper' foreign key assignment, but it's what
works in my MySQL implementation (see above). So, which is the
relationship name?

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

That's what's confusing me. How come the SQL works fine?

I'm currently redoing the schema for this DB (the above is only a
subset) as it's scope has changed so would like to create it such that
DBIx queries will trivial to do.
Thanks.