Align "As" within a JOIN

[arichiardi]

Hi there, wonderful package, I really like configuration, it reminds me of how configurable formatters can be.

One thing I am trying to achieve is to change this:

 JOIN LATERAL jsonb_to_recordset(jsonb_path_query_array(reports.tree, 'strict $.children.report.children.images.children.*'))
 AS image_descriptors("name" text, "order" text, "descriptor" jsonb)

to

JOIN LATERAL jsonb_to_recordset(jsonb_path_query_array(reports.tree, 'strict $.children.report.children.images.children.*'))
             AS image_descriptors("name" text, "order" text, "descriptor" jsonb)

It makes more sense, at least to me, to see that the AS belongs to the jsonb_to_recordset above.

Not a big deal but I could not figure out a way - I am also a sqlind newbie to be honest :smile:

Thanks a lot in advance for your help!

[alex-hhh]

Hi @arichiardi , thanks for reporting this. sql-indent would not detect correctly the various tables and table continuations inside the "from" section of a query, so the "AS" keyword would not be detected correctly.

I pushed a fix to the following branch https://github.com/alex-hhh/emacs-sql-indent/tree/ah/pr99, however, while the changes are relatively small, a lot of the tests had to change since the "from" section of queries is now classified differently. I will need to take another look at this branch before I merge it, but you can try it out.

If you decide to try it out, here are a few things to keep in mind:

• the example you gave in this issue is incomplete, and the "AS" line will be detected as a statement continuation (since this is the closest to what that piece of code is), you will need a full select statement to try it out
• in a correct SQL statement, the "AS" line will be detected as a select-table-continuation with the anchor being the position of the table (here "table" means anything table-like in the from section). you can adjust the indentation of this element as you wish, this is explained in the customization section of the "sql-indent.org" document, if you use "0" for it, it will line up with the table itself (by default it is "+" which indents it one block)

[arichiardi]

Hey @alex-hhh I am definitely going to try this thank you!

For completeness, this is the query:

DELECT reports.report_version_id,
       image_descriptors.order,
       image_descriptors.descriptor,
       files.file_name as name,
       files.file_id as id,
       files.content_type
  FROM report_versions as reports,
       files
  CROSS JOIN LATERAL jsonb_to_recordset(jsonb_path_query_array(reports.tree, 'strict $.children.report.children.images.children.*'))
  AS image_descriptors("name" text, "order" text, "descriptor" jsonb)
 WHERE reports.report_version_id = files.report_version_id
   AND files.file_id = image_descriptors.NAME

The above is indented with the following:

(setq sqlind-indentation-offsets-alist
                                     `((select-clause sqlind-right-justify-clause)
                                       (insert-clause 0)
                                       (delete-clause 0)
                                       (update-clause 0)
                                       (select-table-continuation sqlind-lineup-joins-to-anchor)
                                       ,@sqlind-default-indentation-offsets-alist))

[arichiardi]

Ok I have tried your branch and it almost works as expected - the only thing is what you see below between FROM and JOIN

SELECT reports.diagnostic_report_version_id,
       image_descriptors.order,
       image_descriptors.descriptor,
       files.file_name as name,
       files.file_id as id,
       files.content_type
  FROM diagnostic_report_versions as reports,
       files
       JOIN LATERAL jsonb_to_recordset(jsonb_path_query_array(reports.tree, 'strict $.children.report.children.images.children.*'))
                    AS image_descriptors("name" text, "order" text, "descriptor" jsonb)
 WHERE reports.diagnostic_report_version_id = files.diagnostic_report_version_id
   AND files.file_id = image_descriptors.NAME

The config

(use-package sql-indent
  :hook (sqlind-minor-mode . (lambda ()
                               (setq sqlind-indentation-offsets-alist
                                     `((select-clause sqlind-right-justify-clause)
                                       (insert-clause 0)
                                       (delete-clause 0)
                                       (update-clause 0)
                                       (select-table-continuation 0)
                                       ,@sqlind-default-indentation-offsets-alist)))))

[alex-hhh]

The "JOIN LATERAL" line is detected as a select-table syntax, since it starts a new table in the from section. You will need to update the select-table indentation with a custom indenter. Note that the indenters can be chained, so, if the indenter does not apply, it can simply return its input indentation:

;; NOTE: this is meant to be chained onto another indenter, if the line does
;; not start with a join keyword, it just returns BASE-INDENTATION
(defun pr99-adjust-join (syntax base-indentation)
  (save-excursion
    (back-to-indentation)
    (if (or (looking-at "join\\_>")
            (looking-at sqlind-select-join-regexp))
        (sqlind-use-anchor-indentation syntax base-indentation)
      base-indentation)))

(use-package
 sql-indent
 :hook (sqlind-minor-mode
        .
        (lambda ()
          (setq sqlind-indentation-offsets-alist
                `((select-clause sqlind-right-justify-clause)
                  (insert-clause 0)
                  (delete-clause 0)
                  (update-clause 0)
                  (select-table sqlind-indent-select-table pr99-adjust-join)
                  (select-table-continuation 0)
                  ,@sqlind-default-indentation-offsets-alist)))))

[alex-hhh]

I merged the changes so indentation of "JOIN" statements can be customized now, and also showed an example of how to do it in the previous comment.

[arichiardi]

Awesome, will definitely try it out soon!