Rewrote 'SQL functions on the lhs' to use the new SQLA literal SQL + bind feature.

[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 e7e14ea..5a9e75d 100644 (file)
--- a/lib/DBIx/Class/Manual/Cookbook.pod
+++ b/lib/DBIx/Class/Manual/Cookbook.pod
@@ -247,6 +247,8 @@ any of your aliases using either of these:
   # Or use DBIx::Class::AccessorGroup:
   __PACKAGE__->mk_group_accessors('column' => 'name_length');
 
+See also L</Using SQL functions on the left hand side of a comparison>.
+
 =head2 SELECT DISTINCT with multiple columns
 
   my $rs = $schema->resultset('Artist')->search(
@@ -334,7 +336,7 @@ B<NOTE>: You have to explicitly use '=' when doing an equality comparison.
 The following will B<not> work:
 
   my $rs = $schema->resultset('CD')->search({
-    artist_id => $inside_rs->get_column('id')->as_query,
+    artist_id => $inside_rs->get_column('id')->as_query,  # does NOT work
   });
 
 =head3 Support
@@ -407,8 +409,10 @@ Then call your new method in your code:
 
 =head2 Using SQL functions on the left hand side of a comparison
 
-Using SQL functions on the left hand side of a comparison is generally
-not a good idea since it requires a scan of the entire table.  However,
+Using SQL functions on the left hand side of a comparison is generally not a
+good idea since it requires a scan of the entire table. (Unless your RDBMS
+supports indexes on expressions - including return values of functions -, and
+you create an index on the return value of the function in question.) However,
 it can be accomplished with C<DBIx::Class> when necessary.
 
 If you do not have quoting on, simply include the function in your search
@@ -416,25 +420,24 @@ specification as you would any column:
 
   $rs->search({ 'YEAR(date_of_birth)' => 1979 });
 
-With quoting on, or for a more portable solution, use the C<where>
-attribute:
-
-  $rs->search({}, { where => \'YEAR(date_of_birth) = 1979' });
+With quoting on, or for a more portable solution, use literal SQL values with
+placeholders:
 
-=begin hidden
+  $rs->search(\[ 'YEAR(date_of_birth)', [ dummy => 1979 ] ]);
 
-(When the bind args ordering bug is fixed, this technique will be better
-and can replace the one above.)
-
-With quoting on, or for a more portable solution, use the C<where> and
-C<bind> attributes:
+  # Equivalent SQL:
+  # SELECT * FROM employee WHERE YEAR(date_of_birth) = ?
 
-  $rs->search({}, {
-      where => \'YEAR(date_of_birth) = ?',
-      bind  => [ 1979 ]
+  $rs->search({
+    name => 'Bob',
+    -nest => \[ 'YEAR(date_of_birth)', [ dummy => 1979 ] ],
   });
 
-=end hidden
+  # Equivalent SQL:
+  # SELECT * FROM employee WHERE name = ? AND YEAR(date_of_birth) = ?
+
+See also L<SQL::Abstract/Literal SQL with placeholders and bind values
+(subqueries)>.
 
 =head1 JOINS AND PREFETCHING
 
@@ -925,6 +928,9 @@ B<Test File> test.pl
     ### The statement below will print
     print "I can do admin stuff\n" if $admin->can('do_admin_stuff');
 
+Alternatively you can use L<DBIx::Class::DynamicSubclass> that implements
+exactly the above functionality.
+
 =head2 Skip row object creation for faster results
 
 DBIx::Class is not built for speed, it's built for convenience and
@@ -1065,7 +1071,7 @@ create the relationship.
 To order C<< $book->pages >> by descending page_number, create the relation
 as follows:
 
-  __PACKAGE__->has_many('pages' => 'Page', 'book', { order_by => \'page_number DESC'} );
+  __PACKAGE__->has_many('pages' => 'Page', 'book', { order_by => { -desc => 'page_number'} } );
 
 =head2 Filtering a relationship result set