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. _______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[email protected]
