NAME

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

DESCRIPTION

Output the results of the query

SELECT * FROM film f 
WHERE film_id 
  IN ( SELECT film_id FROM film_category fc WHERE fc.film_category IN (6,11) );

Sample Usage:

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

RECIPES

DBIx::Class

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


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

=for SQL

SELECT * FROM film f 
WHERE film_id 
   IN ( SELECT film_id FROM film_category fc WHERE fc.film_category IN (6,11) );

=cut

# I think a view is much simpler dont you :)

  my $sub_rs = do { 
    my $where = { category_id => [qw/6 11/] } ;
    my $attr = {};
    $self->app->schema->resultset('FilmCategory')->search($where, $attr);
  };

  my $rs = do {
    my $where = { film_id => { IN => $sub_rs->get_column('film_id')->as_query } } ;
    my $attr = {};
    $self->app->schema->resultset('Film')->search($where, $attr);
  };

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

1;