initial merge of Schwern's CDBICompat work, with many thanks

[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 4dec7ae..70e645c 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
@@ -213,27 +263,6 @@ Then call your new method in your code:
 
    my $ordered_cds = $schema->resultset('CD')->search_cds_ordered();
 
-
-=head3 Predefined searches without writing a ResultSet class
-
-Alternatively you can automatically generate a DBIx::Class::ResultSet
-class by using the ResultSetManager component and tagging your method
-as ResultSet:
-
-  __PACKAGE__->load_components(qw/ ResultSetManager Core /);
-
-  sub search_cds_ordered : ResultSet {
-      my ($self) = @_;
-      return $self->search(
-          {},
-          { order_by => 'name DESC' },
-      );
-  } 
-
-Then call your method in the same way from your code:
-
-   my $ordered_cds = $schema->resultset('CD')->search_cds_ordered();
-
 =head2 Using joins and prefetch
 
 You can use the C<join> attribute to allow searching on, or sorting your
@@ -571,7 +600,7 @@ It's as simple as overriding the C<new> method.  Note the use of
 C<next::method>.
 
   sub new {
-    my ( $class, $attrs ) = @_;
+    my ( $self, $attrs ) = @_;
 
     $attrs->{foo} = 'bar' unless defined $attrs->{foo};
 
@@ -585,7 +614,7 @@ documentation. See also L<DBIx::Class::Manual::Component> for more
 ways to write your own base classes to do this.
 
 People looking for ways to do "triggers" with DBIx::Class are probably
-just looking for this.
+just looking for this. 
 
 =head2 Stringification
 
@@ -818,9 +847,10 @@ generator to put the quotes the correct place.
 
 =head2 Overloading methods
 
-L<DBIx::Class> uses the L<Class::C3> package, which provides for redispatch of 
-method calls.  You have to use calls to C<next::method> to overload methods.  
-More information on using L<Class::C3> with L<DBIx::Class> can be found in 
+L<DBIx::Class> uses the L<Class::C3> package, which provides for redispatch of
+method calls, useful for things like default values and triggers. You have to
+use calls to C<next::method> to overload methods. More information on using
+L<Class::C3> with L<DBIx::Class> can be found in
 L<DBIx::Class::Manual::Component>.
 
 =head3 Changing one field whenever another changes