Make "subquery" views join handler work, from views data configuration

The \Drupal\views\Plugin\views\join\Subquery class is misleading, and I think useless as is -- but a join class that joins a subquery would be entirely useful.

I've found several scenarios that would greatly benefit from having views handlers that are based on joining an aggregate subquery. For example, I'm working with a site that has a multi-value daterange field, used to store class start and end times for a multi-day seminar. If it's a 3 day seminar, if you try to include the date field in a view, or add a filter or a sort, you end up with 3 rows for that seminar -- and there's apparently no way you can get this down to a single row without altering the query.

I figured out I could specify a views data structure that would invoke the subquery join handler, and attach a field, filter, and sort handler to it. However, the subquery handler does not generate a subquery for the join -- it only generates a subquery for the "left" side of the join condition -- and only as a string, which cannot access any aliases that might have actually been created in the join -- basically it's impossible to end up with a valid join on a subquery without implementing the ->query method on all views handlers trying to use this subquery.

Here's an example of the views data code:

  $data['rng_date_commerce_product']['table'] = [
    'join' => [
      'commerce_product_field_data'  => [
              'join_id' => 'subquery',
              'left_query' => "SELECT entity_id, min(field_dates_value) as start, max(field_dates_end_value) as end
                FROM commerce_product__field_dates GROUP BY entity_id",
              'left_field' => 'product_id',
              'field' => 'entity_id',
      ];
  ];
  // Can then add 'field', 'filter', and 'sort' keys under a psuedo field key at the same level as 'table'.

... adding that inside a hook_views_data_alter loads up the subquery class successfully -- but this does not work. Through experimentation, I was able to figure out that changing 'left_query' to 'table formula' inserts this into the query where I want it -- however, there's no apparent way to get its alias to specify in the left_query -- and the "table formula" gets treated as a table name -- squished together and wrapped with braces.

"table formula" can be set to a Select query object in a $query method in a views handler, but that does not seem appropriate (or possible) in the views data structure -- and so it does not seem possible.

Just to clarify, the join clause ends up looking like this:

LEFT JOIN {table formula} alias ON (left_query) = base_table.field

... I have never seen a query where you would want something other than the alias of one of the tables or of the subquery, where the "left_query" gets dropped in. And "table formula" is not possible to specify in a way that gets parsed correctly in the views data structure.

I know "table formula" is in the JoinPluginBase class the same way, and we've actually used that in various query() methods or alterations. But it seems to be that the current Subquery class might be repurposed into something actually useful -- a join method that could be configured entirely in views data.

I'm thinking that the buildJoin method can basically support more configuration settings to build an actual Select query object to pass into the addJoin at the end of the method -- which is what needs to happen to make the subquery work at all. Drop the "left_join" configuration pattern, and add other configuration settings and document them... this would provide some extremely powerful, easy-to-use ways to create calculated fields by simply declaring them in views data -- and more, allow those same fields to be sortable and filterable.