KISS - a select(1) makes perfect ordering criteria

[dbsrgits/DBIx-Class.git] / lib / DBIx / Class / SQLAHacks / MSSQL.pm

diff --git a/lib/DBIx/Class/SQLAHacks/MSSQL.pm b/lib/DBIx/Class/SQLAHacks/MSSQL.pm
index 6110c8d..ee2e4cf 100644 (file)
--- a/lib/DBIx/Class/SQLAHacks/MSSQL.pm
+++ b/lib/DBIx/Class/SQLAHacks/MSSQL.pm
@@ -1,16 +1,36 @@
 package # Hide from PAUSE
   DBIx::Class::SQLAHacks::MSSQL;
 
+use warnings;
+use strict;
+
 use base qw( DBIx::Class::SQLAHacks );
 use Carp::Clan qw/^DBIx::Class|^SQL::Abstract/;
 
-sub _RowNumberOver {
-   my $self = shift;
-   my $sql  =  $self->SUPER::_RowNumberOver(@_);
-   $sql =~ s/(\s*)SELECT\s Q1\.\*,\s ROW_NUMBER\(\)\s OVER\(\s \)\s AS\s ROW_NUM\s
-             FROM\s \(\n(\s*.*)\n\s*(.*)\n\s*\)\s Q1
-             /$1SELECT Q1.*, ROW_NUMBER() OVER($3) AS ROW_NUM FROM (\n$2\n) Q1/ixm;
-   return $sql;
+# an MSSQL-specific implementation of the Row-Number-Over limiting
+# technique
+
+sub _MSRowNumberOver {
+  my ($self, $sql, $order, $rows, $offset ) = @_;
+
+  # get the order_by only (or make up an order if none exists)
+  my $order_by = $self->_order_by(
+    (delete $order->{order_by}) || \ '(SELECT (1))'
+  );
+
+  # whatever is left
+  my $group_having = $self->_order_by($order);
+
+  $sql = sprintf (<<'EOS', $order_by, $sql, $group_having, $offset + 1, $offset + $rows, );
+
+SELECT * FROM (
+  SELECT orig_query.*, ROW_NUMBER() OVER(%s ) AS rno__row__index FROM (%s%s) orig_query
+) rno_subq WHERE rno__row__index BETWEEN %d AND %d
+
+EOS
+
+  $sql =~ s/\s*\n\s*/ /g;   # easier to read in the debugger
+  return $sql;
 }
 
 1;