[Dbix-class] Complex join problem

[Chris Cole]

Hi all,

I'm managing to 'cascade' joins along relation's relationships, but only
so far. Is there a limit?

The table definitions are at the bottom, but I'm trying to convert this
SQL into DBIx:
SELECT
db_match.read_id,db_match.seq_id,match_start,match_end,query_start,mismatch,abundance
FROM db_match
JOIN seq_reads ON db_match.read_id = seq_reads.read_id
JOIN reads2expt ON seq_reads.read_id = reads2expt.read_id
WHERE expt_id = 2

The code have is:
my @rs = $schema->resultset('DbMatch')->search(
   {
      expt_id => 2,
   },
   {
      join => {         # join on seq_reads -> reads2expt tables
         read_id => {
           reads2expts => 'read_id'
         }
      },
   }
);

This works, in that I get the right rows back, but I cannot retrieve any
specific column values from the reads2expt table (e.g. 'abundance').
Neither with a get_column() method nor directly. I've tried things like
the following with no joy:

$rs[0]->abundance;
$rs[0]->read_id->reads2expts->read_id->get_column('abundance');

I can get get the data from the other tables no problem, it's just the
reads2expt one. Anyone know where I'm going wrong?
Any help is most appreciated.
Chris

Table classes:
__PACKAGE__->load_components("Core");
__PACKAGE__->table("db_match");
__PACKAGE__->add_columns(
  "seq_id",
  { data_type => "INT", default_value => "", is_nullable => 0, size => 10 },
  "read_id",
  { data_type => "BIGINT", default_value => "", is_nullable => 0, size
=> 20 },
  "search_id",
  { data_type => "SMALLINT", default_value => "", is_nullable => 0, size
=> 5 },
  "match_start",
  { data_type => "SMALLINT", default_value => "", is_nullable => 0, size
=> 5 },
  "match_end",
  { data_type => "SMALLINT", default_value => "", is_nullable => 0, size
=> 5 },
  "query_start",
  { data_type => "SMALLINT", default_value => "", is_nullable => 0, size
=> 5 },
  "mismatch",
  { data_type => "TINYINT", default_value => "", is_nullable => 0, size
=> 3 },
);
__PACKAGE__->belongs_to("seq_id", "DB::Schema::DbSequences", { seq_id =>
"seq_id" });
__PACKAGE__->belongs_to("read_id", "DB::Schema::SeqReads", { read_id =>
"read_id" });
__PACKAGE__->belongs_to(
  "search_id",
  "DB::Schema::SearchParams",
  { search_id => "search_id" },
);

__PACKAGE__->load_components("Core");
__PACKAGE__->table("seq_reads");
__PACKAGE__->add_columns(
  "read_id",
  { data_type => "BIGINT", default_value => undef, is_nullable => 0,
size => 20 },
  "length",
  { data_type => "SMALLINT", default_value => "", is_nullable => 0, size
=> 5 },
  "seq",
  { data_type => "VARCHAR", default_value => "", is_nullable => 0, size
=> 255 },
);
__PACKAGE__->set_primary_key("read_id");
__PACKAGE__->has_many(
  "db_matches",
  "DB::Schema::DbMatch",
  { "foreign.read_id" => "self.read_id" },
);
__PACKAGE__->has_many(
  "genome_matches",
  "DB::Schema::GenomeMatch",
  { "foreign.read_id" => "self.read_id" },
);
__PACKAGE__->has_many(
  "reads2expts",
  "DB::Schema::Reads2expt",
  { "foreign.read_id" => "self.read_id" },
);

__PACKAGE__->load_components("Core");
__PACKAGE__->table("reads2expt");
__PACKAGE__->add_columns(
  "expt_id",
  { data_type => "SMALLINT", default_value => "", is_nullable => 0, size
=> 5 },
  "read_id",
  { data_type => "BIGINT", default_value => "", is_nullable => 0, size
=> 20 },
  "abundance",
  { data_type => "INT", default_value => "", is_nullable => 0, size => 10 },
  "read_name",
  { data_type => "VARCHAR", default_value => "", is_nullable => 0, size
=> 255 },
);
__PACKAGE__->belongs_to("expt_id", "DB::Schema::Experiment", { expt_id
=> "expt_id" });
__PACKAGE__->belongs_to("read_id", "DB::Schema::SeqReads", { read_id =>
"read_id" });