X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FDBIx%2FClass%2FManual%2FCookbook.pod;h=b86ab7a334b4e535379d7cb5743a39f4c5933ec0;hb=d00a5c68a046c661c3fe9991b8b6d4927c306ec6;hp=6717ea77f74f5453ae2f3f8e47bcdd2a6b9db12d;hpb=a5b293612996cda25ce7e7bf1a5a5a23249c7b01;p=dbsrgits%2FDBIx-Class.git

diff --git a/lib/DBIx/Class/Manual/Cookbook.pod b/lib/DBIx/Class/Manual/Cookbook.pod
index 6717ea7..b86ab7a 100644
--- a/lib/DBIx/Class/Manual/Cookbook.pod
+++ b/lib/DBIx/Class/Manual/Cookbook.pod
@@ -68,6 +68,41 @@ This results in the following C<WHERE> clause:
 For more information on generating complex queries, see
 L<SQL::Abstract/WHERE CLAUSES>.
 
+=head2 Retrieve one and only one row from a resultset
+
+Sometimes you need only the first "top" row of a resultset. While this can be
+easily done with L<< $rs->first|DBIx::Class::ResultSet/first >>, it is suboptimal,
+as a full blown cursor for the resultset will be created and then immediately
+destroyed after fetching the first row object. 
+L<< $rs->single|DBIx::Class::ResultSet/single >> is
+designed specifically for this case - it will grab the first returned result
+without even instantiating a cursor. 
+
+Before replacing all your calls to C<first()> with C<single()> please observe the 
+following CAVEATS:
+
+=over
+
+=item *
+While single() takes a search condition just like search() does, it does
+_not_ accept search attributes. However one can always chain a single() to
+a search():
+
+  my $top_cd = $cd_rs -> search({}, { order_by => 'rating' }) -> single;
+
+
+=item *
+Since single() is the engine behind find(), it is designed to fetch a
+single row per database query. Thus a warning will be issued when the
+underlying SELECT returns more than one row. Sometimes however this usage
+is valid: i.e. we have an arbitrary number of cd's but only one of them is
+at the top of the charts at any given time. If you know what you are doing,
+you can silence the warning by explicitly limiting the resultset size:
+
+  my $top_cd = $cd_rs -> search ({}, { order_by => 'rating', rows => 1 }) -> single;
+
+=back
+
 =head2 Arbitrary SQL through a custom ResultSource
 
 Sometimes you have to run arbitrary SQL because your query is too complex
@@ -106,7 +141,7 @@ you have to add to your User class:
   SQL 
 
   # Finally, register your new ResultSource with your Schema
-  My::Schema->register_source( 'UserFriendsComplex' => $new_source );
+  My::Schema->register_extra_source( 'UserFriendsComplex' => $new_source );
 
 Next, you can execute your complex query using bind parameters like this:
 
@@ -116,7 +151,36 @@ Next, you can execute your complex query using bind parameters like this:
     }
   ) ];
   
-... and you'll get back a perfect L<DBIx::Class::ResultSet>.
+... and you'll get back a perfect L<DBIx::Class::ResultSet> (except, of course,
+that you cannot modify the rows it contains, ie. cannot call L</update>,
+L</delete>, ...  on it).
+
+If you prefer to have the definitions of these custom ResultSources in separate
+files (instead of stuffing all of them into the same resultset class), you can
+achieve the same with subclassing the resultset class and defining the
+ResultSource there:
+
+  package My::Schema::UserFriendsComplex;
+
+  use My::Schema::User;
+  use base qw/My::Schema::User/;
+
+  __PACKAGE__->table('dummy');  # currently must be called before anything else
+
+  # 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!
+  __PACKAGE__->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
+
+TIMTOWDI.
 
 =head2 Using specific columns
 
@@ -749,17 +813,6 @@ To do this simply use L<DBIx::Class::ResultClass::HashRefInflator>.
 
 Wasn't that easy?
   
-=head2 Skip row object creation for faster results, but still inflate
-column values to the corresponding objects
-
- my $rs = $schema->resultset('CD');
- 
- $rs->result_class(DBIx::Class::ResultClass::HashRefInflator->new (
-    inflate_columns => 1
- ));
- 
- my $hash_ref = $rs->find(1);
-  
 =head2 Get raw data for blindingly fast results
 
 If the L<HashRefInflator|DBIx::Class::ResultClass::HashRefInflator> solution
@@ -881,6 +934,12 @@ as follows:
 
   __PACKAGE__->has_many('pages' => 'Page', 'book', { order_by => \'page_number DESC'} );
 
+=head2 Filtering a relationship result set
+
+If you want to get a filtered result set, you can just add add to $attr as follows:
+
+ __PACKAGE__->has_many('pages' => 'Page', 'book', { where => { scrap => 0 } } );
+
 =head2 Many-to-many relationships
 
 This is straightforward using L<ManyToMany|DBIx::Class::Relationship/many_to_many>:
@@ -1238,7 +1297,7 @@ characters to use. C<name_sep> needs to be set to allow the SQL
 generator to put the quotes the correct place.
 
 In most cases you should set these as part of the arguments passed to 
-L<DBIx::Class::Schema/conect>:
+L<DBIx::Class::Schema/connect>:
 
  my $schema = My::Schema->connect(
   'dbi:mysql:my_db',
@@ -1268,6 +1327,40 @@ that Microsoft doesn't deliver native client libraries for. (e.g. Linux)
 The limit dialect can also be set at connect time by specifying a 
 C<limit_dialect> key in the final hash as shown above.
 
+=head2 Working with PostgreSQL array types
+
+If your SQL::Abstract version (>= 1.50) supports it, you can assign to
+PostgreSQL array values by passing array references in the C<\%columns>
+(C<\%vals>) hashref of the L<DBIx::Class::ResultSet/create> and
+L<DBIx::Class::Row/update> family of methods:
+
+  $resultset->create({
+    numbers => [1, 2, 3]
+  });
+
+  $row->update(
+    {
+      numbers => [1, 2, 3]
+    },
+    {
+      day => '2008-11-24'
+    }
+  );
+
+In conditions (eg. C<\%cond> in the L<DBIx::Class::ResultSet/search> family of
+methods) you cannot directly use array references (since this is interpreted as
+a list of values to be C<OR>ed), but you can use the following syntax to force
+passing them as bind values:
+
+  $resultset->search(
+    {
+      numbers => \[ '= ?', [1, 2, 3] ]
+    }
+  );
+
+See L<SQL::Abstract/array_datatypes> and L<SQL::Abstract/Literal SQL with
+placeholders and bind values (subqueries)> for more explanation.
+
 =head1 BOOTSTRAPPING/MIGRATING 
 
 =head2 Easy migration from class-based to schema-based setup