File Coverage

blib/lib/DBIx/Class/Helper/ResultSet/SetOperations.pm

Criterion	Covered	Total	%
statement	41	43	95.3
branch	9	12	75.0
condition	2	3	66.6
subroutine	11	13	84.6
pod	6	6	100.0
total	69	77	89.6

package DBIx::Class::Helper::ResultSet::SetOperations;
$DBIx::Class::Helper::ResultSet::SetOperations::VERSION = '2.035000';
# ABSTRACT: Do set operations with DBIx::Class

use strict;
use warnings;

use parent 'DBIx::Class::ResultSet';

# cribbed from perlfaq4
sub _compare_arrays {
   my ($self, $first, $second) = @_;

   no warnings; # silence spurious -w undef complaints
   return 0 unless @$first == @$second;
   for (my $i = 0; $i < @$first; $i++) {
      return 0 if $first->[$i] ne $second->[$i];
   }
   return 1;
}

sub union {
   shift->_set_operation( UNION => @_ );
}

sub union_all {
   shift->_set_operation( "UNION ALL" => @_ );
}

sub intersect {
   shift->_set_operation( INTERSECT => @_ );
}

sub intersect_all {
   shift->_set_operation( "INTERSECT ALL" => @_ );
}

sub _except_keyword {
   my $self = shift;

   $self->{_except_keyword} ||= ( $self->result_source->schema->storage->sqlt_type eq 'Oracle' ? "MINUS" : "EXCEPT" );
}

sub except {
   my ( $self, @args ) = @_;
   $self->_set_operation( $self->_except_keyword => @args );
}

sub except_all {
   # not supported on most DBs
   shift->_set_operation( "EXCEPT ALL" => @_ );
}

sub _set_operation {
   my ( $self, $operation, $other ) = @_;

   my @sql;
   my @params;

   my $as = $self->_resolved_attrs->{as};

   my @operands = ( $self, ref $other eq 'ARRAY' ? @$other : $other );

   for (@operands) {
      $self->throw_exception("ResultClass of ResultSets do not match!")
         unless $self->result_class eq $_->result_class;

      my $attrs = $_->_resolved_attrs;

      $self->throw_exception('ResultSets do not all have the same selected columns!')
         unless $self->_compare_arrays($as, $attrs->{as});

      my ($sql, @bind) = @{${$_->as_query}};
      $sql =~ s/^\s*\((.*)\)\s*$/$1/;

      push @sql, $sql;
      push @params, @bind;
   }

   my $query = q<(> . join(" $operation ", @sql). q<)>;

   my $attrs = $self->_resolved_attrs;
   return $self->result_source->resultset->search(undef, {
      alias => $self->current_source_alias,
      from => [{
         $self->current_source_alias => \[ $query, @params ],
         -alias                      => $self->current_source_alias,
         -source_handle              => $self->result_source->handle,
      }],
      columns => $attrs->{as},
      result_class => $self->result_class,
   });
}

1;

__END__

=pod

=head1 NAME

DBIx::Class::Helper::ResultSet::SetOperations - Do set operations with DBIx::Class

=head1 SYNOPSIS

 package MyApp::Schema::ResultSet::Foo;

 __PACKAGE__->load_components(qw{Helper::ResultSet::SetOperations});

 ...

 1;

And then elsewhere, like in a controller:

 my $rs1 = $rs->search({ foo => 'bar' });
 my $rs2 = $rs->search({ baz => 'biff' });
 for ($rs1->union($rs2)->all) { ... }

=head1 DESCRIPTION

This component allows you to use various set operations with your ResultSets.
See L<DBIx::Class::Helper::ResultSet/NOTE> for a nice way to apply it to your
entire schema.

Component throws exceptions if ResultSets have different ResultClasses or
different "Columns Specs."

The basic idea here is that in SQL if you use a set operation they must be
selecting the same columns names, so that the results will all match.  The deal
with the ResultClasses is that DBIC needs to inflate the results the same for
the entire ResultSet, so if one were to try to apply something like a union in
a table with the same column name but different classes DBIC wouldn't be doing
what you would expect.

A nice way to use this is with L<DBIx::Class::ResultClass::HashRefInflator>.

You might have something like the following sketch autocompletion code:

 my $rs1 = $schema->resultset('Album')->search({
    name => { -like => "$input%" }
 }, {
   columns => [qw( id name ), {
      tablename => \['?', [{} => 'album']],
   }],
 });

 my $rs2 = $schema->resultset('Artist')->search({
    name => { -like => "$input%" }
 }, {
   columns => [qw( id name ), {
      tablename => \['?', [{} => 'artist']],
   }],
 });

 my $rs3 = $schema->resultset('Song')->search({
    name => { -like => "$input%" }
 }, {
   columns => [qw( id name ), {
      tablename => \['?', [{} => 'song']],
   }],
 });

 $_->result_class('DBIx::Class::ResultClass::HashRefInflator')
   for ($rs1, $rs2, $rs3);

 my $data = [$rs1->union([$rs2, $rs3])->all];

=head1 METHODS

=head2 union

=head2 union_all

=head2 intersect

=head2 intersect_all

=head2 except

=head2 except_all

All of these methods take a single ResultSet or an ArrayRef of ResultSets as
the parameter only parameter.

On Oracle C<except> will issue a C<MINUS> operation.

=head1 AUTHOR

Arthur Axel "fREW" Schmidt <[e-mail]>

=head1 COPYRIGHT AND LICENSE

This software is copyright (c) 2020 by Arthur Axel "fREW" Schmidt.

This is free software; you can redistribute it and/or modify it under
the same terms as the Perl 5 programming language system itself.

=cut