[Dbix-class] Joins using sub-selects

[Andrew Beverley]

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