Copy the working mk_hash from HashRefInflator in -current into Cookbook

[dbsrgits/DBIx-Class.git] / lib / DBIx / Class / Manual / Cookbook.pod

diff --git a/lib/DBIx/Class/Manual/Cookbook.pod b/lib/DBIx/Class/Manual/Cookbook.pod
index 52bb889..8f098af 100644 (file)
--- a/lib/DBIx/Class/Manual/Cookbook.pod
+++ b/lib/DBIx/Class/Manual/Cookbook.pod
@@ -70,6 +70,56 @@ This results in the following C<WHERE> clause:
 For more information on generating complex queries, see
 L<SQL::Abstract/WHERE CLAUSES>.
 
+=head3 Arbitrary SQL through a custom ResultSource
+
+Sometimes you have to run arbitrary SQL because your query is too complex
+(e.g. it contains Unions, Sub-Selects, Stored Procedures, etc.) or has to
+be optimized for your database in a special way, but you still want to 
+get the results as a L<DBIx::Class::ResultSet>. 
+The recommended way to accomplish this is by defining a separate ResultSource 
+for your query. You can then inject complete SQL statements using a scalar 
+reference (this is a feature of L<SQL::Abstract>).
+
+Say you want to run a complex custom query on your user data, here's what
+you have to add to your User class:
+
+  package My::Schema::User;
+  
+  use base qw/DBIx::Class/;
+  
+  # ->load_components, ->table, ->add_columns, etc.
+
+  # Make a new ResultSource based on the User class
+  my $source = __PACKAGE__->result_source_instance();
+  my $new_source = $source->new( $source );
+  $new_source->source_name( 'UserFriendsComplex' );
+  
+  # Hand in your query as a scalar reference
+  # It will be added as a sub-select after FROM,
+  # so pay attention to the surrounding brackets!
+  $new_source->name( \<<SQL );
+  ( SELECT u.* FROM user u 
+  INNER JOIN user_friends f ON u.id = f.user_id 
+  WHERE f.friend_user_id = ?
+  UNION 
+  SELECT u.* FROM user u 
+  INNER JOIN user_friends f ON u.id = f.friend_user_id 
+  WHERE f.user_id = ? )
+  SQL 
+
+  # Finally, register your new ResultSource with your Schema
+  My::Schema->register_source( 'UserFriendsComplex' => $new_source );
+
+Next, you can execute your complex query using bind parameters like this:
+
+  my $friends = [ $schema->resultset( 'UserFriendsComplex' )->search( {}, 
+    {
+      bind  => [ 12345, 12345 ]
+    }
+  ) ];
+  
+... and you'll get back a perfect L<DBIx::Class::ResultSet>.
+
 =head3 Using specific columns
 
 When you only want specific columns from a table, you can use
@@ -571,13 +621,13 @@ It's as simple as overriding the C<new> method.  Note the use of
 C<next::method>.
 
   sub new {
-    my ( $self, $attrs ) = @_;
+    my ( $class, $attrs ) = @_;
 
     $attrs->{foo} = 'bar' unless defined $attrs->{foo};
 
-    $self->next::method($attrs);
+    my $new = $class->next::method($attrs);
 
-    return $self;
+    return $new;
   }
 
 For more information about C<next::method>, look in the L<Class::C3> 
@@ -1122,8 +1172,26 @@ C<inflate_result>:
   sub mk_hash {
      my ($me, $rest) = @_;
 
-     return { %$me, 
-        map { ($_ => mk_hash(@{$rest->{$_}})) } keys %$rest
+     # to avoid emtpy has_many rels contain one empty hashref
+     return if (not keys %$me);
+
+     my $def;
+
+     foreach (values %$me) {
+         if (defined $_) {
+             $def = 1;
+             last;
+         }
+     }
+     return unless $def;
+
+     return { %$me,
+         map {
+           ( $_ =>
+              ref($rest->{$_}[0]) eq 'ARRAY' ? [ map { mk_hash(@$_) } @{$rest->{$_}} ]
+                                             : mk_hash( @{$rest->{$_}} )
+           )
+         } keys %$rest
      };
   }
 
@@ -1145,6 +1213,22 @@ C<inflate_result>:
      }
   }
 
+=head2 Get raw data for blindingly fast results
+
+If the C<inflate_result> solution above is not fast enough for you, you
+can use a DBIx::Class to return values exactly as they come out of the
+data base with none of the convenience methods wrapped round them.
+
+This is used like so:-
+
+  my $cursor = $rs->cursor
+  while (my @vals = $cursor->next) {
+      # use $val[0..n] here
+  }
+
+You will need to map the array offsets to particular columns (you can
+use the I<select> attribute of C<search()> to force ordering).
+
 =head2 Want to know if find_or_create found or created a row?
 
 Just use C<find_or_new> instead, then check C<in_storage>: