add support for INNER JOIN

mojolicious · Jan 27, 2018 · 71bf0a2 · 71bf0a2
1 parent 84dc9b3
commit 71bf0a2
Show file tree

Hide file tree

Showing 5 changed files with 75 additions and 1 deletion.
diff --git a/Changes b/Changes
@@ -1,5 +1,7 @@
 
-4.07  2018-01-27
+4.07  2018-01-28
+  - Added support for "INNER JOIN" 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
     "ON CONFLICT DO NOTHING".

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

diff --git a/lib/SQL/Abstract/Pg.pm b/lib/SQL/Abstract/Pg.pm
@@ -108,6 +108,31 @@ sub _order_by {
   return $sql, @bind;
 }
 
+sub _table {
+  my ($self, $table) = @_;
+
+  return $self->SUPER::_table($table) unless ref $table eq 'ARRAY';
+
+  my (@table, @join);
+  for my $t (@$table) {
+    if   (ref $t eq 'ARRAY') { push @join,  $t }
+    else                     { push @table, $t }
+  }
+
+  $table = $self->SUPER::_table(\@table);
+  for my $join (@join) {
+    my ($name, $pk, $fk) = @$join;
+    $table
+      .= $self->_sqlcase(' join ')
+      . $self->_quote($name)
+      . $self->_sqlcase(' on ') . '('
+      . $self->_quote("$name.$pk") . ' = '
+      . $self->_quote("$table[0].$fk") . ')';
+  }
+
+  return $table;
+}
+
 1;
 
 =encoding utf8
@@ -206,6 +231,15 @@ pass literal SQL are supported.
   # "select * from some_table for update skip locked"
   $abstract->select('some_table', '*, undef, {for => \'update skip locked'});
 
+=head2 JOIN
+
+The C<$source> argument now also accepts array references containing not only
+table names, but also array references with tables to generate C<JOIN> clauses
+for.
+
+  # "select * from foo join bar on (bar.foo_id = foo.id)"
+  $abstract->select(['foo', ['bar', 'foo_id', 'id']], '*');
+
 =head1 METHODS
 
 L<SQL::Abstract::Pg> inherits all methods from L<SQL::Abstract>.

diff --git a/t/crud.t b/t/crud.t
@@ -142,6 +142,25 @@ my $fail;
 $db->dollar_only->query_p('does_not_exist')->catch(sub { $fail = shift })->wait;
 like $fail, qr/does_not_exist/, 'right error';
 
+# Join
+$db->query(
+  'create table if not exists crud_test4 (
+     id    serial primary key,
+     test1 text
+   )'
+);
+$db->query(
+  'create table if not exists crud_test5 (
+     id    serial primary key,
+     test2 text
+   )'
+);
+$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'}],
+  'right structure';
+
 # Clean up once we are done
 $pg->db->query('drop schema mojo_crud_test cascade');
 

diff --git a/t/sql.t b/t/sql.t
@@ -10,6 +10,8 @@ is_deeply [$abstract->insert('foo', {bar => 'baz'})],
   ['INSERT INTO "foo" ( "bar") VALUES ( ? )', 'baz'], 'right query';
 is_deeply [$abstract->select('foo', '*')], ['SELECT * FROM "foo"'],
   'right query';
+is_deeply [$abstract->select(['foo', 'bar', 'baz'])],
+  ['SELECT * FROM "foo", "bar", "baz"'], 'right query';
 
 # ON CONFLICT
 my @sql
@@ -99,4 +101,18 @@ like $@, qr/SCALAR value "update skip locked" not allowed/, 'right error';
 eval { $abstract->select('foo', '*', undef, {for => []}) };
 like $@, qr/ARRAYREF/, 'right error';
 
+# JOIN
+@sql = $abstract->select(['foo', ['bar', 'foo_id', 'id']]);
+is_deeply \@sql,
+  ['SELECT * FROM "foo" JOIN "bar" ON ("bar"."foo_id" = "foo"."id")'],
+  'right query';
+@sql = $abstract->select(
+  ['foo', ['bar', 'foo_id', 'id'], ['baz', 'foo_id', 'id']]);
+$result
+  = [ 'SELECT * FROM "foo"'
+    . ' JOIN "bar" ON ("bar"."foo_id" = "foo"."id")'
+    . ' JOIN "baz" ON ("baz"."foo_id" = "foo"."id")'
+  ];
+is_deeply \@sql, $result, 'right query';
+
 done_testing();