Allow table aliases in JOIN queries

[akarelas]

• SQL::Abstract::Pg version: 1.0
• Perl version: v5.34
• Operating system: Ubuntu Linux 21.04

It's not possible to create table aliases in generated SQL queries. This would be useful for generating queries where a table is JOINed to itself. Especially useful if someone wants to create an ORM based on Mojo::Pg.

A suggestion would be to allow the following syntax:

$abstract->select(
    [
        \'"a" as "t1"',
        [ \'"a" as "t2"', '"t2"."parent_id"' => '"t1"."id"' ],
    ],
);

[kraih]

And you're sure this shouldn't be a regular SQL::Abstract feature?

[shadowcat-mst]

It already is a regular SQL::Abstract feature if you enable the ExtraClauses plugin on version 2.

See https://p3rl.org/SQL::Abstract::Plugin::ExtraClauses#join

[akarelas]

@kraih Yes, to a reasonable degree, because the processing of the array syntax of JOIN happens in SQL::Abstract::Pg.

EDIT: I'm referring to whether aliasing should happen in ::Pg rather than SQLA, not where the array syntax itself should happen.

[shadowcat-mst]

It appears passing more stuff down to SQLA to get access to part of the SQLA2 handling effectively for free is a one-line change that I don't think will cause any regressions in SQLA1 using deployments.

mallet2=; git diff
diff --git a/lib/SQL/Abstract/Pg.pm b/lib/SQL/Abstract/Pg.pm
index 7b8b851..d4f10d7 100644
--- a/lib/SQL/Abstract/Pg.pm
+++ b/lib/SQL/Abstract/Pg.pm
@@ -172,7 +172,7 @@ sub _table {
     my ($type, $name, $fk, $pk, @morekeys) = @$join % 2 == 0 ? @$join : ('', @$join);
     $table
       .= $self->_sqlcase($type =~ /^-(.+)$/ ? " $1 join " : ' join ')
-      . $self->_quote($name)
+      . $self->_table($name)
       . $self->_sqlcase(' on ') . '(';
     do {
       $table

is sufficient to make this work:

mallet2=; plx -Mnew=SQL::Abstract::Pg -E '
    $O->plugin("SQL::Abstract::Plugin::ExtraClauses");
    warn scalar $O->select([{ not_actually_a => { -as => "a" }},["b","a_id","id"]]);
'
SELECT * FROM not_actually_a AS a JOIN b ON (b.a_id = HASH(0x56126be9ebb0).id) at -e line 1.
mallet2=;

For Mojo::Pg I'd expect

$pg->abstract->plugin("SQL::Abstract::Plugin::ExtraClauses");

should do the trick to enable it.

Edit: I AM AN IDIOT.

@akarelas just rightly point outed the HASH(...) thing in my example because $name is getting interpolated again later.