.\" Automatically generated by Pod::Man 4.14 (Pod::Simple 3.40)
.\"
.\" Standard preamble:
.\" ========================================================================
.de Sp \" Vertical space (when we can't use .PP)
.if t .sp .5v
.if n .sp
..
.de Vb \" Begin verbatim text
.ft CW
.nf
.ne \\$1
..
.de Ve \" End verbatim text
.ft R
.fi
..
.\" Set up some character translations and predefined strings.  \*(-- will
.\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left
.\" double quote, and \*(R" will give a right double quote.  \*(C+ will
.\" give a nicer C++.  Capital omega is used to do unbreakable dashes and
.\" therefore won't be available.  \*(C` and \*(C' expand to `' in nroff,
.\" nothing in troff, for use with C<>.
.tr \(*W-
.ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p'
.ie n \{\
.    ds -- \(*W-
.    ds PI pi
.    if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch
.    if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\"  diablo 12 pitch
.    ds L" ""
.    ds R" ""
.    ds C` ""
.    ds C' ""
'br\}
.el\{\
.    ds -- \|\(em\|
.    ds PI \(*p
.    ds L" ``
.    ds R" ''
.    ds C`
.    ds C'
'br\}
.\"
.\" Escape single quotes in literal strings from groff's Unicode transform.
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\"
.\" If the F register is >0, we'll generate index entries on stderr for
.\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index
.\" entries marked with X<> in POD.  Of course, you'll have to process the
.\" output yourself in some meaningful fashion.
.\"
.\" Avoid warning from groff about undefined register 'F'.
.de IX
..
.nr rF 0
.if \n(.g .if rF .nr rF 1
.if (\n(rF:(\n(.g==0)) \{\
.    if \nF \{\
.        de IX
.        tm Index:\\$1\t\\n%\t"\\$2"
..
.        if !\nF==2 \{\
.            nr % 0
.            nr F 2
.        \}
.    \}
.\}
.rr rF
.\" ========================================================================
.\"
.IX Title "SQL::Abstract::Plugin::ExtraClauses 3pm"
.TH SQL::Abstract::Plugin::ExtraClauses 3pm "2021-09-30" "perl v5.32.1" "User Contributed Perl Documentation"
.\" For nroff, turn off justification.  Always turn off hyphenation; it makes
.\" way too many mistakes in technical documents.
.if n .ad l
.nh
.SH "NAME"
SQL::Abstract::ExtraClauses \- new/experimental additions to SQL::Abstract
.SH "SYNOPSIS"
.IX Header "SYNOPSIS"
.Vb 2
\&  my $sqla = SQL::Abstract\->new;
\&  SQL::Abstract::ExtraClauses\->apply_to($sqla);
.Ve
.SH "WARNING"
.IX Header "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.
.SH "METHODS"
.IX Header "METHODS"
.SS "apply_to"
.IX Subsection "apply_to"
Applies the plugin to an SQL::Abstract object.
.SS "register_extensions"
.IX Subsection "register_extensions"
Registers the extensions described below
.SS "cb"
.IX Subsection "cb"
For plugin authors, creates a callback to call a method on the plugin.
.SS "register"
.IX Subsection "register"
For plugin authors, registers callbacks more easily.
.SS "sqla"
.IX Subsection "sqla"
Available only during plugin callback executions, contains the currently
active SQL::Abstract object.
.SH "NODE TYPES"
.IX Header "NODE TYPES"
.SS "alias"
.IX Subsection "alias"
Represents a table alias. Expands name and column names with ident as default.
.PP
.Vb 2
\&  # expr
\&  { \-alias => [ \*(Aqt\*(Aq, \*(Aqx\*(Aq, \*(Aqy\*(Aq, \*(Aqz\*(Aq ] }
\&
\&  # aqt
\&  { \-alias => [
\&      { \-ident => [ \*(Aqt\*(Aq ] }, { \-ident => [ \*(Aqx\*(Aq ] },
\&      { \-ident => [ \*(Aqy\*(Aq ] }, { \-ident => [ \*(Aqz\*(Aq ] },
\&  ] }
\&
\&  # query
\&  t(x, y, z)
\&  []
.Ve
.SS "as"
.IX Subsection "as"
Represents an sql \s-1AS. LHS\s0 is expanded with ident as default, \s-1RHS\s0 is treated
as a list of arguments for the alias node.
.PP
.Vb 2
\&  # expr
\&  { foo => { \-as => \*(Aqbar\*(Aq } }
\&
\&  # aqt
\&  { \-as => [ { \-ident => [ \*(Aqfoo\*(Aq ] }, { \-ident => [ \*(Aqbar\*(Aq ] } ] }
\&
\&  # query
\&  foo AS bar
\&  []
\&
\&  # expr
\&  { \-as => [ { \-select => { _ => \*(Aqblah\*(Aq } }, \*(Aqt\*(Aq, \*(Aqblah\*(Aq ] }
\&
\&  # aqt
\&  { \-as => [
\&      { \-select =>
\&          { select => { \-op => [ \*(Aq,\*(Aq, { \-ident => [ \*(Aqblah\*(Aq ] } ] } }
\&      },
\&      { \-alias => [ { \-ident => [ \*(Aqt\*(Aq ] }, { \-ident => [ \*(Aqblah\*(Aq ] } ] },
\&  ] }
\&
\&  # query
\&  (SELECT blah) AS t(blah)
\&  []
.Ve
.SS "cast"
.IX Subsection "cast"
.Vb 2
\&  # expr
\&  { \-cast => [ { \-ident => \*(Aqbirthday\*(Aq }, \*(Aqdate\*(Aq ] }
\&
\&  # aqt
\&  { \-func => [
\&      \*(Aqcast\*(Aq, {
\&        \-as => [ { \-ident => [ \*(Aqbirthday\*(Aq ] }, { \-ident => [ \*(Aqdate\*(Aq ] } ]
\&      },
\&  ] }
\&
\&  # query
\&  CAST(birthday AS date)
\&  []
.Ve
.SS "join"
.IX Subsection "join"
If given an arrayref, pretends it was given a hashref with the first
element of the arrayref as the value for 'to' and the remaining pairs copied.
.PP
Given a hashref, the 'as' key is if presented expanded to wrap the 'to'.
.PP
If present the 'using' key is expanded as a list of idents.
.PP
Known keys are: 'from' (the left hand side), 'type' ('left', 'right', or
nothing), 'to' (the right hand side), 'on' and 'using'.
.PP
.Vb 7
\&  # expr
\&  { \-join => {
\&      from => \*(Aqlft\*(Aq,
\&      on => { \*(Aqlft.bloo\*(Aq => { \*(Aq>\*(Aq => \*(Aqrgt.blee\*(Aq } },
\&      to => \*(Aqrgt\*(Aq,
\&      type => \*(Aqleft\*(Aq,
\&  } }
\&
\&  # aqt
\&  { \-join => {
\&      from => { \-ident => [ \*(Aqlft\*(Aq ] },
\&      on => { \-op => [
\&          \*(Aq>\*(Aq, { \-ident => [ \*(Aqlft\*(Aq, \*(Aqbloo\*(Aq ] },
\&          { \-ident => [ \*(Aqrgt\*(Aq, \*(Aqblee\*(Aq ] },
\&      ] },
\&      to => { \-ident => [ \*(Aqrgt\*(Aq ] },
\&      type => \*(Aqleft\*(Aq,
\&  } }
\&
\&  # query
\&  lft LEFT JOIN rgt ON lft.bloo > rgt.blee
\&  []
.Ve
.SS "from_list"
.IX Subsection "from_list"
List of components of the \s-1FROM\s0 clause; \-foo type elements indicate a pair
with the next element; this is easiest if I show you:
.PP
.Vb 5
\&  # expr
\&  { \-from_list => [
\&      \*(Aqt1\*(Aq, \-as => \*(Aqtable_one\*(Aq, \-join =>
\&      [ \*(Aqt2\*(Aq, \*(Aqon\*(Aq, { \*(Aqtable_one.x\*(Aq => \*(Aqt2.x\*(Aq } ],
\&  ] }
\&
\&  # aqt
\&  { \-join => {
\&      from =>
\&        {
\&          \-as => [ { \-ident => [ \*(Aqt1\*(Aq ] }, { \-ident => [ \*(Aqtable_one\*(Aq ] } ]
\&        },
\&      on => { \-op => [
\&          \*(Aq=\*(Aq, { \-ident => [ \*(Aqtable_one\*(Aq, \*(Aqx\*(Aq ] },
\&          { \-ident => [ \*(Aqt2\*(Aq, \*(Aqx\*(Aq ] },
\&      ] },
\&      to => { \-ident => [ \*(Aqt2\*(Aq ] },
\&      type => undef,
\&  } }
\&
\&  # query
\&  t1 AS table_one JOIN t2 ON table_one.x = t2.x
\&  []
.Ve
.PP
Or with using:
.PP
.Vb 4
\&  # expr
\&  { \-from_list =>
\&      [ \*(Aqt1\*(Aq, \-as => \*(Aqtable_one\*(Aq, \-join => [ \*(Aqt2\*(Aq, \*(Aqusing\*(Aq, [ \*(Aqx\*(Aq ] ] ]
\&  }
\&
\&  # aqt
\&  { \-join => {
\&      from =>
\&        {
\&          \-as => [ { \-ident => [ \*(Aqt1\*(Aq ] }, { \-ident => [ \*(Aqtable_one\*(Aq ] } ]
\&        },
\&      to => { \-ident => [ \*(Aqt2\*(Aq ] },
\&      type => undef,
\&      using =>
\&        { \-op => [ \*(Aqor\*(Aq, { \-op => [ \*(Aqor\*(Aq, { \-ident => [ \*(Aqx\*(Aq ] } ] } ] },
\&  } }
\&
\&  # query
\&  t1 AS table_one JOIN t2 USING ( x )
\&  []
.Ve
.PP
With oddities:
.PP
.Vb 5
\&  # expr
\&  { \-from_list => [
\&      \*(Aqx\*(Aq, \-join =>
\&      [ [ \*(Aqy\*(Aq, \-join => [ \*(Aqz\*(Aq, \*(Aqtype\*(Aq, \*(Aqleft\*(Aq ] ], \*(Aqtype\*(Aq, \*(Aqleft\*(Aq ],
\&  ] }
\&
\&  # aqt
\&  { \-join => {
\&      from => { \-ident => [ \*(Aqx\*(Aq ] },
\&      to => { \-join => {
\&          from => { \-ident => [ \*(Aqy\*(Aq ] },
\&          to => { \-ident => [ \*(Aqz\*(Aq ] },
\&          type => \*(Aqleft\*(Aq,
\&      } },
\&      type => \*(Aqleft\*(Aq,
\&  } }
\&
\&  # query
\&  x LEFT JOIN ( y LEFT JOIN z )
\&  []
.Ve
.SS "setops"
.IX Subsection "setops"
Expanders are provided for union, union_all, intersect, intersect_all,
except and except_all, and each takes an arrayref of queries:
.PP
.Vb 5
\&  # expr
\&  { \-union => [
\&      { \-select => { _ => { \-value => 1 } } },
\&      { \-select => { _ => { \-value => 2 } } },
\&  ] }
\&
\&  # aqt
\&  { \-union => { queries => [
\&        { \-select =>
\&            { select => { \-op => [ \*(Aq,\*(Aq, { \-bind => [ undef, 1 ] } ] } }
\&        },
\&        { \-select =>
\&            { select => { \-op => [ \*(Aq,\*(Aq, { \-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 => [ \*(Aq,\*(Aq, { \-bind => [ undef, 1 ] } ] } }
\&        },
\&        { \-select =>
\&            { select => { \-op => [ \*(Aq,\*(Aq, { \-bind => [ undef, 2 ] } ] } }
\&        },
\&        { \-select =>
\&            { select => { \-op => [ \*(Aq,\*(Aq, { \-bind => [ undef, 1 ] } ] } }
\&        },
\&      ],
\&      type => \*(Aqall\*(Aq,
\&  } }
\&
\&  # query
\&  (SELECT ?) UNION ALL (SELECT ?) UNION ALL (SELECT ?)
\&  [ 1, 2, 1 ]
.Ve
.SH "STATEMENT EXTENSIONS"
.IX Header "STATEMENT EXTENSIONS"
.SS "group by clause for select"
.IX Subsection "group by clause for select"
Expanded as a list with an ident default:
.PP
.Vb 2
\&  # expr
\&  { \-select => { group_by => [ \*(Aqfoo\*(Aq, \*(Aqbar\*(Aq ] } }
\&
\&  # aqt
\&  { \-select => { group_by =>
\&        {
\&          \-op => [ \*(Aq,\*(Aq, { \-ident => [ \*(Aqfoo\*(Aq ] }, { \-ident => [ \*(Aqbar\*(Aq ] } ]
\&        }
\&  } }
\&
\&  # query
\&  GROUP BY foo, bar
\&  []
.Ve
.SS "having clause for select"
.IX Subsection "having clause for select"
Basic expr, just like where, given having is pretty much post-group-by
where clause:
.PP
.Vb 4
\&  # expr
\&  { \-select =>
\&      { having => { \*(Aq>\*(Aq => [ { \-count => { \-ident => \*(Aqfoo\*(Aq } }, 3 ] } }
\&  }
\&
\&  # aqt
\&  { \-select => { having => { \-op => [
\&          \*(Aq>\*(Aq, { \-func => [ \*(Aqcount\*(Aq, { \-ident => [ \*(Aqfoo\*(Aq ] } ] },
\&          { \-bind => [ undef, 3 ] },
\&  ] } } }
\&
\&  # query
\&  HAVING COUNT(foo) > ?
\&  [ 3 ]
.Ve
.SS "setop clauses"
.IX Subsection "setop clauses"
If a select query contains a clause matching any of the setop node types,
clauses that appear before the setop would in the resulting query are
gathered together and moved into an inner select node:
.PP
.Vb 11
\&  # expr
\&  { \-select => {
\&      _ => \*(Aq*\*(Aq,
\&      from => \*(Aqfoo\*(Aq,
\&      order_by => \*(Aqbaz\*(Aq,
\&      union =>
\&        {
\&          \-select => { _ => \*(Aq*\*(Aq, from => \*(Aqbar\*(Aq, where => { thing => 1 } }
\&        },
\&      where => { thing => 1 },
\&  } }
\&
\&  # aqt
\&  { \-select => {
\&      order_by => { \-op => [ \*(Aq,\*(Aq, { \-ident => [ \*(Aqbaz\*(Aq ] } ] },
\&      setop => { \-union => { queries => [
\&            { \-select => {
\&                from => { \-ident => [ \*(Aqfoo\*(Aq ] },
\&                select => { \-op => [ \*(Aq,\*(Aq, { \-ident => [ \*(Aq*\*(Aq ] } ] },
\&                where => { \-op => [
\&                    \*(Aq=\*(Aq, { \-ident => [ \*(Aqthing\*(Aq ] },
\&                    { \-bind => [ \*(Aqthing\*(Aq, 1 ] },
\&                ] },
\&            } },     ] },
\&            { \-select => {
\&                from => { \-ident => [ \*(Aqbar\*(Aq ] },
\&                select => { \-op => [ \*(Aq,\*(Aq, { \-ident => [ \*(Aq*\*(Aq ] } ] },
\&                where => { \-op => [
\&                    \*(Aq=\*(Aq, { \-ident => [ \*(Aqthing\*(Aq ] },
\&                    { \-bind => [ \*(Aqthing\*(Aq, 1 ] },
\&            } },
\&      ] } },
\&  } }
\&
\&  # query
\&  (SELECT * FROM foo WHERE thing = ?) UNION (
\&    SELECT * FROM bar WHERE thing = ?
\&  )
\&  ORDER BY baz
\&  [ 1, 1 ]
.Ve
.SS "update from clause"
.IX Subsection "update from clause"
Some databases allow an additional \s-1FROM\s0 clause to reference other tables
for the data to update; this clause is expanded as a normal from list, check
your database for what is and isn't allowed in practice.
.PP
.Vb 10
\&  # expr
\&  { \-update => {
\&      _ => \*(Aqemployees\*(Aq,
\&      from => \*(Aqaccounts\*(Aq,
\&      set => { sales_count => { sales_count => { \*(Aq+\*(Aq => \e1 } } },
\&      where => {
\&        \*(Aqaccounts.name\*(Aq => { \*(Aq=\*(Aq => \e"\*(AqAcme Corporation\*(Aq" },
\&        \*(Aqemployees.id\*(Aq => { \-ident => \*(Aqaccounts.sales_person\*(Aq },
\&      },
\&  } }
\&
\&  # aqt
\&  { \-update => {
\&      from => { \-ident => [ \*(Aqaccounts\*(Aq ] },
\&      set => { \-op => [
\&          \*(Aq,\*(Aq, { \-op => [
\&              \*(Aq=\*(Aq, { \-ident => [ \*(Aqsales_count\*(Aq ] }, { \-op => [
\&                  \*(Aq+\*(Aq, { \-ident => [ \*(Aqsales_count\*(Aq ] },
\&                  { \-literal => [ 1 ] },
\&              ] },
\&          ] },
\&      ] },
\&      target => { \-ident => [ \*(Aqemployees\*(Aq ] },
\&      where => { \-op => [
\&          \*(Aqand\*(Aq, { \-op => [
\&              \*(Aq=\*(Aq, { \-ident => [ \*(Aqaccounts\*(Aq, \*(Aqname\*(Aq ] },
\&              { \-literal => [ "\*(AqAcme Corporation\*(Aq" ] },
\&          ] }, { \-op => [
\&              \*(Aq=\*(Aq, { \-ident => [ \*(Aqemployees\*(Aq, \*(Aqid\*(Aq ] },
\&              { \-ident => [ \*(Aqaccounts\*(Aq, \*(Aqsales_person\*(Aq ] },
\&          ] },
\&      ] },
\&  } }
\&
\&  # query
\&  UPDATE employees SET sales_count = sales_count + 1 FROM accounts
\&  WHERE (
\&    accounts.name = \*(AqAcme Corporation\*(Aq
\&    AND employees.id = accounts.sales_person
\&  )
\&  []
.Ve
.SS "delete using clause"
.IX Subsection "delete using clause"
Some databases allow an additional \s-1USING\s0 clause to reference other tables
for the data to update; this clause is expanded as a normal from list, check
your database for what is and isn't allowed in practice.
.PP
.Vb 6
\&  # expr
\&  { \-delete => {
\&      from => \*(Aqx\*(Aq,
\&      using => \*(Aqy\*(Aq,
\&      where => { \*(Aqx.id\*(Aq => { \-ident => \*(Aqy.x_id\*(Aq } },
\&  } }
\&
\&  # aqt
\&  { \-delete => {
\&      target => { \-op => [ \*(Aq,\*(Aq, { \-ident => [ \*(Aqx\*(Aq ] } ] },
\&      using => { \-ident => [ \*(Aqy\*(Aq ] },
\&      where => { \-op => [
\&          \*(Aq=\*(Aq, { \-ident => [ \*(Aqx\*(Aq, \*(Aqid\*(Aq ] },
\&          { \-ident => [ \*(Aqy\*(Aq, \*(Aqx_id\*(Aq ] },
\&      ] },
\&  } }
\&
\&  # query
\&  DELETE FROM x USING y WHERE x.id = y.x_id
\&  []
.Ve
.SS "insert rowvalues and select clauses"
.IX Subsection "insert rowvalues and select clauses"
rowvalues and select are shorthand for
.PP
.Vb 1
\&  { from => { \-select ... } }
.Ve
.PP
and
.PP
.Vb 1
\&  { from => { \-values ... } }
.Ve
.PP
respectively:
.PP
.Vb 4
\&  # expr
\&  { \-insert =>
\&      { into => \*(Aqnumbers\*(Aq, 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 => [ \*(Aqnumbers\*(Aq ] },
\&  } }
\&
\&  # query
\&  INSERT INTO numbers VALUES (?, ?), (?, ?), (?, ?)
\&  [ 1, 2, 3, 4, 5, 6 ]
\&
\&  # expr
\&  { \-insert =>
\&      { into => \*(Aqnumbers\*(Aq, select => { _ => \*(Aq*\*(Aq, from => \*(Aqold_numbers\*(Aq } }
\&  }
\&
\&  # aqt
\&  { \-insert => {
\&      from => { \-select => {
\&          from => { \-ident => [ \*(Aqold_numbers\*(Aq ] },
\&          select => { \-op => [ \*(Aq,\*(Aq, { \-ident => [ \*(Aq*\*(Aq ] } ] },
\&      } },
\&      target => { \-ident => [ \*(Aqnumbers\*(Aq ] },
\&  } }
\&
\&  # query
\&  INSERT INTO numbers SELECT * FROM old_numbers
\&  []
.Ve
.SS "with and with_recursive clauses"
.IX Subsection "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).
.PP
The value should be an arrayref of name/query pairs:
.PP
.Vb 6
\&  # expr
\&  { \-select => {
\&      from => \*(Aqfoo\*(Aq,
\&      select => \*(Aq*\*(Aq,
\&      with => [ \*(Aqfoo\*(Aq, { \-select => { select => \e1 } } ],
\&  } }
\&
\&  # aqt
\&  { \-select => {
\&      from => { \-ident => [ \*(Aqfoo\*(Aq ] },
\&      select => { \-op => [ \*(Aq,\*(Aq, { \-ident => [ \*(Aq*\*(Aq ] } ] },
\&      with => { queries => [ [
\&            { \-ident => [ \*(Aqfoo\*(Aq ] }, { \-select =>
\&                { select => { \-op => [ \*(Aq,\*(Aq, { \-literal => [ 1 ] } ] } }
\&            },
\&      ] ] },
\&  } }
\&
\&  # query
\&  WITH foo AS (SELECT 1) SELECT * FROM foo
\&  []
.Ve
.PP
A more complete example (designed for mariadb, (ab)using the fact that
mysqloids materialise subselects in \s-1FROM\s0 into an unindexed temp table to
circumvent the restriction that you can't select from the table you're
currently updating:
.PP
.Vb 10
\&  # expr
\&  { \-update => {
\&      _ => [
\&        \*(Aqtree_table\*(Aq, \-join => {
\&          as => \*(Aqtree\*(Aq,
\&          on => { \*(Aqtree.id\*(Aq => \*(Aqtree_with_path.id\*(Aq },
\&          to => { \-select => {
\&              from => \*(Aqtree_with_path\*(Aq,
\&              select => \*(Aq*\*(Aq,
\&              with_recursive => [
\&                [ \*(Aqtree_with_path\*(Aq, \*(Aqid\*(Aq, \*(Aqparent_id\*(Aq, \*(Aqpath\*(Aq ],
\&                { \-select => {
\&                    _ => [
\&                      \*(Aqid\*(Aq, \*(Aqparent_id\*(Aq, { \-as => [
\&                          { \-cast => { \-as => [ \*(Aqid\*(Aq, \*(Aqchar\*(Aq, 255 ] } },
\&                          \*(Aqpath\*(Aq,
\&                      ] } ],
\&                    from => \*(Aqtree_table\*(Aq,
\&                    union_all => { \-select => {
\&                        _ => [
\&                          \*(Aqt.id\*(Aq, \*(Aqt.parent_id\*(Aq, { \-as => [
\&                              { \-concat => [ \*(Aqr.path\*(Aq, \e"\*(Aq/\*(Aq", \*(Aqt.id\*(Aq ] },
\&                              \*(Aqpath\*(Aq,
\&                          ] },
\&                        ],
\&                        from => [
\&                          \*(Aqtree_table\*(Aq, \-as => \*(Aqt\*(Aq, \-join => {
\&                            as => \*(Aqr\*(Aq,
\&                            on => { \*(Aqt.parent_id\*(Aq => \*(Aqr.id\*(Aq },
\&                            to => \*(Aqtree_with_path\*(Aq,
\&                          },
\&                        ],
\&                    } },
\&                    where => { parent_id => undef },
\&                } },
\&              ],
\&          } },
\&        },
\&      ],
\&      set => { path => { \-ident => [ \*(Aqtree\*(Aq, \*(Aqpath\*(Aq ] } },
\&  } }
\&
\&  # 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, \*(Aq/\*(Aq, 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
\&  []
.Ve