lib/Data/Query/Renderer/SQL/Slice/RowNum.pm

   1 package Data::Query::Renderer::SQL::Slice::RowNum;
   2
   3 use Data::Query::ExprHelpers;
   4 use Moo::Role;
   5
   6 with 'Data::Query::Renderer::SQL::Slice::SubqueryRemap';
   7
   8 sub slice_subquery {
   9   (limit => 1, offset => 1);
  10 }
  11
  12 sub slice_stability {
  13   (offset => 'check');
  14 }
  15
  16 sub _render_slice {
  17   my ($self, $dq) = @_;
  18   die "Slice's inner is not a Select"
  19     unless is_Select my $orig_select = $dq->{from};
  20   my %remapped = $self->_subquery_remap_select($orig_select);
  21   my $inside_select = Alias(
  22     $remapped{default_inside_alias},
  23     Select($remapped{inside_select_list}, $orig_select->{from}),
  24   );
  25   unless ($dq->{offset}) {
  26     return $self->render(
  27       Select(
  28         $remapped{outside_select_list},
  29         Where(
  30           Operator(
  31             { 'SQL.Naive' => '<=' },
  32             [
  33               Literal(SQL => 'ROWNUM'),
  34               $dq->{limit}
  35             ]
  36           ),
  37           $inside_select
  38         )
  39       )
  40     );
  41   }
  42   my ($limit_plus_offset, $offset_plus) = (
  43     { %{$dq->{limit}}, value => $dq->{limit}{value}+$dq->{offset}{value} },
  44     { %{$dq->{limit}}, value => $dq->{offset}{value}+1 }
  45   );
  46
  47   my $rownum_name = 'rownum__index';
  48
  49   if ($dq->{order_is_stable}) {
  50     return $self->render(
  51       Select(
  52         $remapped{outside_select_list},
  53         Where(
  54           Operator(
  55             { 'SQL.Naive' => '>=' },
  56             [ Identifier($rownum_name), $offset_plus ]
  57           ),
  58           Alias(
  59             $remapped{default_inside_alias},
  60             Select(
  61               [ @{$remapped{outside_select_list}},
  62                 Alias($rownum_name, Literal(SQL => 'ROWNUM')) ],
  63               Where(
  64                 Operator(
  65                   { 'SQL.Naive' => '<=' },
  66                   [ Literal(SQL => 'ROWNUM'), $limit_plus_offset ]
  67                 ),
  68                 $inside_select,
  69               )
  70             )
  71           )
  72         )
  73       )
  74     );
  75   } else {
  76     return $self->render(
  77       Select(
  78         $remapped{outside_select_list},
  79         Where(
  80           Operator(
  81             { 'SQL.Naive' => 'BETWEEN' },
  82             [ Identifier($rownum_name), $offset_plus, $limit_plus_offset ]
  83           ),
  84           Alias(
  85             $remapped{default_inside_alias},
  86             Select(
  87               [ @{$remapped{outside_select_list}},
  88                 Alias($rownum_name, Literal(SQL => 'ROWNUM')) ],
  89               $inside_select
  90             )
  91           )
  92         )
  93       )
  94     );
  95   }
  96 }
  97
  98 1;