Seems I've been Warnocked. I'll post this on StackExchange. ---------- Forwarded message ---------- From: Diab Jerius <djer...@cfa.harvard.edu> Date: Fri, Jan 26, 2018 at 4:12 PM Subject: joining subqueries To: dbix-class@lists.scsys.co.uk
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 _______________________________________________ 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