On Mon, 8 Oct 2018 15:26:41 +0000 David Cantrell wrote: > I'm having trouble figuring out how to express one of my joins in > DBIx::Class. > > The two tables involved and some relevant sample data are: > > > select * from serviceplan_price; > +----+----------------+-------+-------+---------------------+ > | id | serviceplan_id | type | value | effective_date_time | > +----+----------------+-------+-------+---------------------+ > | 78 | 63 | new | 0.14 | 1973-01-01 00:00:00 | > | 79 | 64 | new | 0.73 | 1982-01-01 00:00:00 | > | 80 | 64 | new | 3.18 | 2012-01-01 00:00:00 | > | 81 | 63 | new | 2.99 | 2019-01-01 00:00:00 | > | 82 | 63 | renew | ... > > Note that the effective date can be in the future. This is how we > represent historical prices, and planned price rises. And that there are > two types, 'new' (the price you pay when you first buy something) and > 'renew' (the price for subsequent renewals). The 'new' price might > include the cost of setting up hardware, for example, which isn't needed > on renewal. > > > select * from serviceplan; > +----+--------------------------------+ > | id | name | irrelevant details ... | > +----+--------------------------------+ > | 63 | foo | blahblah | > | 64 | bar | blahblah | > +----+--------------------------------+ > > And I want to define a relationship so that, along with a serviceplan, I > can fetch its *current* new price or renewal price. For an added wrinkle > we want to be able to mock the current date/time in our tests, so we > can't just use NOW(), but I don't think that's the problem. In plain old > SQL it would look like this for fetching them with their current new price: > > SELECT me.id, me.name, ... > current_new_price.id, ... > FROM serviceplan me > JOIN serviceplan_price current_new_price ON ( > current_new_price.serviceplan_id = me.id AND > current_new_price.type = 'new' AND > current_new_price.effective_date_time = ( > SELECT MAX(effective_date_time) > FROM serviceplan_price > WHERE effective_date_time <= '$mocked' > AND type ='new' > AND serviceplan_id = me.id > ) > ) > > In terms of a DBIx::Class relationship on my serviceplan result class > I've got this (repeated for the current_renew_price): > > __PACKAGE__->belongs_to( > current_new_price => 'MyApp::Result::ServiceplanPrice', > sub ($args) { > my $foreign = $args->{foreign_alias}; > my $me = $args->{self_alias}; > my $mocked_now = MyApp::Mocks->now(), > return { > "$foreign.serviceplan_id" => { -ident => "$me.id" }, > "$foreign.type" => 'new', > "$foreign.effective_date_time" => { -ident => qq{ > ( SELECT MAX(effective_date_time) > FROM serviceplan_price > WHERE effective_date_time <= '$mocked_now' > AND type = 'new' > AND serviceplan_id = $me.id > ) > } } > } > } > ); > > I *think* that I have no choice but to write the relationship condition > as an anonymous sub,
You can actually do this with pure DBIC, using a correlated query with Frew's module: https://blog.afoolishmanifesto.com/posts/introducing-dbix-class-helper-resultset-correlaterelationship/ The blog post is pretty good and should explain it, but shout if you can't work it out. Andy _______________________________________________ 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/dbix-class@lists.scsys.co.uk