From: Peter Rabbitson <ribasushi@cpan.org>
Date: Mon, 17 Aug 2009 09:41:08 +0000 (+0000)
Subject: Cookbook entry for -as and syntax tests
X-Git-Tag: v0.08109~7
X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits%2FDBIx-Class.git;a=commitdiff_plain;h=324bc21498b1df5a13c87bbd5209b85985d1743f

Cookbook entry for -as and syntax tests
---

diff --git a/lib/DBIx/Class/Manual/Cookbook.pod b/lib/DBIx/Class/Manual/Cookbook.pod
index 03e6e49..7561248 100644
--- a/lib/DBIx/Class/Manual/Cookbook.pod
+++ b/lib/DBIx/Class/Manual/Cookbook.pod
@@ -199,12 +199,33 @@ to access the returned value:
   # SELECT name name, LENGTH( name )
   # FROM artist
 
-Note that the C< as > attribute has absolutely nothing to with the sql
-syntax C< SELECT foo AS bar > (see the documentation in
-L<DBIx::Class::ResultSet/ATTRIBUTES>).  If your alias exists as a
-column in your base class (i.e. it was added with C<add_columns>), you
-just access it as normal. Our C<Artist> class has a C<name> column, so
-we just use the C<name> accessor:
+Note that the C<as> attribute B<has absolutely nothing to do> with the sql
+syntax C< SELECT foo AS bar > (see the documentation in 
+L<DBIx::Class::ResultSet/ATTRIBUTES>). You can control the C<AS> part of the
+generated SQL via the C<-as> field attribute as follows:
+
+  my $rs = $schema->resultset('Artist')->search(
+    {},
+    {
+      join => 'cds',
+      distinct => 1,
+      +select => [ { count => 'cds.cdid', -as => 'amount_of_cds' } ],
+      +as => [qw/num_cds/],
+      order_by => { -desc => 'amount_of_cds' },
+    }
+  );
+
+  # Equivalent SQL
+  # SELECT me.artistid, me.name, me.rank, me.charfield, COUNT( cds.cdid ) AS amount_of_cds 
+  #   FROM artist me LEFT JOIN cd cds ON cds.artist = me.artistid 
+  # GROUP BY me.artistid, me.name, me.rank, me.charfield 
+  # ORDER BY amount_of_cds DESC 
+
+
+If your alias exists as a column in your base class (i.e. it was added with
+L<add_columns|DBIx::Class::ResultSource/add_columns>), you just access it as
+normal. Our C<Artist> class has a C<name> column, so we just use the C<name>
+accessor:
 
   my $artist = $rs->first();
   my $name = $artist->name();
diff --git a/lib/DBIx/Class/SQLAHacks.pm b/lib/DBIx/Class/SQLAHacks.pm
index edd73fb..d5041ba 100644
--- a/lib/DBIx/Class/SQLAHacks.pm
+++ b/lib/DBIx/Class/SQLAHacks.pm
@@ -423,7 +423,7 @@ sub _recurse_fields {
       $self->_sqlcase($func),
       $self->_recurse_fields($args),
       $as
-        ? join (' ', $self->_sqlcase('as'), $as)
+        ? sprintf (' %s %s', $self->_sqlcase('as'), $as)
         : ''
     );
 
diff --git a/t/count/distinct.t b/t/count/distinct.t
index 6df9ed0..b93f9ad 100644
--- a/t/count/distinct.t
+++ b/t/count/distinct.t
@@ -11,8 +11,6 @@ use DBIC::SqlMakerTest;
 
 my $schema = DBICTest->init_schema();
 
-plan tests => 56;
-
 # The tag Blue is assigned to cds 1 2 3 and 5
 # The tag Cheesy is assigned to cds 2 4 and 5
 #
@@ -86,5 +84,34 @@ throws_ok(
   'throw on unsupported syntax'
 );
 
+# make sure distinct+func works
+{
+  my $rs = $schema->resultset('Artist')->search(
+    {},
+    {
+      join => 'cds',
+      distinct => 1,
+      '+select' => [ { count => 'cds.cdid', -as => 'amount_of_cds' } ],
+      '+as' => [qw/num_cds/],
+      order_by => { -desc => 'amount_of_cds' },
+    }
+  );
+
+  is_same_sql_bind (
+    $rs->as_query,
+    '(
+      SELECT me.artistid, me.name, me.rank, me.charfield, COUNT( cds.cdid ) AS amount_of_cds
+        FROM artist me LEFT JOIN cd cds ON cds.artist = me.artistid
+      GROUP BY me.artistid, me.name, me.rank, me.charfield
+      ORDER BY amount_of_cds DESC
+    )',
+    [],
+  );
+
+  is ($rs->next->get_column ('num_cds'), 3, 'Function aliased correctly');
+}
+
 # These two rely on the database to throw an exception. This might not be the case one day. Please revise.
 dies_ok(sub { my $count = $schema->resultset('Tag')->search({}, { '+select' => \'tagid AS tag_id', distinct => 1 })->count }, 'expecting to die');
+
+done_testing;