add support for LIMIT and OFFSET

mojolicious · Jan 26, 2018 · 3de699a · 3de699a
1 parent 530db71
commit 3de699a
Show file tree

Hide file tree

Showing 6 changed files with 120 additions and 12 deletions.
diff --git a/Changes b/Changes
@@ -1,5 +1,8 @@
 
-4.05  2017-12-17
+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 module SQL::Abstract::Pg.
 
 4.04  2017-12-16
   - Added db attribute to Mojo::Pg::Results.

diff --git a/lib/Mojo/Pg.pm b/lib/Mojo/Pg.pm
@@ -8,10 +8,14 @@ use Mojo::Pg::Migrations;
 use Mojo::Pg::PubSub;
 use Mojo::URL;
 use Scalar::Util qw(blessed weaken);
-use SQL::Abstract;
+use SQL::Abstract::Pg;
 
 has abstract => sub {
-  SQL::Abstract->new(array_datatypes => 1, name_sep => '.', quote_char => '"');
+  SQL::Abstract::Pg->new(
+    array_datatypes => 1,
+    name_sep        => '.',
+    quote_char      => '"'
+  );
 };
 has [qw(auto_migrate parent search_path)];
 has database_class  => 'Mojo::Pg::Database';
@@ -329,11 +333,11 @@ L<Mojo::Pg> implements the following attributes.
 =head2 abstract
 
   my $abstract = $pg->abstract;
-  $pg          = $pg->abstract(SQL::Abstract->new);
+  $pg          = $pg->abstract(SQL::Abstract::Pg->new);
 
-L<SQL::Abstract> object used to generate CRUD queries for L<Mojo::Pg::Database>,
-defaults to enabling C<array_datatypes> and setting C<name_sep> to C<.> and
-C<quote_char> to C<">.
+L<SQL::Abstract::Pg> object used to generate CRUD queries for
+L<Mojo::Pg::Database>, defaults to enabling C<array_datatypes> and setting
+C<name_sep> to C<.> and C<quote_char> to C<">.
 
   # Generate WHERE clause and bind values
   my($stmt, @bind) = $pg->abstract->where({foo => 'bar', baz => 'yada'});
@@ -533,6 +537,8 @@ This is the class hierarchy of the L<Mojo::Pg> distribution.
 
 =item * L<Mojo::Pg::Transaction>
 
+=item * L<SQL::Abstract::Pg>
+
 =back
 
 =head1 AUTHOR
@@ -559,7 +565,7 @@ William Lindley
 
 =head1 COPYRIGHT AND LICENSE
 
-Copyright (C) 2014-2017, Sebastian Riedel and others.
+Copyright (C) 2014-2018, Sebastian Riedel and others.
 
 This program is free software, you can redistribute it and/or modify it under
 the terms of the Artistic License version 2.0.

diff --git a/lib/Mojo/Pg/Database.pm b/lib/Mojo/Pg/Database.pm
@@ -463,7 +463,7 @@ L<Mojo::Promise> object instead of accepting a callback.
 
 =head2 select
 
-  my $results = $db->select($source, $fields, $where, $order);
+  my $results = $db->select($source, $fields, $where, \%options);
 
 Generate a C<SELECT> statement with L<Mojo::Pg/"abstract"> (usually an
 L<SQL::Abstract> object) and execute it with L</"query">. You can also append a
@@ -488,14 +488,17 @@ L<SQL::Abstract>.
   $db->select('some_table', undef, {foo => 'bar'});
 
   # "select * from some_table where foo = 'bar' order by id desc"
-  $db->select('some_table', undef, {foo => 'bar'}, {-desc => 'id'});
+  $db->select('some_table', '*', {foo => 'bar'}, {order_by => {-desc => 'id'}});
+
+  # "select * from some_table limit 10 offset 20"
+  $db->select('some_table', undef, undef, {limit => 10, offset => 20});
 
   # "select * from some_table where foo like '%test%'"
   $db->select('some_table', undef, {foo => {-like => '%test%'}});
 
 =head2 select_p
 
-  my $promise = $db->select_p($source, $fields, $where, $order);
+  my $promise = $db->select_p($source, $fields, $where, \%options);
 
 Same as L</"select">, but performs all operations non-blocking and returns a
 L<Mojo::Promise> object instead of accepting a callback.

diff --git a/lib/SQL/Abstract/Pg.pm b/lib/SQL/Abstract/Pg.pm
@@ -0,0 +1,65 @@
+package SQL::Abstract::Pg;
+use Mojo::Base 'SQL::Abstract';
+
+sub _order_by {
+  my ($self, $arg) = @_;
+
+  # Legacy
+  return $self->SUPER::_order_by($arg)
+    if ref $arg ne 'HASH'
+    or grep {/^-(?:desc|asc)/i} keys %$arg;
+
+  return $self->_parse($arg);
+}
+
+sub _parse {
+  my ($self, $options) = @_;
+
+  # ORDER BY
+  my $sql = '';
+  my @bind;
+  if (exists $options->{order_by}) {
+    $sql .= $self->_order_by($options->{order_by});
+  }
+
+  # LIMIT
+  if (defined $options->{limit}) {
+    $sql .= ' LIMIT ?';
+    push @bind, $options->{limit};
+  }
+
+  # OFFSET
+  if (defined $options->{offset}) {
+    $sql .= ' OFFSET ?';
+    push @bind, $options->{offset};
+  }
+
+  return $sql, @bind;
+}
+
+1;
+
+=encoding utf8
+
+=head1 NAME
+
+SQL::Abstract::Pg - PostgreSQL Magic
+
+=head1 SYNOPSIS
+
+  my $abstract = SQL::Abstract::Pg->new;
+
+=head1 DESCRIPTION
+
+L<SQL::Abstract::Pg> extends L<SQL::Abstract> with a few PostgreSQL features
+used by L<Mojo::Pg>.
+
+=head1 METHODS
+
+L<SQL::Abstract::Pg> inherits all methods from L<SQL::Abstract>.
+
+=head1 SEE ALSO
+
+L<Mojo::Pg>, L<Mojolicious::Guides>, L<http://mojolicious.org>.
+
+=cut
diff --git a/t/crud.t b/t/crud.t
@@ -40,6 +40,10 @@ is_deeply $db->select('crud_test', ['name'], {name => 'foo'})->hashes->to_array,
   [{name => 'foo'}], 'right structure';
 is_deeply $db->select('crud_test', ['name'], undef, {-desc => 'id'})
   ->hashes->to_array, [{name => 'bar'}, {name => 'foo'}], 'right structure';
+is_deeply $db->select('crud_test', undef, undef, {offset => 1})
+  ->hashes->to_array, [{id => 2, name => 'bar'}], 'right structure';
+is_deeply $db->select('crud_test', undef, undef, {limit => 1})
+  ->hashes->to_array, [{id => 1, name => 'foo'}], 'right structure';
 
 # Non-blocking read
 my $result;
@@ -107,7 +111,7 @@ is $result->{name}, 'promise', 'right result';
 $result = undef;
 my $first  = $pg->db->query_p("select * from crud_test where name = 'promise'");
 my $second = $pg->db->query_p("select * from crud_test where name = 'promise'");
-$first->all($second)->then(
+Mojo::Promise->all($first, $second)->then(
   sub {
     my ($first, $second) = @_;
     $result = [$first->[0]->hash, $second->[0]->hash];

diff --git a/t/sql.t b/t/sql.t
@@ -0,0 +1,27 @@
+use Mojo::Base -strict;
+
+use Test::More;
+use Mojo::Pg;
+
+# Basics
+my $pg       = Mojo::Pg->new;
+my $abstract = $pg->abstract;
+is_deeply [$abstract->select('foo', '*')], ['SELECT * FROM "foo"'],
+  'right query';
+
+# ORDER BY
+my @sql = $abstract->select('foo', '*', {bar => 'baz'}, {-desc => 'yada'});
+is_deeply \@sql,
+  ['SELECT * FROM "foo" WHERE ( "bar" = ? ) ORDER BY "yada" DESC', 'baz'],
+  'right query';
+@sql = $abstract->select('foo', '*', {bar => 'baz'},
+  {order_by => {-desc => 'yada'}});
+is_deeply \@sql,
+  ['SELECT * FROM "foo" WHERE ( "bar" = ? ) ORDER BY "yada" DESC', 'baz'],
+  'right query';
+
+# LIMIT/OFFSET
+@sql = $abstract->select('foo', '*', undef, {limit => 10, offset => 5});
+is_deeply \@sql, ['SELECT * FROM "foo" LIMIT ? OFFSET ?', 10, 5], 'right query';
+
+done_testing();