add support for "GROUP BY" with "HAVING"

mojolicious · Jan 28, 2018 · 842b386 · 842b386
1 parent 13ac0fa
commit 842b386
Show file tree

Hide file tree

Showing 4 changed files with 38 additions and 1 deletion.
diff --git a/Changes b/Changes
@@ -1,6 +1,8 @@
 
 4.07  2018-01-28
-  - Added support for "INNER JOIN" to select and select_p methods in
+  - Added support for "JOIN" 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
     Mojo::Pg::Database with shortcuts for "ON CONFLICT DO UPDATE SET" and

diff --git a/lib/Mojo/Pg/Database.pm b/lib/Mojo/Pg/Database.pm
@@ -517,6 +517,9 @@ As well as some PostgreSQL specific extensions added by L<SQL::Abstract::Pg>.
   # "select * from some_table where foo = 23 group by foo, bar"
   $db->select('some_table', '*', {foo => 23}, {group_by => ['foo', 'bar']});
 
+  # "select * from t where a = 'b' group by c having d = 'e'"
+  $db->select('t', '*', {a => 'b'}, {group_by => ['c'], having => {d => 'e'}});
+
   # "select * from some_table where id = 1 for update"
   $db->select('some_table', '*', {id => 1}, {for => 'update'});
 

diff --git a/lib/SQL/Abstract/Pg.pm b/lib/SQL/Abstract/Pg.pm
@@ -75,6 +75,13 @@ sub _order_by {
     $sql .= $self->_sqlcase(' group by ') . $group_sql;
   }
 
+  # HAVING
+  if (defined(my $having = $options->{having})) {
+    my ($having_sql, @having_bind) = $self->_recurse_where($having);
+    $sql .= $self->_sqlcase(' having ') . $having_sql;
+    push @bind, @having_bind;
+  }
+
   # ORDER BY
   $sql .= $self->_order_by($options->{order_by})
     if defined $options->{order_by};
@@ -221,6 +228,14 @@ references to pass literal SQL are supported.
   # "select * from some_table group by foo, bar"
   $abstract->select('some_table', '*', undef, {group_by => \'foo, bar'});
 
+=head2 HAVING
+
+The C<having> option can be used to generate C<SELECT> queries with C<HAVING>
+clauses. Takes the same arguments as the C<$where> argument.
+
+  # "select * from t group by a having b = 'c'"
+  $abstract->select('t', '*', undef, {group_by => ['a'], having => {b => 'c'}});
+
 =head2 FOR
 
 The C<for> option can be used to generate C<SELECT> queries with C<FOR> clauses.

diff --git a/t/sql.t b/t/sql.t
@@ -85,6 +85,23 @@ is_deeply \@sql, ['SELECT * FROM "foo" GROUP BY bar, baz'], 'right query';
 @sql = $abstract->select('foo', '*', undef, {group_by => ['bar', 'baz']});
 is_deeply \@sql, ['SELECT * FROM "foo" GROUP BY "bar", "baz"'], 'right query';
 
+# HAVING
+@sql = $abstract->select('foo', '*', undef,
+  {group_by => ['bar'], having => {baz => 'yada'}});
+is_deeply \@sql,
+  ['SELECT * FROM "foo" GROUP BY "bar" HAVING "baz" = ?', 'yada'],
+  'right query';
+@sql = $abstract->select(
+  'foo', '*',
+  {bar => {'>' => 'baz'}},
+  {group_by => ['bar'], having => {baz => {'<' => 'bar'}}}
+);
+$result = [
+  'SELECT * FROM "foo" WHERE ( "bar" > ? ) GROUP BY "bar" HAVING "baz" < ?',
+  'baz', 'bar'
+];
+is_deeply \@sql, $result, 'right query';
+
 # GROUP BY (unsupported value)
 eval { $abstract->select('foo', '*', undef, {group_by => {}}) };
 like $@, qr/HASHREF/, 'right error';