[Dbix-class] joining subqueries

[Diab Jerius]

Howdy!

I have a relatively simple setup, just two Result classes shadowing
tables, with a
one-to-many relationship between them.

The Parameters class has many Runs, via a common column model_id
(which is a primary key in Parameters, and a foreign key in Runs );

I am performing some aggregated analysis on Runs, which should then
get joined with Parameters.

The aggregated analysis is actually two passes through Runs, i.e.

(SELECT
         analysis,
         model_id,
         counts_per_frame,
         AVG( nframes ) AS nframes_populated,
         AVG( CAST( npiled_up AS DOUBLE PRECISION ) / nframes ) AS
npiled_up_ave,
         STDDEV_SAMP( CAST(npiled_up AS DOUBLE PRECISION ) / nframes )
AS npiled_up_dev
   FROM  pileup_vs_rate_runs
   GROUP BY
         analysis,
         model_id,
         counts_per_frame
) AS S1


(  SELECT
         analysis,
         model_id,
         avg( nframes ) AS nframes_total
   FROM  pileup_vs_rate_runs
   GROUP BY
         analysis,
         model_id
) AS S2


These then are joined on (analysis, model_id) and then joined with
Parameters on model_id.  In SQL it looks like the following:

SELECT *
FROM <S1>
JOIN <S2>
  USING ( model_id, analysis )
JOIN  pileup_vs_rate_parameters
  USING ( model_id )
ORDER BY
      analysis asc,
      model_id asc,
      counts_per_frame desc
;


where <S1> and <S2> are the SQL statements above.


I can perform a join of Parameters with the first subquery with this code:

my $rs = $schema->resultset( 'Parameters' )->search_related( 'runs' )->search(
    {},
    {
        select => [
            qw( analysis runs.model_id counts_per_frame  ),
            {
                avg => 'nframes',
                -as => 'nframes_populated'
            },
            {
                avg => 'CAST( npiled_up AS DOUBLE PRECISION ) / runs.nframes',
                -as => 'npiled_up_ave'
            },
            {
                stddev_samp =>
                  'CAST( npiled_up AS DOUBLE PRECISION ) / runs.nframes',
                -as => 'npiled_up_dev'
            },
        ],
        order_by => [
            { -asc  => 'analysis' },
            { -asc  => 'model_id' },
            { -desc => 'counts_per_frame' }
        ],
        group_by => [qw( analysis runs.model_id counts_per_frame )],
    },
);

But now I'm stuck.  How do I perform a join with the second subquery?
According to the docs,

  "Subqueries are supported in the where clause (first hashref), and
  in the from, select, and +select attributes."

But not join.  Do I need to set up another Result class which performs
the subselect and join with that?

Any help is appreciated.

Thanks,

Diab