Nested navigation from SQL

[hfjn]

Hi! Thank you for this awesome piece of software. Really enjoy using it. I'm currently trying to build a filter logic for a table which will be my main data view. As I have a data structure which is nested up to 4 levels deep and has a couple hundreds of elements in total I'd love to automate the generation of a tree view which then allows me to select single nodes and pass them as parameter to my WHERE clause.

My current idea was to reuse the Tabler navigation structure, use the horizontal instead of the boxed layout and a bit of logic in a custom shell module to build my tree, so basically like this

<ul class="navbar-nav ms-auto">
{{#foreach}}
    <li class="nav-item">
        <a class="nav-link text-capitalize" href="?filter{{this}}">{{this}}</a>
    </li>
{{/foreach}}
</ul>

I'm struggling though to make the shell template accept results from a sql query, similar to how the list or table modules do. Any pointer how I could achieve that? Much appreciated!

[lovasoa]

Hello and welcome to SQLPage !

I am not sure I understand exactly what you are trying to achieve. Are you trying to replicate these website navigation menus, but want to populate them with data coming from your database ?

There can be only a single shell component template for a given SQLPage website, and you can overwrite the default one by creating a file under ./sqlpage/templates/shell.handlebars in your website. You can make your custom shell accept any number of arguments coming from your database.

If you want to use a complex structure (for instance an array of objects with sub arrays), the easiest is to pass it as json from the database. If your database does not have a native json object type (sqlite uses text strings to manipulate json, for instance) then you can parse a text string as json from the template using the parse_json handlebars helper :

<ul class="navbar-nav ms-auto">
{{#each (parse_json menu_items)}}
    <li class="nav-item">
        <a class="nav-link text-capitalize" href="?filter={{filter}}">{{title}}</a>
    </li>
{{/each}}
</ul>

and then, in your SQL

select 'shell' as component,
  '[
    {"filter": "filter1", "title": "My Title"}
  ]' as menu_items;

(in practice, you would use your database's native json functions to create the json object)

[lovasoa]

I published a small example on replit, tell me if this is what you were looking for: https://replit.com/@pimaj62145/SQLPage-custom-menu-example?v=1

The important parts are:

-- in index.sql
select 'shell' as component,
  'TODO' as title,
  (select json_group_array(
      json_object(
          'filter', filter,
          'title', title
      )
  ) from menu_filters) as menu_item;

in ./sqlpage/templates/shell.handlebars:

<ul class="navbar-nav ms-auto">
    {{#each (parse_json menu_item)}}
        <li class="nav-item">
            <a class="nav-link text-capitalize" href="?filter={{filter}}">{{title}}</a>
        </li>
    {{/each}}
</ul>

[lovasoa]

I added a documentation page about writing custom components: https://sql.ophir.dev/custom_components.sql

Let me know if it can be improved.

[lovasoa]

I added custom links and submenus to v0.15.2, which I'll release this weekend.

https://sql.ophir.dev/documentation.sql?component=shell#component

Open to feedback, @hfjn :)