add support for field aliases to select and select_p methods in Mojo:…

…:Pg::Database
mojolicious · Jan 28, 2018 · 71c592e · 71c592e
1 parent 842b386
commit 71c592e
Show file tree

Hide file tree

Showing 6 changed files with 65 additions and 6 deletions.
diff --git a/Changes b/Changes
@@ -2,6 +2,8 @@
 4.07  2018-01-28
   - Added support for "JOIN" to select and select_p methods in
     Mojo::Pg::Database.
+  - Added support for field aliases to select and select_p methods in
+    Mojo::Pg::Database.
   - Added support for having option to select and select_p methods in
     Mojo::Pg::Database.
   - Improved on_conflict option of insert and insert_p methods in

diff --git a/lib/Mojo/Pg/Database.pm b/lib/Mojo/Pg/Database.pm
@@ -502,6 +502,12 @@ L<SQL::Abstract>.
 
 As well as some PostgreSQL specific extensions added by L<SQL::Abstract::Pg>.
 
+  # "select foo as bar from some_table"
+  $db->select('some_table', [[foo => 'bar']]);
+
+  # "select extract(epoch from foo) as foo, bar from some_table"
+  $db->select('some_table', [\'extract(epoch from foo) as foo', 'bar']);
+
   # "select * from foo join bar on (bar.foo_id = foo.id)"
   $db->select(['foo', ['bar', foo_id => 'id']]);
 

diff --git a/lib/SQL/Abstract/Pg.pm b/lib/SQL/Abstract/Pg.pm
@@ -10,6 +10,28 @@ sub insert {
   return $self->SUPER::insert($table, $data, $options);
 }
 
+sub select {
+  my ($self, $table, $fields, @args) = @_;
+
+  if (ref $fields eq 'ARRAY') {
+    my @fields;
+    for my $field (@$fields) {
+      if (ref $field eq 'ARRAY') {
+        puke 'as value needs at least 2 elements' if @$field < 2;
+        push @fields,
+            $self->_quote($field->[0])
+          . $self->_sqlcase(' as ')
+          . $self->_quote($field->[1]);
+      }
+      elsif (ref $field eq 'SCALAR') { push @fields, $$field }
+      else                           { push @fields, $self->_quote($field) }
+    }
+    $fields = join ', ', @fields;
+  }
+
+  return $self->SUPER::select($table, $fields, @args);
+}
+
 sub _insert_returning {
   my ($self, $options) = @_;
 
@@ -130,7 +152,6 @@ sub _table {
   $table = $self->SUPER::_table(\@table);
   for my $join (@join) {
     puke 'join value needs at least 3 elements' if @$join < 3;
-    puke 'join values cannot be undefined' if grep { !defined $_ } @$join;
     my $type = @$join > 3 ? shift @$join : '';
     my ($name, $fk, $pk) = @$join;
     $table
@@ -248,6 +269,21 @@ pass literal SQL are supported.
   # "select * from some_table for update skip locked"
   $abstract->select('some_table', '*', undef, {for => \'update skip locked'});
 
+=head2 AS
+
+The C<$fields> argument now also accepts array references containing array
+references with field names and aliases, as well as array references containing
+scalar references with literal SQL.
+
+  # "select foo as bar from some_table"
+  $abstract->select('some_table', [[foo => 'bar']]);
+
+  # "select foo, bar as baz, yada from some_table"
+  $abstract->select('some_table', ['foo', [bar => 'baz'], 'yada']);
+
+  # "select extract(epoch from foo) as foo from some_table"
+  $abstract->select('some_table', [\'extract(epoch from foo) as foo']);
+
 =head2 JOIN
 
 The C<$source> argument now also accepts array references containing not only

diff --git a/t/crud.t b/t/crud.t
@@ -157,8 +157,10 @@ $db->query(
 );
 $db->insert('crud_test4', {test1 => 'hello'});
 $db->insert('crud_test5', {test2 => 'world'});
-is_deeply $db->select(['crud_test4', ['crud_test5', id => 'id']])
-  ->hashes->to_array, [{id => 1, test1 => 'hello', test2 => 'world'}],
+is_deeply $db->select(['crud_test4', ['crud_test5', id => 'id']],
+  ['crud_test4.id', 'test1', 'test2', ['crud_test4.test1' => 'test3']])
+  ->hashes->to_array,
+  [{id => 1, test1 => 'hello', test2 => 'world', test3 => 'hello'}],
   'right structure';
 
 # Clean up once we are done

diff --git a/t/pod_coverage.t b/t/pod_coverage.t
@@ -7,7 +7,8 @@ plan skip_all => 'set TEST_POD to enable this test (developer only!)'
 plan skip_all => 'Test::Pod::Coverage 1.04+ required for this test!'
   unless eval 'use Test::Pod::Coverage 1.04; 1';
 
-my %RULES = ('SQL::Abstract::Pg' => {also_private => ['insert', 'puke']},);
+my %RULES
+  = ('SQL::Abstract::Pg' => {also_private => ['insert', 'puke', 'select']},);
 pod_coverage_ok($_, $RULES{$_} || {}) for all_modules();
 
 done_testing();
diff --git a/t/sql.t b/t/sql.t
@@ -118,6 +118,20 @@ like $@, qr/for value "update skip locked" not allowed/, 'right error';
 eval { $abstract->select('foo', '*', undef, {for => []}) };
 like $@, qr/ARRAYREF/, 'right error';
 
+# AS
+@sql = $abstract->select('foo', ['bar', [bar => 'baz'], 'yada']);
+is_deeply \@sql, ['SELECT "bar", "bar" AS "baz", "yada" FROM "foo"'],
+  'right query';
+@sql = $abstract->select('foo',
+  ['bar', \'extract(epoch from baz) as baz', 'yada']);
+is_deeply \@sql,
+  ['SELECT "bar", extract(epoch from baz) as baz, "yada" FROM "foo"'],
+  'right query';
+
+# AS (unsupported value)
+eval { $abstract->select('foo', [[]]) };
+like $@, qr/as value needs at least 2 elements/, 'right error';
+
 # JOIN
 @sql = $abstract->select(['foo', ['bar', foo_id => 'id']]);
 is_deeply \@sql,
@@ -147,7 +161,5 @@ is_deeply \@sql,
 # JOIN (unsupported value)
 eval { $abstract->select(['foo', []]) };
 like $@, qr/join value needs at least 3 elements/, 'right error';
-eval { $abstract->select(['foo', [undef, undef, undef]]) };
-like $@, qr/join values cannot be undefined/, 'right error';
 
 done_testing();