Using lists as component parameters (to generate a dynamic navigation menu)

[gourk]

Hi,

I try to generate a menu from db on the shell component configuring it with a name and a link. For that, I override the shell component. I copied the shell.handlebars file and changed only this:

<ul class="navbar-nav ms-auto">
  {{#each (to_array menu_item) as |menu|}}
    <li class="nav-item">
      <a class="nav-link text-capitalize" href="{{menu.Menulink}}.sql">{{menu.Menuname}}</a>
    </li>
  {{/each}}
</ul>

I call the sell component with that query:

Select 'shell' as component,
    (
        Select a.Value
        FROM AppSettings a
        where a.Name = 'AssociationShortname'
    ) AS title,
    'Raleway' as font,
    'fr' as lang,
    'heart-filled' AS icon,
    '/home.sql' AS link,
    (
        select json_object(
                'menuname',
                Name,
                'menulink',
                Link
            )
        FROM (
                select m.Name,
                    m.Link,
                    DisplayOrder
                FROM MenuSettings m
                    JOIN GetUserFromSession s ON m.IsAdmin = s.IsAdmin
                WHERE Session = sqlpage.cookie('session')
                    AND m.IsActive = 1
                    AND m.IsAdmin = 1
                UNION
                SELECT Name,
                    Link,
                    DisplayOrder
                FROM MenuSettings
                WHERE IsActive = 1
                    AND IsAdmin = 0
                ORDER BY DisplayOrder
            )
    ) as menu_item;

The shell component is well displayed but without any menu. I tried to send a JSON array to handlebars or to change the template but nothing worked. I didn't check the rust code. It may not be possible to achieve what I want. I don't know. Am I doing something wrong?

[lovasoa]

What you are trying to do is definitely possible !

Excuse me for the long answer, but I will try to make it as generally useful to others as possible.

There are two points in your question:

• How to pass a list as a parameter to a component ?
• How to use the list from your custom component template ?

How to pass a list as a parameter to a component ?

• The first solution is to return multiple columns with the same name from your query.
  • SELECT 1 AS x, 2 AS x will pass { "x": [1,2] } to your template
  • This works well when the number of items in the array does not depend on the database; when it is fixed and known in advance.
  • That is not what you need in your case, because it looks like you have a dynamic number of menu items that come from your database.
• The second solution is to return your database's native json array type from your query.
  • This works only for databases that have a native json type (not SQLite or Microsoft SQL Server)
  • In postgres, the name of the function to create a json array from records is json_agg. If multiple elements come from the database, you can use :

    •  SELECT json_agg(my_items.item) AS menu_item FROM my_items

• If your list size is not fixed and your database does not have a native json type (you are using SQLite or Microsoft SQL Server)
  • You can return a simple string containing your json array, but you will have to parse that string from the component's template

    •  SELECT json_group_array(my_items.item) AS menu_item FROM my_items; -- in SQLite, json_group_array returns a string

    • {{#each (parse_json menu_item) }}
             Litst item: {{ this }}
      {{/each}}

How to use the list from your custom component template ?

If your list contains primitive values (strings, numbers)

  {{#each my_list}}
    list item: {{ this }}
  {{/each}}

Note that you don't have to use to_array.
The to_array function allows transforming a single value to an array, you need it only if my_list can be either an array or a primitive value.

• to_array(123) -> [123]
• to_array([123, 456]) -> [123, 456]

If your list contains objects, and you want to extract the values of the object

SELECT json_agg(json_object('firstName', first_name, 'lastName', last_name)) AS my_list FROM my_users;

  {{#each my_list}}
    {{ firstName }} {{ lastName }}
  {{/each}}

And when you are lost, don't forget that you can use the debug component to see exactly what data is passed to your templates.

[lovasoa]

I updated the user authentication example to demonstrate dynamic menu items that depend on the logged in state.

[gourk]

Thank you so much for your very complete answer and the example. It works perfectly now. You are a star!

[lovasoa]

SQLPage v0.9.5 was just released with improved support for JSON. Notably, you can now use the more efficient JSONB type if you are using postgres.