NAME

DBIx::Cookbook::Recipe::Searching::subquery_correlated - correlated subquery

DESCRIPTION

Output the results of the query

SELECT
 *
FROM
 payment p_outer
WHERE 
 amount > (SELECT AVG(amount) 
           FROM payment p_inner 
           WHERE p_outer.customer_id=p_inner.customer_id)

Sample Usage:

shell> ${orm}_cmd subquery_correlated  # orm = dbic, skinny, rose, etc

RECIPES

DBIx::Class

package DBIx::Cookbook::DBIC::Command::subquery_correlated;
use Moose;
extends qw(MooseX::App::Cmd::Command);


sub execute {
  my ($self, $opt, $args) = @_;

=for SQL

SELECT
  *
FROM
  payment p_outer
WHERE 
  amount > (SELECT AVG(amount) FROM payment p_inner WHERE p_outer.customer_id=p_inner.customer_id)

=cut

    my $p_rs = $self->app->schema->resultset('Payment');

  my $rs = $p_rs->search
    ({
      amount => { '>' => $p_rs->search
		  (
		   { customer_id => { '=' => \'me.customer_id' } }, 
                   { alias => 'p_inner' }
                  )->get_column('amount')->func_rs('AVG')->as_query
                }
       });


  while (my $row = $rs->next) {
    use Data::Dumper;
    my %data = $row->get_columns;
    warn Dumper(\%data);
  }
}

1;