SQL::Ab...aClauses(3)

💖 NAME 💖

okay so this is a User Contributed Perl Documentation page, and the manual name is SQL::Abstract::ExtraClauses(3). The whole vibe? new/experimental additions to SQL::Abstract. Literally the experimental-bestie expansion pack.

---

✨ SYNOPSIS ✨

Bestie, the setup is a whole two lines:

  my $sqla = SQL::Abstract->new;
  SQL::Abstract::ExtraClauses->apply_to($sqla);

---

⚠️ WARNING ⚠️

This module is basically a nursery for things that seem like a good idea to live in until we figure out if we were right about that. So yes, it's giving experimental nursery energy, no cap. 💀

---

🫶 METHODS 🫶

🎀 apply_to 🎀

Applies the plugin to an SQL::Abstract object. That's the whole job, and it ate.

🎀 register_extensions 🎀

Registers the extensions described below. And yes, "below" means the rest of this page, bestie. iykyk.

🎀 cb 🎀

For plugin authors: creates a callback to call a method on the plugin. Very plugin-author core.

🎀 register 🎀

For plugin authors: registers callbacks more easily. Convenience slay.

🎀 sqla 🎀

Available only during plugin callback executions; contains the currently active SQL::Abstract object. It's literally only there during callback time, periodt.

---

🌈 NODE TYPES 🌈

🦋 alias 🦋

Represents a table alias. Expands the name and column names with ident as default. Here's the glow-up:

# expr
{ -alias => [ 't', 'x', 'y', 'z' ] }

# aqt
{ -alias => [
    { -ident => [ 't' ] }, { -ident => [ 'x' ] },
    { -ident => [ 'y' ] }, { -ident => [ 'z' ] },
] }

# query
t(x, y, z)
[]

🦋 as 🦋

Represents an SQL AS. The LHS is expanded with ident as default, and the RHS is treated as a list of arguments for the alias node. Two iconic lil examples:

# expr
{ foo => { -as => 'bar' } }

# aqt
{ -as => [ { -ident => [ 'foo' ] }, { -ident => [ 'bar' ] } ] }

# query
foo AS bar
[]

# expr
{ -as => [ { -select => { _ => 'blah' } }, 't', 'blah' ] }

# aqt
{ -as => [
    { -select =>
        { select => { -op => [ ',', { -ident => [ 'blah' ] } ] } }
    },
    { -alias => [ { -ident => [ 't' ] }, { -ident => [ 'blah' ] } ] },
] }

# query
(SELECT blah) AS t(blah)
[]

🦋 cast 🦋

The cast node is giving literal CAST energy. Here's the expression, the aqt, and the query:

# expr
{ -cast => [ { -ident => 'birthday' }, 'date' ] }

# aqt
{ -func => [
    'cast', {
      -as => [ { -ident => [ 'birthday' ] }, { -ident => [ 'date' ] } ]
    },
] }

# query
CAST(birthday AS date)
[]

🦋 join 🦋

If you give it an arrayref, it pretends it was given a hashref with the first element of the arrayref as the value for 'to' and the remaining pairs copied.

If you give it a hashref, the 'as' key — if present — is expanded to wrap the 'to'.

If present, the 'using' key is expanded as a list of idents.

Known keys are: 'from' (the left hand side), 'type' ('left', 'right', or nothing), 'to' (the right hand side), 'on' and 'using'.

# expr
{ -join => {
    from => 'lft',
    on => { 'lft.bloo' => { '>' => 'rgt.blee' } },
    to => 'rgt',
    type => 'left',
} }

# aqt
{ -join => {
    from => { -ident => [ 'lft' ] },
    on => { -op => [
        '>', { -ident => [ 'lft', 'bloo' ] },
        { -ident => [ 'rgt', 'blee' ] },
    ] },
    to => { -ident => [ 'rgt' ] },
    type => 'left',
} }

# query
lft LEFT JOIN rgt ON lft.bloo > rgt.blee
[]

🦋 from_list 🦋

List of components of the FROM clause; -foo type elements indicate a pair with the next element; this is easiest if I show you:

# expr
{ -from_list => [
    't1', -as => 'table_one', -join =>
    [ 't2', 'on', { 'table_one.x' => 't2.x' } ],
] }

# aqt
{ -join => {
    from =>
      {
        -as => [ { -ident => [ 't1' ] }, { -ident => [ 'table_one' ] } ]
      },
    on => { -op => [
        '=', { -ident => [ 'table_one', 'x' ] },
        { -ident => [ 't2', 'x' ] },
    ] },
    to => { -ident => [ 't2' ] },
    type => undef,
} }

# query
t1 AS table_one JOIN t2 ON table_one.x = t2.x
[]

Or with using:

# expr
{ -from_list =>
    [ 't1', -as => 'table_one', -join => [ 't2', 'using', [ 'x' ] ] ]
}

# aqt
{ -join => {
    from =>
      {
        -as => [ { -ident => [ 't1' ] }, { -ident => [ 'table_one' ] } ]
      },
    to => { -ident => [ 't2' ] },
    type => undef,
    using =>
      { -op => [ 'or', { -op => [ 'or', { -ident => [ 'x' ] } ] } ] },
} }

# query
t1 AS table_one JOIN t2 USING ( x )
[]

With oddities:

# expr
{ -from_list => [
    'x', -join =>
    [ [ 'y', -join => [ 'z', 'type', 'left' ] ], 'type', 'left' ],
] }

# aqt
{ -join => {
    from => { -ident => [ 'x' ] },
    to => { -join => {
        from => { -ident => [ 'y' ] },
        to => { -ident => [ 'z' ] },
        type => 'left',
    } },
    type => 'left',
} }

# query
x LEFT JOIN ( y LEFT JOIN z )
[]

🦋 setops 🦋

Expanders are provided for union, union_all, intersect, intersect_all, except and except_all, and each takes an arrayref of queries:

# expr
{ -union => [
    { -select => { _ => { -value => 1 } } },
    { -select => { _ => { -value => 2 } } },
] }

# aqt
{ -union => { queries => [
      { -select =>
          { select => { -op => [ ',', { -bind => [ undef, 1 ] } ] } }
      },
      { -select =>
          { select => { -op => [ ',', { -bind => [ undef, 2 ] } ] } }
      },
] } }

# query
(SELECT ?) UNION (SELECT ?)
[ 1, 2 ]

# expr
{ -union_all => [
    { -select => { _ => { -value => 1 } } },
    { -select => { _ => { -value => 2 } } },
    { -select => { _ => { -value => 1 } } },
] }

# aqt
{ -union => {
    queries => [
      { -select =>
          { select => { -op => [ ',', { -bind => [ undef, 1 ] } ] } }
      },
      { -select =>
          { select => { -op => [ ',', { -bind => [ undef, 2 ] } ] } }
      },
      { -select =>
          { select => { -op => [ ',', { -bind => [ undef, 1 ] } ] } }
      },
    ],
    type => 'all',
} }

# query
(SELECT ?) UNION ALL (SELECT ?) UNION ALL (SELECT ?)
[ 1, 2, 1 ]

💅 STATEMENT EXTENSIONS 💅

okay bestie, this is where the statements themselves get their little upgrades ✨ same energy, same facts, just with more sparkle.

---

🎀 group by clause for select 🎀

This one expands into a list with an ident default, like so:

# expr
{ -select => { group_by => [ 'foo', 'bar' ] } }

# aqt
{ -select => { group_by =>
      {
        -op => [ ',', { -ident => [ 'foo' ] }, { -ident => [ 'bar' ] } ]
      }
} }

# query
GROUP BY foo, bar
[]

---

🦋 having clause for select 🦋

It’s a basic expr, just like where, because honestly having is pretty much the post-group-by where clause:

# expr
{ -select =>
    { having => { '>' => [ { -count => { -ident => 'foo' } }, 3 ] } }
}

# aqt
{ -select => { having => { -op => [
        '>', { -func => [ 'count', { -ident => [ 'foo' ] } ] },
        { -bind => [ undef, 3 ] },
] } } }

# query
HAVING COUNT(foo) > ?
[ 3 ]

---

🌈 setop clauses 🌈

So if a select query contains a clause matching any of the setop node types, then the clauses that appear before the setop would in the resulting query get gathered together and moved into an inner select node. It’s giving “we’re reorganizing the group chat” energy:

# expr
{ -select => {
    _ => '*',
    from => 'foo',
    order_by => 'baz',
    union =>
      {
        -select => { _ => '*', from => 'bar', where => { thing => 1 } }
      },
    where => { thing => 1 },
} }

# aqt
{ -select => {
    order_by => { -op => [ ',', { -ident => [ 'baz' ] } ] },
    setop => { -union => { queries => [
          { -select => {
              from => { -ident => [ 'foo' ] },
              select => { -op => [ ',', { -ident => [ '*' ] } ] },
              where => { -op => [
                  '=', { -ident => [ 'thing' ] },
                  { -bind => [ 'thing', 1 ] },
              ] },
          } },     ] },
          { -select => {
              from => { -ident => [ 'bar' ] },
              select => { -op => [ ',', { -ident => [ '*' ] } ] },
              where => { -op => [
                  '=', { -ident => [ 'thing' ] },
                  { -bind => [ 'thing', 1 ] },
          } },
    ] } },
} }

# query
(SELECT * FROM foo WHERE thing = ?) UNION (
  SELECT * FROM bar WHERE thing = ?
)
ORDER BY baz
[ 1, 1 ]

---

💜 update from clause 💜

Some databases allow an additional FROM clause to reference other tables for the data to update. This clause is expanded as a normal from list — but fr fr, check your database for what is and isn’t allowed in practice:

# expr
{ -update => {
    _ => 'employees',
    from => 'accounts',
    set => { sales_count => { sales_count => { '+' => \1 } } },
    where => {
      'accounts.name' => { '=' => \"'Acme Corporation'" },
      'employees.id' => { -ident => 'accounts.sales_person' },
    },
} }

# aqt
{ -update => {
    from => { -ident => [ 'accounts' ] },
    set => { -op => [
        ',', { -op => [
            '=', { -ident => [ 'sales_count' ] }, { -op => [
                '+', { -ident => [ 'sales_count' ] },
                { -literal => [ 1 ] },
            ] },
        ] },
    ] },
    target => { -ident => [ 'employees' ] },
    where => { -op => [
        'and', { -op => [
            '=', { -ident => [ 'accounts', 'name' ] },
            { -literal => [ "'Acme Corporation'" ] },
        ] }, { -op => [
            '=', { -ident => [ 'employees', 'id' ] },
            { -ident => [ 'accounts', 'sales_person' ] },
        ] },
    ] },
} }

# query
UPDATE employees SET sales_count = sales_count + 1 FROM accounts
WHERE (
  accounts.name = 'Acme Corporation'
  AND employees.id = accounts.sales_person
)
[]

---

🧚 delete using clause 🧚

Some databases allow an additional USING clause to reference other tables for the data to update. This clause is expanded as a normal from list — again, check your database for what is and isn’t allowed in practice:

# expr
{ -delete => {
    from => 'x',
    using => 'y',
    where => { 'x.id' => { -ident => 'y.x_id' } },
} }

# aqt
{ -delete => {
    target => { -op => [ ',', { -ident => [ 'x' ] } ] },
    using => { -ident => [ 'y' ] },
    where => { -op => [
        '=', { -ident => [ 'x', 'id' ] },
        { -ident => [ 'y', 'x_id' ] },
    ] },
} }

# query
DELETE FROM x USING y WHERE x.id = y.x_id
[]

---

🌸 insert rowvalues and select clauses 🌸

rowvalues and select are shorthand for

{ from => { -select ... } }

and

{ from => { -values ... } }

respectively. So cute, so convenient:

# expr
{ -insert =>
    { into => 'numbers', rowvalues => [ [ 1, 2 ], [ 3, 4 ], [ 5, 6 ] ] }
}

# aqt
{ -insert => {
    from => { -values => [
        { -row =>
            [ { -bind => [ undef, 1 ] }, { -bind => [ undef, 2 ] } ]
        },
        { -row =>
            [ { -bind => [ undef, 3 ] }, { -bind => [ undef, 4 ] } ]
        },
        { -row =>
            [ { -bind => [ undef, 5 ] }, { -bind => [ undef, 6 ] } ]
        },
    ] },
    target => { -ident => [ 'numbers' ] },
} }

# query
INSERT INTO numbers VALUES (?, ?), (?, ?), (?, ?)
[ 1, 2, 3, 4, 5, 6 ]

# expr
{ -insert =>
    { into => 'numbers', select => { _ => '*', from => 'old_numbers' } }
}

# aqt
{ -insert => {
    from => { -select => {
        from => { -ident => [ 'old_numbers' ] },
        select => { -op => [ ',', { -ident => [ '*' ] } ] },
    } },
    target => { -ident => [ 'numbers' ] },
} }

# query
INSERT INTO numbers SELECT * FROM old_numbers
[]

---

👑 with and with_recursive clauses 👑

These clauses are available on select/insert/update/delete queries. Check your database for applicability (e.g. mysql supports all four but mariadb only select).

The value should be an arrayref of name/query pairs:

# expr
{ -select => {
    from => 'foo',
    select => '*',
    with => [ 'foo', { -select => { select => \1 } } ],
} }

# aqt
{ -select => {
    from => { -ident => [ 'foo' ] },
    select => { -op => [ ',', { -ident => [ '*' ] } ] },
    with => { queries => [ [
          { -ident => [ 'foo' ] }, { -select =>
              { select => { -op => [ ',', { -literal => [ 1 ] } ] } }
          },
    ] ] },
} }

# query
WITH foo AS (SELECT 1) SELECT * FROM foo
[]

A more complete example — designed for mariadb, lowkey (ab)using the fact that mysqloids materialise subselects in FROM into an unindexed temp table to circumvent the restriction that you can’t select from the table you’re currently updating:

# expr
{ -update => {
    _ => [
      'tree_table', -join => {
        as => 'tree',
        on => { 'tree.id' => 'tree_with_path.id' },
        to => { -select => {
            from => 'tree_with_path',
            select => '*',
            with_recursive => [
              [ 'tree_with_path', 'id', 'parent_id', 'path' ],
              { -select => {
                  _ => [
                    'id', 'parent_id', { -as => [
                        { -cast => { -as => [ 'id', 'char', 255 ] } },
                        'path',
                    ] } ],
                  from => 'tree_table',
                  union_all => { -select => {
                      _ => [
                        't.id', 't.parent_id', { -as => [
                            { -concat => [ 'r.path', \"'/'", 't.id' ] },
                            'path',
                        ] },
                      ],
                      from => [
                        'tree_table', -as => 't', -join => {
                          as => 'r',
                          on => { 't.parent_id' => 'r.id' },
                          to => 'tree_with_path',
                        },
                      ],
                  } },
                  where => { parent_id => undef },
              } },
            ],
        } },
      },
    ],
    set => { path => { -ident => [ 'tree', 'path' ] } },
} }

# query
UPDATE
  tree_table JOIN
  (
    WITH RECURSIVE
      tree_with_path(id, parent_id, path) AS (
        (
          SELECT id, parent_id, CAST(id AS char(255)) AS path
          FROM tree_table WHERE parent_id IS NULL
        ) UNION ALL (
          SELECT t.id, t.parent_id, CONCAT(r.path, '/', t.id) AS path
          FROM
            tree_table AS t JOIN tree_with_path AS r ON
            t.parent_id = r.id
        )
      )
    SELECT * FROM tree_with_path
  ) AS tree
  ON tree.id = tree_with_path.id
SET path = tree.path
[]

---

and that’s the whole statement-extensions moment, bestie 🫶 this page is from perl v5.42.0, dated 2021-01-24 — iconic behavior, no notes 💅✨