Fixed group_by bind position problem, reported and patch by Alexande Keusch. Patch...
Robert Bohne [Thu, 27 May 2010 08:17:35 +0000 (08:17 +0000)]
lib/DBIx/Class/SQLAHacks.pm
lib/DBIx/Class/SQLAHacks/Oracle.pm
t/73oracle.t

index 40bc7a3..674b90c 100644 (file)
@@ -399,7 +399,7 @@ sub _find_syntax {
 sub select {
   my ($self, $table, $fields, $where, $rs_attrs, @rest) = @_;
 
-  $self->{"${_}_bind"} = [] for (qw/having from order/);
+  $self->{"${_}_bind"} = [] for (qw/having from order where/);
 
   if (not ref($table) or ref($table) eq 'SCALAR') {
     $table = $self->_quote($table);
@@ -409,10 +409,11 @@ sub select {
   croak "LIMIT 0 Does Not Compute" if $rest[0] == 0;
     # and anyway, SQL::Abstract::Limit will cause a barf if we don't first
 
-  my ($sql, @where_bind) = $self->SUPER::select(
+  my $sql = '';
+  ($sql, @{$self->{where_bind}}) = $self->SUPER::select(
     $table, $self->_recurse_fields($fields), $where, $rs_attrs, @rest
   );
-  return wantarray ? ($sql, @{$self->{from_bind}}, @where_bind, @{$self->{having_bind}}, @{$self->{order_bind}} ) : $sql;
+  return wantarray ? ($sql, @{$self->{from_bind}}, @{$self->{where_bind}}, @{$self->{having_bind}}, @{$self->{order_bind}} ) : $sql;
 }
 
 # Quotes table names, and handles default inserts
index b91eb67..5c407b6 100644 (file)
@@ -27,10 +27,9 @@ sub new {
 sub select {
     my ($self, $table, $fields, $where, $rs_attrs, @rest) = @_;
 
-    my ($sql, @bind) = $self->SUPER::select($table, $fields, $where, $rs_attrs, @rest);
-    push @bind, @{$self->{_oracle_connect_by_binds}};
+    my $sql = $self->SUPER::select($table, $fields, $where, $rs_attrs, @rest);
 
-    return wantarray ? ($sql, @bind) : $sql;
+    return wantarray ? ($sql, @{$self->{from_bind}}, @{$self->{where_bind}}, @{$self->{_oracle_connect_by_binds}}, @{$self->{having_bind}}, @{$self->{order_bind}} ) : $sql;
 }
 
 sub _emulate_limit {
index 2a89880..dbbe78e 100644 (file)
@@ -343,13 +343,16 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) {
 
     $schema->resultset('Artist')->create ({
         name => 'root',
+        rank => 1,
         cds => [],
         children => [
             {
                 name => 'child1',
+                rank => 2,
                 children => [
                     {
                         name => 'grandchild',
+                        rank => 3,
                         cds => [
                             {
                                 title => "grandchilds's cd" ,
@@ -365,6 +368,7 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) {
                         children => [
                             {
                                 name => 'greatgrandchild',
+                                rank => 3,
                             }
                         ],
                     }
@@ -372,6 +376,7 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) {
             },
             {
                 name => 'child2',
+                rank => 3,
             },
         ],
     });
@@ -623,6 +628,36 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) {
       is( $rs->count, 2, 'Connect By; LIMIT count ok' );
     }
 
+    # combine a connect_by with group_by and having
+    {
+      my $rs = $schema->resultset('Artist')->search({}, {
+        select => ['count(rank)'],
+        start_with => { name => 'root' },
+        connect_by => { parentid => { -prior => \ 'artistid' } },
+        group_by => ['rank'],
+        having => { 'count(rank)' => { '<', 2 } },
+      });
+
+      is_same_sql_bind (
+        $rs->as_query,
+        '(
+            SELECT count(rank)
+            FROM artist me
+            START WITH name = ?
+            CONNECT BY parentid = PRIOR artistid
+            GROUP BY rank HAVING count(rank) < ?
+        )',
+        [ [ name => 'root' ], [ 'count(rank)' => 2 ] ],
+      );
+
+      is_deeply (
+        [ $rs->get_column ('count(rank)')->all ],
+        [1, 1],
+        'Group By a Connect By query - correct values'
+      );
+    }
+
+
     # select the whole cycle tree without nocylce
     {
       my $rs = $schema->resultset('Artist')->search({}, {