add support for group_by and for options

mojolicious · Jan 26, 2018 · 3156c3c · 3156c3c
1 parent f70ae20
commit 3156c3c
Show file tree

Hide file tree

Showing 4 changed files with 59 additions and 4 deletions.
diff --git a/Changes b/Changes
@@ -1,7 +1,7 @@
 
 4.05  2018-01-26
-  - Added support for limit, offset and order_by options to select and select_p
-    methods in Mojo::Pg::Database.
+  - Added support for for, group_by, limit, offset and order_by options to
+    select and select_p methods in Mojo::Pg::Database.
   - Added module SQL::Abstract::Pg.
 
 4.04  2017-12-16

diff --git a/lib/Mojo/Pg/Database.pm b/lib/Mojo/Pg/Database.pm
@@ -496,6 +496,12 @@ L<SQL::Abstract>.
   # "select * from some_table where foo like '%test%'"
   $db->select('some_table', undef, {foo => {-like => '%test%'}});
 
+  # "select * from some_table where foo = 23 group by foo, bar"
+  $db->select('some_table', '*', {foo => 23}, {group_by => \'foo, bar'});
+
+  # "select * from some_table where id = 1 for update skip locked"
+  $db->select('some_table', '*', {id => 1}, {for => \'update skip locked'});
+
 =head2 select_p
 
   my $promise = $db->select_p($source, $fields, $where, \%options);

diff --git a/lib/SQL/Abstract/Pg.pm b/lib/SQL/Abstract/Pg.pm
@@ -1,6 +1,8 @@
 package SQL::Abstract::Pg;
 use Mojo::Base 'SQL::Abstract';
 
+use Carp 'croak';
+
 sub _order_by {
   my ($self, $arg) = @_;
 
@@ -15,10 +17,17 @@ sub _order_by {
 sub _parse {
   my ($self, $options) = @_;
 
-  # ORDER BY
+  # GROUP BY
   my $sql = '';
   my @bind;
-  if (exists $options->{order_by}) {
+  if (defined $options->{group_by}) {
+    croak qq{Unsupported group_by value "$options->{group_by}"}
+      unless ref $options->{group_by} eq 'SCALAR';
+    $sql .= ' GROUP BY ' . ${$options->{group_by}};
+  }
+
+  # ORDER BY
+  if (defined $options->{order_by}) {
     $sql .= $self->_order_by($options->{order_by});
   }
 
@@ -34,6 +43,13 @@ sub _parse {
     push @bind, $options->{offset};
   }
 
+  # FOR
+  if (defined $options->{for}) {
+    croak qq{Unsupported for value "$options->{for}"}
+      unless ref $options->{for} eq 'SCALAR';
+    $sql .= ' FOR ' . ${$options->{for}};
+  }
+
   return $sql, @bind;
 }
 
@@ -80,6 +96,23 @@ with C<LIMIT> and C<OFFSET> clauses.
   # "select * from some_table limit 10 offset 5"
   $abstract->select('some_table', undef, undef, {limit => 10, offset => 5});
 
+=head2 GROUP BY
+
+The C<group_by> option can be used to generate C<SELECT> queries with
+C<GROUP BY> clauses. So far only scalar references to pass literal SQL are
+supported.
+
+  # "select * from some_table group by foo, bar"
+  $abstract->select('some_table', undef, undef, {group_by => \'foo, bar'});
+
+=head2 FOR
+
+The C<for> option can be used to generate C<SELECT> queries with C<FOR> clauses.
+So far only scalar references to pass literal SQL are supported.
+
+  # "select * from some_table for update skip locked"
+  $abstract->select('some_table', undef, undef, {for => \'update skip locked'});
+
 =head1 METHODS
 
 L<SQL::Abstract::Pg> inherits all methods from L<SQL::Abstract>.

diff --git a/t/sql.t b/t/sql.t
@@ -24,4 +24,20 @@ is_deeply \@sql,
 @sql = $abstract->select('foo', '*', undef, {limit => 10, offset => 5});
 is_deeply \@sql, ['SELECT * FROM "foo" LIMIT ? OFFSET ?', 10, 5], 'right query';
 
+# GROUP BY
+@sql = $abstract->select('foo', '*', undef, {group_by => \'bar, baz'});
+is_deeply \@sql, ['SELECT * FROM "foo" GROUP BY bar, baz'], 'right query';
+
+# GROUP BY (unsupported value)
+eval { $abstract->select('foo', '*', undef, {group_by => []}) };
+like $@, qr/Unsupported group_by value "ARRAY/, 'right error';
+
+# FOR
+@sql = $abstract->select('foo', '*', undef, {for => \'update skip locked'});
+is_deeply \@sql, ['SELECT * FROM "foo" FOR update skip locked'], 'right query';
+
+# FOR (unsupported value)
+eval { $abstract->select('foo', '*', undef, {for => []}) };
+like $@, qr/Unsupported for value "ARRAY/, 'right error';
+
 done_testing();