Re: [Dbix-class] joining subqueries

[Peter Rabbitson]

On 01/26/2018 10:12 PM, Diab Jerius wrote:

Howdy!



Hi!

I somehow marked your email is read without having read it D:



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


Currently you can not do this with DBIC-level APIs alone. There is no 
quick fix for this. But that's ok because at any point you can feed 
arbitrary SQL to the chaining structure.


You will have to do some surgery to assemble the query you want, by 
extracting it from $rs1->as_query / $rs2->as_query, and mangling the 
first value of the returned structure.


Then once you have the \[ $sql, @binds ] that you want to see you'd 
simply feed them to:


->search( {}, { from => \[...],  } );

Hope this helps!

___
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

[Dbix-class] Fwd: joining subqueries

[Diab Jerius]

Seems I've been Warnocked.  I'll post this on StackExchange.

-- Forwarded message --
From: Diab Jerius 
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 
JOIN 
  USING ( model_id, analysis )
JOIN  pileup_vs_rate_parameters
  USING ( model_id )
ORDER BY
  analysis asc,
  model_id asc,
  counts_per_frame desc
;


where  and  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