X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FDBIx%2FClass%2FStorage%2FDBIHacks.pm;h=e3fef8b3470265212417dcd53c1d225851ff5b35;hb=0488c7e1294791e01dc75dfe633454d0f4201384;hp=3efd4884a39813da053863b9b494c13038ee5dfe;hpb=908aa1bb761ec1da5c061fe9f687598e3f1934bc;p=dbsrgits%2FDBIx-Class.git

diff --git a/lib/DBIx/Class/Storage/DBIHacks.pm b/lib/DBIx/Class/Storage/DBIHacks.pm
index 3efd488..e3fef8b 100644
--- a/lib/DBIx/Class/Storage/DBIHacks.pm
+++ b/lib/DBIx/Class/Storage/DBIHacks.pm
@@ -16,6 +16,8 @@ use mro 'c3';
 use List::Util 'first';
 use Scalar::Util 'blessed';
 use Sub::Name 'subname';
+use Data::Query::Constants;
+use Data::Query::ExprHelpers;
 use namespace::clean;
 
 #
@@ -23,40 +25,68 @@ use namespace::clean;
 # {from} specs, aiding the RDBMS query optimizer
 #
 sub _prune_unused_joins {
-  my $self = shift;
-  my ($from, $select, $where, $attrs) = @_;
+  my ($self, $attrs) = @_;
+
+  # only standard {from} specs are supported, and we could be disabled in general
+  return ($attrs->{from}, {})  unless (
+    ref $attrs->{from} eq 'ARRAY'
+      and
+    @{$attrs->{from}} > 1
+      and
+    ref $attrs->{from}[0] eq 'HASH'
+      and
+    ref $attrs->{from}[1] eq 'ARRAY'
+      and
+    $self->_use_join_optimizer
+  );
 
-  return $from unless $self->_use_join_optimizer;
+  my $orig_aliastypes = $self->_resolve_aliastypes_from_select_args($attrs);
 
-  if (ref $from ne 'ARRAY' || ref $from->[0] ne 'HASH' || ref $from->[1] ne 'ARRAY') {
-    return $from;   # only standard {from} specs are supported
-  }
+  my $new_aliastypes = { %$orig_aliastypes };
 
-  my $aliastypes = $self->_resolve_aliastypes_from_select_args(@_);
+  # we will be recreating this entirely
+  my @reclassify = 'joining';
 
   # a grouped set will not be affected by amount of rows. Thus any
-  # {multiplying} joins can go
-  delete $aliastypes->{multiplying} if $attrs->{group_by};
+  # purely multiplicator classifications can go
+  # (will be reintroduced below if needed by something else)
+  push @reclassify, qw(multiplying premultiplied)
+    if $attrs->{_force_prune_multiplying_joins} or $attrs->{group_by};
+
+  # nuke what will be recalculated
+  delete @{$new_aliastypes}{@reclassify};
 
-  my @newfrom = $from->[0]; # FROM head is always present
+  my @newfrom = $attrs->{from}[0]; # FROM head is always present
 
+  # recalculate what we need once the multipliers are potentially gone
+  # ignore premultiplies, since they do not add any value to anything
   my %need_joins;
-  for (values %$aliastypes) {
+  for ( @{$new_aliastypes}{grep { $_ ne 'premultiplied' } keys %$new_aliastypes }) {
     # add all requested aliases
     $need_joins{$_} = 1 for keys %$_;
 
     # add all their parents (as per joinpath which is an AoH { table => alias })
-    $need_joins{$_} = 1 for map { values %$_ } map { @$_ } values %$_;
+    $need_joins{$_} = 1 for map { values %$_ } map { @{$_->{-parents}} } values %$_;
   }
-  for my $j (@{$from}[1..$#$from]) {
+
+  for my $j (@{$attrs->{from}}[1..$#{$attrs->{from}}]) {
     push @newfrom, $j if (
-      (! $j->[0]{-alias}) # legacy crap
+      (! defined $j->[0]{-alias}) # legacy crap
         ||
       $need_joins{$j->[0]{-alias}}
     );
   }
 
-  return \@newfrom;
+  # we have a new set of joiners - for everything we nuked pull the classification
+  # off the original stack
+  for my $ctype (@reclassify) {
+    $new_aliastypes->{$ctype} = { map
+      { $need_joins{$_} ? ( $_ => $orig_aliastypes->{$ctype}{$_} ) : () }
+      keys %{$orig_aliastypes->{$ctype}}
+    }
+  }
+
+  return ( \@newfrom, $new_aliastypes );
 }
 
 #
@@ -64,90 +94,159 @@ sub _prune_unused_joins {
 # SELECT me.*, other.* FROM ( SELECT me.* FROM ... ) JOIN other ON ...
 #
 sub _adjust_select_args_for_complex_prefetch {
-  my ($self, $from, $select, $where, $attrs) = @_;
-
-  $self->throw_exception ('Nothing to prefetch... how did we get here?!')
-    if not @{$attrs->{_prefetch_selector_range}};
-
-  $self->throw_exception ('Complex prefetches are not supported on resultsets with a custom from attribute')
-    if (ref $from ne 'ARRAY' || ref $from->[0] ne 'HASH' || ref $from->[1] ne 'ARRAY');
+  my ($self, $attrs) = @_;
+
+  $self->throw_exception ('Complex prefetches are not supported on resultsets with a custom from attribute') unless (
+    ref $attrs->{from} eq 'ARRAY'
+      and
+    @{$attrs->{from}} > 1
+      and
+    ref $attrs->{from}[0] eq 'HASH'
+      and
+    ref $attrs->{from}[1] eq 'ARRAY'
+  );
 
+  my $root_alias = $attrs->{alias};
 
   # generate inner/outer attribute lists, remove stuff that doesn't apply
   my $outer_attrs = { %$attrs };
-  delete $outer_attrs->{$_} for qw/where bind rows offset group_by having/;
+  delete @{$outer_attrs}{qw(from bind rows offset group_by _grouped_by_distinct having)};
+
+  my $inner_attrs = { %$attrs };
+  delete @{$inner_attrs}{qw(for collapse select as _related_results_construction)};
 
-  my $inner_attrs = { %$attrs, _is_internal_subuery => 1 };
-  delete $inner_attrs->{$_} for qw/for collapse _prefetch_selector_range select as/;
+  # there is no point of ordering the insides if there is no limit
+  delete $inner_attrs->{order_by} if (
+    delete $inner_attrs->{_order_is_artificial}
+      or
+    ! $inner_attrs->{rows}
+  );
 
   # generate the inner/outer select lists
   # for inside we consider only stuff *not* brought in by the prefetch
   # on the outside we substitute any function for its alias
-  my $outer_select = [ @$select ];
-  my $inner_select = [];
+  $outer_attrs->{select} = [ @{$attrs->{select}} ];
+
+  my ($root_node, $root_node_offset);
+
+  for my $i (0 .. $#{$inner_attrs->{from}}) {
+    my $node = $inner_attrs->{from}[$i];
+    my $h = (ref $node eq 'HASH')                                ? $node
+          : (ref $node  eq 'ARRAY' and ref $node->[0] eq 'HASH') ? $node->[0]
+          : next
+    ;
+
+    if ( ($h->{-alias}||'') eq $root_alias and $h->{-rsrc} ) {
+      $root_node = $h;
+      $root_node_offset = $i;
+      last;
+    }
+  }
+
+  $self->throw_exception ('Complex prefetches are not supported on resultsets with a custom from attribute')
+    unless $root_node;
 
-  my ($p_start, $p_end) = @{$outer_attrs->{_prefetch_selector_range}};
-  for my $i (0 .. $p_start - 1, $p_end + 1 .. $#$outer_select) {
-    my $sel = $outer_select->[$i];
+  # use the heavy duty resolver to take care of aliased/nonaliased naming
+  my $colinfo = $self->_resolve_column_info($inner_attrs->{from});
+  my $selected_root_columns;
+
+  for my $i (0 .. $#{$outer_attrs->{select}}) {
+    my $sel = $outer_attrs->{select}->[$i];
+
+    next if (
+      $colinfo->{$sel} and $colinfo->{$sel}{-source_alias} ne $root_alias
+    );
 
     if (ref $sel eq 'HASH' ) {
       $sel->{-as} ||= $attrs->{as}[$i];
-      $outer_select->[$i] = join ('.', $attrs->{alias}, ($sel->{-as} || "inner_column_$i") );
+      $outer_attrs->{select}->[$i] = join ('.', $root_alias, ($sel->{-as} || "inner_column_$i") );
+    }
+    elsif (! ref $sel and my $ci = $colinfo->{$sel}) {
+      $selected_root_columns->{$ci->{-colname}} = 1;
     }
 
-    push @$inner_select, $sel;
+    push @{$inner_attrs->{select}}, $sel;
 
     push @{$inner_attrs->{as}}, $attrs->{as}[$i];
   }
 
-  # construct the inner $from and lock it in a subquery
-  # we need to prune first, because this will determine if we need a group_by below
-  # the fake group_by is so that the pruner throws away all non-selecting, non-restricting
-  # multijoins (since we def. do not care about those inside the subquery)
+  # We will need to fetch all native columns in the inner subquery, which may
+  # be a part of an *outer* join condition, or an order_by (which needs to be
+  # preserved outside), or wheres. In other words everything but the inner
+  # selector
+  # We can not just fetch everything because a potential has_many restricting
+  # join collapse *will not work* on heavy data types.
+  my $connecting_aliastypes = $self->_resolve_aliastypes_from_select_args({
+    %$inner_attrs,
+    select => undef,
+  });
+
+  for (sort map { keys %{$_->{-seen_columns}||{}} } map { values %$_ } values %$connecting_aliastypes) {
+    my $ci = $colinfo->{$_} or next;
+    if (
+      $ci->{-source_alias} eq $root_alias
+        and
+      ! $selected_root_columns->{$ci->{-colname}}++
+    ) {
+      # adding it to both to keep limits not supporting dark selectors happy
+      push @{$inner_attrs->{select}}, $ci->{-fq_colname};
+      push @{$inner_attrs->{as}}, $ci->{-fq_colname};
+    }
+  }
 
+  # construct the inner {from} and lock it in a subquery
+  # we need to prune first, because this will determine if we need a group_by below
+  # throw away all non-selecting, non-restricting multijoins
+  # (since we def. do not care about multiplication of the contents of the subquery)
   my $inner_subq = do {
 
-    # must use it here regardless of user requests
+    # must use it here regardless of user requests (vastly gentler on optimizer)
     local $self->{_use_join_optimizer} = 1;
 
-    my $inner_from = $self->_prune_unused_joins ($from, $inner_select, $where, {
-      group_by => ['dummy'], %$inner_attrs,
+    # throw away multijoins since we def. do not care about those inside the subquery
+    ($inner_attrs->{from}, my $inner_aliastypes) = $self->_prune_unused_joins ({
+      %$inner_attrs, _force_prune_multiplying_joins => 1
     });
 
-    my $inner_aliastypes =
-      $self->_resolve_aliastypes_from_select_args( $inner_from, $inner_select, $where, $inner_attrs );
-
-    # we need to simulate collapse in the subq if a multiplying join is pulled
-    # by being a non-selecting restrictor
+    # uh-oh a multiplier (which is not us) left in, this is a problem for limits
+    # we will need to add a group_by to collapse the resultset for proper counts
     if (
-      ! $inner_attrs->{group_by}
+      grep { $_ ne $root_alias } keys %{ $inner_aliastypes->{multiplying} || {} }
         and
-      first {
-        $inner_aliastypes->{restricting}{$_}
-          and
-        ! $inner_aliastypes->{selecting}{$_}
-      } ( keys %{$inner_aliastypes->{multiplying}||{}} )
+      # if there are user-supplied groups - assume user knows wtf they are up to
+      ( ! $inner_aliastypes->{grouping} or $inner_attrs->{_grouped_by_distinct} )
     ) {
-      my $unprocessed_order_chunks;
-      ($inner_attrs->{group_by}, $unprocessed_order_chunks) = $self->_group_over_selection (
-        $inner_from, $inner_select, $inner_attrs->{order_by}
-      );
 
-      $self->throw_exception (
-        'A required group_by clause could not be constructed automatically due to a complex '
-      . 'order_by criteria. Either order_by columns only (no functions) or construct a suitable '
-      . 'group_by by hand'
-      )  if $unprocessed_order_chunks;
+      my $cur_sel = { map { $_ => 1 } @{$inner_attrs->{select}} };
+
+      # *possibly* supplement the main selection with pks if not already
+      # there, as they will have to be a part of the group_by to collapse
+      # things properly
+      my $inner_select_with_extras;
+      my @pks = map { "$root_alias.$_" } $root_node->{-rsrc}->primary_columns
+        or $self->throw_exception( sprintf
+          'Unable to perform complex limited prefetch off %s without declared primary key',
+          $root_node->{-rsrc}->source_name,
+        );
+      for my $col (@pks) {
+        push @{ $inner_select_with_extras ||= [ @{$inner_attrs->{select}} ] }, $col
+          unless $cur_sel->{$col}++;
+      }
+
+      ($inner_attrs->{group_by}, $inner_attrs->{order_by}) = $self->_group_over_selection({
+        %$inner_attrs,
+        $inner_select_with_extras ? ( select => $inner_select_with_extras ) : (),
+        _aliastypes => $inner_aliastypes,
+      });
     }
 
-    # we already optimized $inner_from above
-    local $self->{_use_join_optimizer} = 0;
+    # we already optimized $inner_attrs->{from} above
+    # and already local()ized
+    $self->{_use_join_optimizer} = 0;
 
     # generate the subquery
     $self->_select_args_to_query (
-      $inner_from,
-      $inner_select,
-      $where,
+      @{$inner_attrs}{qw(from select where)},
       $inner_attrs,
     );
   };
@@ -162,45 +261,51 @@ sub _adjust_select_args_for_complex_prefetch {
   # - it is part of the restrictions, in which case we need to collapse the outer
   #   result by tackling yet another group_by to the outside of the query
 
-  $from = [ @$from ];
+  # work on a shallow copy
+  my @orig_from = @{$attrs->{from}};
 
-  # so first generate the outer_from, up to the substitution point
-  my @outer_from;
-  while (my $j = shift @$from) {
-    $j = [ $j ] unless ref $j eq 'ARRAY'; # promote the head-from to an AoH
 
-    if ($j->[0]{-alias} eq $attrs->{alias}) { # time to swap
+  $outer_attrs->{from} = \ my @outer_from;
 
-      push @outer_from, [
-        {
-          -alias => $attrs->{alias},
-          -rsrc => $j->[0]{-rsrc},
-          $attrs->{alias} => $inner_subq,
-        },
-        @{$j}[1 .. $#$j],
-      ];
-      last; # we'll take care of what's left in $from below
-    }
-    else {
-      push @outer_from, $j;
-    }
+  # we may not be the head
+  if ($root_node_offset) {
+    # first generate the outer_from, up to the substitution point
+    @outer_from = splice @orig_from, 0, $root_node_offset;
+
+    # substitute the subq at the right spot
+    push @outer_from, [
+      {
+        -alias => $root_alias,
+        -rsrc => $root_node->{-rsrc},
+        $root_alias => $inner_subq,
+      },
+      # preserve attrs from what is now the head of the from after the splice
+      @{$orig_from[0]}[1 .. $#{$orig_from[0]}],
+    ];
+  }
+  else {
+    @outer_from = {
+      -alias => $root_alias,
+      -rsrc => $root_node->{-rsrc},
+      $root_alias => $inner_subq,
+    };
   }
 
+  shift @orig_from; # what we just replaced above
+
   # scan the *remaining* from spec against different attributes, and see which joins are needed
   # in what role
-  my $outer_aliastypes =
-    $self->_resolve_aliastypes_from_select_args( $from, $outer_select, $where, $outer_attrs );
+  my $outer_aliastypes = $outer_attrs->{_aliastypes} =
+    $self->_resolve_aliastypes_from_select_args({ %$outer_attrs, from => \@orig_from });
 
   # unroll parents
-  my ($outer_select_chain, $outer_restrict_chain) = map { +{
-    map { $_ => 1 } map { values %$_} map { @$_ } values %{ $outer_aliastypes->{$_} || {} }
-  } } qw/selecting restricting/;
+  my ($outer_select_chain, @outer_nonselecting_chains) = map { +{
+    map { $_ => 1 } map { values %$_} map { @{$_->{-parents}} } values %{ $outer_aliastypes->{$_} || {} }
+  } } qw/selecting restricting grouping ordering/;
 
   # see what's left - throw away if not selecting/restricting
-  # also throw in a group_by if a non-selecting multiplier,
-  # to guard against cross-join explosions
-  my $need_outer_group_by;
-  while (my $j = shift @$from) {
+  my $may_need_outer_group_by;
+  while (my $j = shift @orig_from) {
     my $alias = $j->[0]{-alias};
 
     if (
@@ -208,38 +313,31 @@ sub _adjust_select_args_for_complex_prefetch {
     ) {
       push @outer_from, $j
     }
-    elsif ($outer_restrict_chain->{$alias}) {
+    elsif (first { $_->{$alias} } @outer_nonselecting_chains ) {
       push @outer_from, $j;
-      $need_outer_group_by ||= $outer_aliastypes->{multiplying}{$alias} ? 1 : 0;
+      $may_need_outer_group_by ||= $outer_aliastypes->{multiplying}{$alias} ? 1 : 0;
     }
   }
 
-  # demote the outer_from head
-  $outer_from[0] = $outer_from[0][0];
-
-  if ($need_outer_group_by and ! $outer_attrs->{group_by}) {
-
-    my $unprocessed_order_chunks;
-    ($outer_attrs->{group_by}, $unprocessed_order_chunks) = $self->_group_over_selection (
-      \@outer_from, $outer_select, $outer_attrs->{order_by}
-    );
-
-    $self->throw_exception (
-      'A required group_by clause could not be constructed automatically due to a complex '
-    . 'order_by criteria. Either order_by columns only (no functions) or construct a suitable '
-    . 'group_by by hand'
-    ) if $unprocessed_order_chunks;
-
+  # also throw in a synthetic group_by if a non-selecting multiplier,
+  # to guard against cross-join explosions
+  # the logic is somewhat fragile, but relies on the idea that if a user supplied
+  # a group by on their own - they know what they were doing
+  if ( $may_need_outer_group_by and $attrs->{_grouped_by_distinct} ) {
+    ($outer_attrs->{group_by}, $outer_attrs->{order_by}) = $self->_group_over_selection ({
+      %$outer_attrs,
+      from => \@outer_from,
+    });
   }
 
-  # This is totally horrific - the $where ends up in both the inner and outer query
+  # This is totally horrific - the {where} ends up in both the inner and outer query
   # Unfortunately not much can be done until SQLA2 introspection arrives, and even
   # then if where conditions apply to the *right* side of the prefetch, you may have
   # to both filter the inner select (e.g. to apply a limit) and then have to re-filter
-  # the outer select to exclude joins you didin't want in the first place
+  # the outer select to exclude joins you didn't want in the first place
   #
   # OTOH it can be seen as a plus: <ash> (notes that this query would make a DBA cry ;)
-  return (\@outer_from, $outer_select, $where, $outer_attrs);
+  return $outer_attrs;
 }
 
 #
@@ -247,41 +345,47 @@ sub _adjust_select_args_for_complex_prefetch {
 #
 # Due to a lack of SQLA2 we fall back to crude scans of all the
 # select/where/order/group attributes, in order to determine what
-# aliases are neded to fulfill the query. This information is used
+# aliases are needed to fulfill the query. This information is used
 # throughout the code to prune unnecessary JOINs from the queries
 # in an attempt to reduce the execution time.
 # Although the method is pretty horrific, the worst thing that can
 # happen is for it to fail due to some scalar SQL, which in turn will
 # result in a vocal exception.
 sub _resolve_aliastypes_from_select_args {
-  my ( $self, $from, $select, $where, $attrs ) = @_;
+  my ( $self, $attrs ) = @_;
 
   $self->throw_exception ('Unable to analyze custom {from}')
-    if ref $from ne 'ARRAY';
+    if ref $attrs->{from} ne 'ARRAY';
 
   # what we will return
   my $aliases_by_type;
 
   # see what aliases are there to work with
+  # and record who is a multiplier and who is premultiplied
   my $alias_list;
-  for (@$from) {
-    my $j = $_;
+  for my $node (@{$attrs->{from}}) {
+
+    my $j = $node;
     $j = $j->[0] if ref $j eq 'ARRAY';
     my $al = $j->{-alias}
       or next;
 
     $alias_list->{$al} = $j;
-    $aliases_by_type->{multiplying}{$al} ||= $j->{-join_path}||[] if (
+
+    $aliases_by_type->{multiplying}{$al} ||= { -parents => $j->{-join_path}||[] }
       # not array == {from} head == can't be multiplying
-      ( ref($_) eq 'ARRAY' and ! $j->{-is_single} )
-        or
-      # a parent of ours is already a multiplier
-      ( grep { $aliases_by_type->{multiplying}{$_} } @{ $j->{-join_path}||[] } )
-    );
+      if ref($node) eq 'ARRAY' and ! $j->{-is_single};
+
+    $aliases_by_type->{premultiplied}{$al} ||= { -parents => $j->{-join_path}||[] }
+      # parts of the path that are not us but are multiplying
+      if grep { $aliases_by_type->{multiplying}{$_} }
+          grep { $_ ne $al }
+           map { values %$_ }
+            @{ $j->{-join_path}||[] }
   }
 
-  # get a column to source/alias map (including unqualified ones)
-  my $colinfo = $self->_resolve_column_info ($from);
+  # get a column to source/alias map (including unambiguous unqualified ones)
+  my $colinfo = $self->_resolve_column_info ($attrs->{from});
 
   # set up a botched SQLA
   my $sql_maker = $self->sql_maker;
@@ -291,6 +395,7 @@ sub _resolve_aliastypes_from_select_args {
   local $sql_maker->{where_bind};
   local $sql_maker->{group_bind};
   local $sql_maker->{having_bind};
+  local $sql_maker->{from_bind};
 
   # we can't scan properly without any quoting (\b doesn't cut it
   # everywhere), so unless there is proper quoting set - use our
@@ -308,38 +413,104 @@ sub _resolve_aliastypes_from_select_args {
     $sql_maker->{name_sep} = '';
   }
 
+  # delete local is 5.12+
+  local @{$sql_maker}{qw(renderer converter)};
+  delete @{$sql_maker}{qw(renderer converter)};
+
   my ($lquote, $rquote, $sep) = map { quotemeta $_ } ($sql_maker->_quote_chars, $sql_maker->name_sep);
 
   # generate sql chunks
   my $to_scan = {
     restricting => [
-      $sql_maker->_recurse_where ($where),
-      $sql_maker->_parse_rs_attrs ({
-        map { $_ => $attrs->{$_} } (qw/group_by having/)
-      }),
+      ($attrs->{where}
+        ? ($sql_maker->_recurse_where($attrs->{where}))[0]
+        : ()
+      ),
+      ($attrs->{having}
+        ? ($sql_maker->_recurse_where($attrs->{having}))[0]
+        : ()
+      ),
+    ],
+    grouping => [
+      ($attrs->{group_by}
+        ? ($sql_maker->_render_sqla(group_by => $attrs->{group_by}))[0]
+        : (),
+      )
+    ],
+    joining => [
+      $sql_maker->_recurse_from (
+        ref $attrs->{from}[0] eq 'ARRAY' ? $attrs->{from}[0][0] : $attrs->{from}[0],
+        @{$attrs->{from}}[1 .. $#{$attrs->{from}}],
+      ),
     ],
     selecting => [
-      $sql_maker->_recurse_fields ($select),
-      ( map { $_->[0] } $self->_extract_order_criteria ($attrs->{order_by}, $sql_maker) ),
+      map { $sql_maker->_render_sqla(select_select => $_) =~ /^SELECT\s+(.+)/ } @{$attrs->{select}||[]},
+    ],
+    ordering => [
+      map { $_->[0] } $self->_extract_order_criteria ($attrs->{order_by}, $sql_maker),
     ],
   };
 
-  # throw away empty chunks
-  $_ = [ map { $_ || () } @$_ ] for values %$to_scan;
+  # throw away empty chunks and all 2-value arrayrefs: the thinking is that these are
+  # bind value specs left in by the sloppy renderer above. It is ok to do this
+  # at this point, since we are going to end up rewriting this crap anyway
+  for my $v (values %$to_scan) {
+    my @nv;
+    for (@$v) {
+      next if (
+        ! defined $_
+          or
+        (
+          ref $_ eq 'ARRAY'
+            and
+          ( @$_ == 0 or @$_ == 2 )
+        )
+      );
+
+      if (ref $_) {
+        require Data::Dumper::Concise;
+        $self->throw_exception("Unexpected ref in scan-plan: " . Data::Dumper::Concise::Dumper($v) );
+      }
+
+      push @nv, $_;
+    }
 
-  # first loop through all fully qualified columns and get the corresponding
+    $v = \@nv;
+  }
+
+  # kill all selectors which look like a proper subquery
+  # this is a sucky heuristic *BUT* - if we get it wrong the query will simply
+  # fail to run, so we are relatively safe
+  $to_scan->{selecting} = [ grep {
+    $_ !~ / \A \s* \( \s* SELECT \s+ .+? \s+ FROM \s+ .+? \) \s* \z /xsi
+  } @{ $to_scan->{selecting} || [] } ];
+
+  # first see if we have any exact matches (qualified or unqualified)
+  for my $type (keys %$to_scan) {
+    for my $piece (@{$to_scan->{$type}}) {
+      if ($colinfo->{$piece} and my $alias = $colinfo->{$piece}{-source_alias}) {
+        $aliases_by_type->{$type}{$alias} ||= { -parents => $alias_list->{$alias}{-join_path}||[] };
+        $aliases_by_type->{$type}{$alias}{-seen_columns}{$colinfo->{$piece}{-fq_colname}} = $piece;
+      }
+    }
+  }
+
+  # now loop through all fully qualified columns and get the corresponding
   # alias (should work even if they are in scalarrefs)
   for my $alias (keys %$alias_list) {
     my $al_re = qr/
-      $lquote $alias $rquote $sep
+      $lquote $alias $rquote $sep (?: $lquote ([^$rquote]+) $rquote )?
         |
-      \b $alias \.
+      \b $alias \. ([^\s\)\($rquote]+)?
     /x;
 
     for my $type (keys %$to_scan) {
       for my $piece (@{$to_scan->{$type}}) {
-        $aliases_by_type->{$type}{$alias} ||= $alias_list->{$alias}{-join_path}||[]
-          if ($piece =~ $al_re);
+        if (my @matches = $piece =~ /$al_re/g) {
+          $aliases_by_type->{$type}{$alias} ||= { -parents => $alias_list->{$alias}{-join_path}||[] };
+          $aliases_by_type->{$type}{$alias}{-seen_columns}{"$alias.$_"} = "$alias.$_"
+            for grep { defined $_ } @matches;
+        }
       }
     }
   }
@@ -349,13 +520,15 @@ sub _resolve_aliastypes_from_select_args {
   for my $col (keys %$colinfo) {
     next if $col =~ / \. /x;   # if column is qualified it was caught by the above
 
-    my $col_re = qr/ $lquote $col $rquote /x;
+    my $col_re = qr/ $lquote ($col) $rquote /x;
 
     for my $type (keys %$to_scan) {
       for my $piece (@{$to_scan->{$type}}) {
-        if ($piece =~ $col_re) {
+        if ( my @matches = $piece =~ /$col_re/g) {
           my $alias = $colinfo->{$col}{-source_alias};
-          $aliases_by_type->{$type}{$alias} ||= $alias_list->{$alias}{-join_path}||[];
+          $aliases_by_type->{$type}{$alias} ||= { -parents => $alias_list->{$alias}{-join_path}||[] };
+          $aliases_by_type->{$type}{$alias}{-seen_columns}{"$alias.$_"} = $_
+            for grep { defined $_ } @matches;
         }
       }
     }
@@ -364,76 +537,182 @@ sub _resolve_aliastypes_from_select_args {
   # Add any non-left joins to the restriction list (such joins are indeed restrictions)
   for my $j (values %$alias_list) {
     my $alias = $j->{-alias} or next;
-    $aliases_by_type->{restricting}{$alias} ||= $j->{-join_path}||[] if (
+    $aliases_by_type->{restricting}{$alias} ||= { -parents => $j->{-join_path}||[] } if (
       (not $j->{-join_type})
         or
       ($j->{-join_type} !~ /^left (?: \s+ outer)? $/xi)
     );
   }
 
+  for (keys %$aliases_by_type) {
+    delete $aliases_by_type->{$_} unless keys %{$aliases_by_type->{$_}};
+  }
+
   return $aliases_by_type;
 }
 
-# This is the engine behind { distinct => 1 }
+# This is the engine behind { distinct => 1 } and the general
+# complex prefetch grouper
 sub _group_over_selection {
-  my ($self, $from, $select, $order_by) = @_;
+  my ($self, $attrs) = @_;
 
-  my $rs_column_list = $self->_resolve_column_info ($from);
+  my $colinfos = $self->_resolve_column_info ($attrs->{from});
 
   my (@group_by, %group_index);
 
   # the logic is: if it is a { func => val } we assume an aggregate,
   # otherwise if \'...' or \[...] we assume the user knows what is
   # going on thus group over it
-  for (@$select) {
+  for (@{$attrs->{select}}) {
     if (! ref($_) or ref ($_) ne 'HASH' ) {
       push @group_by, $_;
       $group_index{$_}++;
-      if ($rs_column_list->{$_} and $_ !~ /\./ ) {
+      if ($colinfos->{$_} and $_ !~ /\./ ) {
         # add a fully qualified version as well
-        $group_index{"$rs_column_list->{$_}{-source_alias}.$_"}++;
+        $group_index{"$colinfos->{$_}{-source_alias}.$_"}++;
       }
     }
   }
 
+  my $sql_maker = $self->sql_maker;
+  my @order_by = $self->_extract_order_criteria($attrs->{order_by}, $sql_maker)
+    or return (\@group_by, $attrs->{order_by});
+
   # add any order_by parts that are not already present in the group_by
-  # we need to be careful not to add any named functions/aggregates
-  # i.e. order_by => [ ... { count => 'foo' } ... ]
-  my @leftovers;
-  for ($self->_extract_order_criteria($order_by)) {
+  # to maintain SQL cross-compatibility and general sanity
+  #
+  # also in case the original selection is *not* unique, or in case part
+  # of the ORDER BY refers to a multiplier - we will need to replace the
+  # skipped order_by elements with their MIN/MAX equivalents as to maintain
+  # the proper overall order without polluting the group criteria (and
+  # possibly changing the outcome entirely)
+
+  my ($leftovers, @new_order_by, $order_chunks, $aliastypes);
+
+  my $group_already_unique = $self->_columns_comprise_identifying_set($colinfos, \@group_by);
+
+  for my $o_idx (0 .. $#order_by) {
+
+    # if the chunk is already a min/max function - there is nothing left to touch
+    next if $order_by[$o_idx][0] =~ /^ (?: min | max ) \s* \( .+ \) $/ix;
+
     # only consider real columns (for functions the user got to do an explicit group_by)
-    if (@$_ != 1) {
-      push @leftovers, $_;
-      next;
+    my $chunk_ci;
+    if (
+      @{$order_by[$o_idx]} != 1
+        or
+      # only declare an unknown *plain* identifier as "leftover" if we are called with
+      # aliastypes to examine. If there are none - we are still in _resolve_attrs, and
+      # can just assume the user knows what they want
+      ( ! ( $chunk_ci = $colinfos->{$order_by[$o_idx][0]} ) and $attrs->{_aliastypes} )
+    ) {
+      push @$leftovers, $order_by[$o_idx][0];
     }
-    my $chunk = $_->[0];
-    my $colinfo = $rs_column_list->{$chunk} or do {
-      push @leftovers, $_;
-      next;
-    };
 
-    $chunk = "$colinfo->{-source_alias}.$chunk" if $chunk !~ /\./;
-    push @group_by, $chunk unless $group_index{$chunk}++;
+    next unless $chunk_ci;
+
+    # no duplication of group criteria
+    next if $group_index{$chunk_ci->{-fq_colname}};
+
+    $aliastypes ||= (
+      $attrs->{_aliastypes}
+        or
+      $self->_resolve_aliastypes_from_select_args({
+        from => $attrs->{from},
+        order_by => $attrs->{order_by},
+      })
+    ) if $group_already_unique;
+
+    # check that we are not ordering by a multiplier (if a check is requested at all)
+    if (
+      $group_already_unique
+        and
+      ! $aliastypes->{multiplying}{$chunk_ci->{-source_alias}}
+        and
+      ! $aliastypes->{premultiplied}{$chunk_ci->{-source_alias}}
+    ) {
+      push @group_by, $chunk_ci->{-fq_colname};
+      $group_index{$chunk_ci->{-fq_colname}}++
+    }
+    else {
+      # We need to order by external columns without adding them to the group
+      # (eiehter a non-unique selection, or a multi-external)
+      #
+      # This doesn't really make sense in SQL, however from DBICs point
+      # of view is rather valid (e.g. order the leftmost objects by whatever
+      # criteria and get the offset/rows many). There is a way around
+      # this however in SQL - we simply tae the direction of each piece
+      # of the external order and convert them to MIN(X) for ASC or MAX(X)
+      # for DESC, and group_by the root columns. The end result should be
+      # exactly what we expect
+
+      # FIXME - this code is a joke, will need to be completely rewritten in
+      # the DQ branch. But I need to push a POC here, otherwise the
+      # pesky tests won't pass
+      # wrap any part of the order_by that "responds" to an ordering alias
+      # into a MIN/MAX
+
+      $order_chunks ||= do {
+        my @c;
+        my $dq_node = $sql_maker->converter->_order_by_to_dq($attrs->{order_by});
+
+        while (is_Order($dq_node)) {
+          push @c, {
+            is_desc => $dq_node->{reverse},
+            dq_node => $dq_node->{by},
+          };
+
+          @{$c[-1]}{qw(sql bind)} = $sql_maker->_render_dq($dq_node->{by});
+
+          $dq_node = $dq_node->{from};
+        }
+
+        \@c;
+      };
+
+      $new_order_by[$o_idx] = {
+        ($order_chunks->[$o_idx]{is_desc} ? '-desc' : '-asc') => \[
+          sprintf ( '%s( %s )',
+            ($order_chunks->[$o_idx]{is_desc} ? 'MAX' : 'MIN'),
+            $order_chunks->[$o_idx]{sql},
+          ),
+          @{ $order_chunks->[$o_idx]{bind} || [] }
+        ]
+      };
+    }
   }
 
-  return wantarray
-    ? (\@group_by, (@leftovers ? \@leftovers : undef) )
-    : \@group_by
-  ;
+  $self->throw_exception ( sprintf
+    'A required group_by clause could not be constructed automatically due to a complex '
+  . 'order_by criteria (%s). Either order_by columns only (no functions) or construct a suitable '
+  . 'group_by by hand',
+    join ', ', map { "'$_'" } @$leftovers,
+  ) if $leftovers;
+
+  # recreate the untouched order parts
+  if (@new_order_by) {
+    $new_order_by[$_] ||= {
+      ( $order_chunks->[$_]{is_desc} ? '-desc' : '-asc' )
+        => \ $order_chunks->[$_]{dq_node}
+    } for ( 0 .. $#$order_chunks );
+  }
+
+  return (
+    \@group_by,
+    (@new_order_by ? \@new_order_by : $attrs->{order_by} ),  # same ref as original == unchanged
+  );
 }
 
 sub _resolve_ident_sources {
   my ($self, $ident) = @_;
 
   my $alias2source = {};
-  my $rs_alias;
 
   # the reason this is so contrived is that $ident may be a {from}
   # structure, specifying multiple tables to join
   if ( blessed $ident && $ident->isa("DBIx::Class::ResultSource") ) {
     # this is compat mode for insert/update/delete which do not deal with aliases
     $alias2source->{me} = $ident;
-    $rs_alias = 'me';
   }
   elsif (ref $ident eq 'ARRAY') {
 
@@ -441,7 +720,6 @@ sub _resolve_ident_sources {
       my $tabinfo;
       if (ref $_ eq 'HASH') {
         $tabinfo = $_;
-        $rs_alias = $tabinfo->{-alias};
       }
       if (ref $_ eq 'ARRAY' and ref $_->[0] eq 'HASH') {
         $tabinfo = $_->[0];
@@ -452,7 +730,7 @@ sub _resolve_ident_sources {
     }
   }
 
-  return ($alias2source, $rs_alias);
+  return $alias2source;
 }
 
 # Takes $ident, \@column_names
@@ -464,7 +742,7 @@ sub _resolve_ident_sources {
 # for all sources
 sub _resolve_column_info {
   my ($self, $ident, $colnames) = @_;
-  my ($alias2src, $root_alias) = $self->_resolve_ident_sources($ident);
+  my $alias2src = $self->_resolve_ident_sources($ident);
 
   my (%seen_cols, @auto_colnames);
 
@@ -586,102 +864,56 @@ sub _inner_join_to_node {
   return \@new_from;
 }
 
-# yet another atrocity: attempt to extract all columns from a
-# where condition by hooking _quote
-sub _extract_condition_columns {
-  my ($self, $cond, $sql_maker) = @_;
-
-  return [] unless $cond;
-
-  $sql_maker ||= $self->{_sql_ident_capturer} ||= do {
-    # FIXME - replace with a Moo trait
-    my $orig_sm_class = ref $self->sql_maker;
-    my $smic_class = "${orig_sm_class}::_IdentCapture_";
-
-    unless ($smic_class->isa('SQL::Abstract')) {
-
-      no strict 'refs';
-      *{"${smic_class}::_quote"} = subname "${smic_class}::_quote" => sub {
-        my ($self, $ident) = @_;
-        if (ref $ident eq 'SCALAR') {
-          $ident = $$ident;
-          my $storage_quotes = $self->sql_quote_char || '"';
-          my ($ql, $qr) = map
-            { quotemeta $_ }
-            (ref $storage_quotes eq 'ARRAY' ? @$storage_quotes : ($storage_quotes) x 2 )
-          ;
-
-          while ($ident =~ /
-            $ql (\w+) $qr
-              |
-            ([\w\.]+)
-          /xg) {
-            $self->{_captured_idents}{$1||$2}++;
-          }
-        }
-        else {
-          $self->{_captured_idents}{$ident}++;
-        }
-        return $ident;
-      };
-
-      *{"${smic_class}::_get_captured_idents"} = subname "${smic_class}::_get_captures" => sub {
-        (delete shift->{_captured_idents}) || {};
-      };
-
-      $self->inject_base ($smic_class, $orig_sm_class);
-
-    }
-
-    $smic_class->new();
-  };
-
-  $sql_maker->_recurse_where($cond);
-
-  return [ sort keys %{$sql_maker->_get_captured_idents} ];
-}
-
 sub _extract_order_criteria {
-  my ($self, $order_by, $sql_maker) = @_;
+  my ($self, $order_by, $sql_maker, $ident_only) = @_;
 
-  my $parser = sub {
-    my ($sql_maker, $order_by) = @_;
+  $sql_maker ||= $self->sql_maker;
 
-    return scalar $sql_maker->_order_by_chunks ($order_by)
-      unless wantarray;
+  my $order_dq = $sql_maker->converter->_order_by_to_dq($order_by);
 
-    my @chunks;
-    for ($sql_maker->_order_by_chunks ($order_by) ) {
-      my $chunk = ref $_ ? $_ : [ $_ ];
-      $chunk->[0] =~ s/\s+ (?: ASC|DESC ) \s* $//ix;
-      push @chunks, $chunk;
-    }
+  my @by;
+  while (is_Order($order_dq)) {
+    push @by, $order_dq->{by};
+    $order_dq = $order_dq->{from};
+  }
 
-    return @chunks;
+  # delete local is 5.12+
+  local @{$sql_maker}{qw(quote_char renderer converter)};
+  delete @{$sql_maker}{qw(quote_char renderer converter)};
+
+  return map { [ $sql_maker->_render_dq($_) ] } do {
+    if ($ident_only) {
+      my @by_ident;
+      scan_dq_nodes({ DQ_IDENTIFIER ,=> sub { push @by_ident, $_[0] } }, @by);
+      @by_ident
+    } else {
+      @by
+    }
   };
-
-  if ($sql_maker) {
-    return $parser->($sql_maker, $order_by);
-  }
-  else {
-    $sql_maker = $self->sql_maker;
-    local $sql_maker->{quote_char};
-    return $parser->($sql_maker, $order_by);
-  }
 }
 
 sub _order_by_is_stable {
   my ($self, $ident, $order_by, $where) = @_;
 
-  my $colinfo = $self->_resolve_column_info($ident, [
-    (map { $_->[0] } $self->_extract_order_criteria($order_by)),
+  my @cols = (
+    (map { $_->[0] } $self->_extract_order_criteria($order_by, undef, 1)),
     $where ? @{$self->_extract_fixed_condition_columns($where)} :(),
-  ]);
+  ) or return undef;
 
-  return undef unless keys %$colinfo;
+  my $colinfo = $self->_resolve_column_info($ident, \@cols);
+
+  return keys %$colinfo
+    ? $self->_columns_comprise_identifying_set( $colinfo,  \@cols )
+    : undef
+  ;
+}
+
+sub _columns_comprise_identifying_set {
+  my ($self, $colinfo, $columns) = @_;
 
   my $cols_per_src;
-  $cols_per_src->{$_->{-source_alias}}{$_->{-colname}} = $_ for values %$colinfo;
+  $cols_per_src -> {$_->{-source_alias}} -> {$_->{-colname}} = $_
+    for grep { defined $_ } @{$colinfo}{@$columns};
 
   for (values %$cols_per_src) {
     my $src = (values %$_)[0]->{-result_source};
@@ -691,7 +923,78 @@ sub _order_by_is_stable {
   return undef;
 }
 
-# returns an arrayref of column names which *definitely* have som
+# this is almost identical to the above, except it accepts only
+# a single rsrc, and will succeed only if the first portion of the order
+# by is stable.
+# returns that portion as a colinfo hashref on success
+sub _main_source_order_by_portion_is_stable {
+  my ($self, $main_rsrc, $order_by, $where) = @_;
+
+  die "Huh... I expect a blessed result_source..."
+    if ref($main_rsrc) eq 'ARRAY';
+
+  my @ord_cols = map
+    { $_->[0] }
+    ( $self->_extract_order_criteria($order_by) )
+  ;
+  return unless @ord_cols;
+
+  my $colinfos = $self->_resolve_column_info($main_rsrc);
+
+  for (0 .. $#ord_cols) {
+    if (
+      ! $colinfos->{$ord_cols[$_]}
+        or
+      $colinfos->{$ord_cols[$_]}{-result_source} != $main_rsrc
+    ) {
+      $#ord_cols =  $_ - 1;
+      last;
+    }
+  }
+
+  # we just truncated it above
+  return unless @ord_cols;
+
+  my $order_portion_ci = { map {
+    $colinfos->{$_}{-colname} => $colinfos->{$_},
+    $colinfos->{$_}{-fq_colname} => $colinfos->{$_},
+  } @ord_cols };
+
+  # since all we check here are the start of the order_by belonging to the
+  # top level $rsrc, a present identifying set will mean that the resultset
+  # is ordered by its leftmost table in a stable manner
+  #
+  # RV of _identifying_column_set contains unqualified names only
+  my $unqualified_idset = $main_rsrc->_identifying_column_set({
+    ( $where ? %{
+      $self->_resolve_column_info(
+        $main_rsrc, $self->_extract_fixed_condition_columns($where)
+      )
+    } : () ),
+    %$order_portion_ci
+  }) or return;
+
+  my $ret_info;
+  my %unqualified_idcols_from_order = map {
+    $order_portion_ci->{$_} ? ( $_ => $order_portion_ci->{$_} ) : ()
+  } @$unqualified_idset;
+
+  # extra optimization - cut the order_by at the end of the identifying set
+  # (just in case the user was stupid and overlooked the obvious)
+  for my $i (0 .. $#ord_cols) {
+    my $col = $ord_cols[$i];
+    my $unqualified_colname = $order_portion_ci->{$col}{-colname};
+    $ret_info->{$col} = { %{$order_portion_ci->{$col}}, -idx_in_order_subset => $i };
+    delete $unqualified_idcols_from_order{$ret_info->{$col}{-colname}};
+
+    # we didn't reach the end of the identifying portion yet
+    return $ret_info unless keys %unqualified_idcols_from_order;
+  }
+
+  die 'How did we get here...';
+}
+
+# returns an arrayref of column names which *definitely* have some
 # sort of non-nullable equality requested in the given condition
 # specification. This is used to figure out if a resultset is
 # constrained to a column which is part of a unique constraint,
@@ -703,7 +1006,13 @@ sub _order_by_is_stable {
 # something that is in fact there - the stack will recover gracefully
 # Also - DQ and the mst it rode in on will save us all RSN!!!
 sub _extract_fixed_condition_columns {
-  my ($self, $where, $nested) = @_;
+  my ($self, $where) = @_;
+
+  if (ref($where) eq 'REF' and ref($$where) eq 'HASH') {
+    # Yes. I know.
+    my $fixed = DBIx::Class::ResultSource->_extract_fixed_values_for($$where);
+    return [ keys %$fixed ];
+  }
 
   return unless ref $where eq 'HASH';
 
@@ -711,8 +1020,8 @@ sub _extract_fixed_condition_columns {
   for my $lhs (keys %$where) {
     if ($lhs =~ /^\-and$/i) {
       push @cols, ref $where->{$lhs} eq 'ARRAY'
-        ? ( map { $self->_extract_fixed_condition_columns($_, 1) } @{$where->{$lhs}} )
-        : $self->_extract_fixed_condition_columns($where->{$lhs}, 1)
+        ? ( map { @{ $self->_extract_fixed_condition_columns($_) } } @{$where->{$lhs}} )
+        : @{ $self->_extract_fixed_condition_columns($where->{$lhs}) }
       ;
     }
     elsif ($lhs !~ /^\-/) {
@@ -725,7 +1034,7 @@ sub _extract_fixed_condition_columns {
       ));
     }
   }
-  return $nested ? @cols : \@cols;
+  return \@cols;
 }
 
 1;