[Dbix-class] Trying to add value restrictions in my relationship JOIN

[Geoffrey P Waigh]

Hello,

I have several tables in the following form:
definition_table: (key1, key2, serial, date_active, date_inactive, meta1, meta2)
value_table: (key1, keyA, key2, serial, date_active, date_inactive, data1,data2)

The primary key for the definition_table is (key1, key2, serialD) where serial is an auto-incrementing sequence number.
The primary key for the value_table is (key1, keyA, key2, serialV)

Within the definition_table, the set of records selected by (key1, key2) will have a non-overlapping sequence of time ranges given by date_active,date_inactive.
Same principle applies to value_table and (key1, keyA, key2)  The date ranges of the two tables are completely unrelated; the definitions/values are revised independently.

# Standard relationships
Definition->belongs_to("pivot_table", "Pivot", { key1 => "key1", key2 => "key2" });
Value->belongs_to("pivot_table", "Pivot", { key1 => "key1", key2 => "key2" });
Pivot->has_many("value_table", "Value", { "foreign.key1" => "self.key1", "foreign.key2 => "self.key2" });
Pivot->has_many("definition_table", "Definition", { "foreign.key1" => "self.key1", "foreign.key2 => "self.key2" });

What I would really like is a relationship from the Value table to the Definition table which is further constrained by (definition.date_active <= now() AND now() < definition.date_inactive)  

http://search.cpan.org/~ribasushi/DBIx-Class-0.08102/lib/DBIx/Class/Relationship/Base.pm#add_relationship suggests that I could put in a non-column constraint, and I've made guesses like the following:

Value->add_relationship(
  "current_def",
  "Definition",
  { 'foreign.key1' => 'self.key1',
    'foreign.key2' => 'self.key2',
    'foreign.date_active' => { '<=' => \"now()" },
    'foreign.date_inactive' => { '>' => \"now()" },
  },
  { accessor => 'single' } # want to say there will be 0 or 1 possible matches
);

But DBIx::Class::ResultSource->resolve_condition() will not accept anything other than foreign/self pairs.

If there is a suitable definition row, I can hoist the date constraint into the search clause, but I'm finding that I want to do a LEFT JOIN on the full condition and get back undef's on the meta columns if there is no definition record:

# Only works if we have a current definition
$rs->search(
  { 'me.date_active' => { '<=' => $timestamp },
    'me.date_inactive' => { '>' => $timestamp },
    'definition_table.date_active' => { '<=' => \"now()" },
    'definition_table.date_inactive' => { '>' => "\now()" },
  },
  { select => [ qw/me.key1 keyA me.key2 meta1 meta2 data1 data2/ ],
    join => { pivot_table => 'definition_table' },
    as => [ qw/key1 keyA key2 meta1 meta2 data1 data2/ ] });

# Would like to write something like:
$rs->search(
  { 'me.date_active' => { '<=' => $timestamp },
    'me.date_inactive' => { '>' => $timestamp },
  },
  { select => [ qw/me.key1 keyA me.key2 meta1 meta2 data1 data2/ ],
    join => 'current_def',
    as => [ qw/key1 keyA key2 meta1 meta2 data1 data2/ ] });

so that I would get SQL akin to:

SELECT me.key1, keyA, me.key2, meta1, meta2, data1, data2
  FROM value_table me
 LEFT JOIN definition_table current_def ON (
              current_def.key1 = me.key1
          AND current_def.key2 = me.key2
          AND current_def.date_active <= now()
          AND current_def.date_inactive > now() )
 WHERE (    me.date_active <= $timestamp
        AND me.date_inactive > $timestamp );


Does anyone know how I can achieve this?

Geoffrey Waigh

This email is confidential and subject to important disclaimers and
conditions including on offers for the purchase or sale of
securities, accuracy and completeness of information, viruses,
confidentiality, legal privilege, and legal entity disclaimers,
available at http://www.jpmorgan.com/pages/disclosures/email.