The dynamic component

Renders other components, given their properties as JSON. If you are looking for a way to run FOR loops, to share similar code between pages of your site, or to render multiple components for every line returned by your SQL query, then this is the component to use

Top-level parameters

properties

A json object or array that contains the names and properties of other components.

JSON

Example 1

The dynamic component has a single top-level property named properties, but it can render any number of other components. Let's start with something simple to illustrate the logic. We'll render a text component with two row-level properties: contents and italics.

select 
    'dynamic' as component,
    '[{"component":"text"}, {"contents":"Hello, I am a dynamic component !", "italics":true}]' as properties;

Result

Hello, I am a dynamic component !

Examples

Static component data stored in `.json` files

You can also store the data for a component in a .json file, and load it using the dynamic component.

This is particularly useful to create a single shell defining the site's overall appearance and menus, and displaying it on all pages without duplicating its code.

The following will load the data for a shell component from a file named shell.json, using the sqlpage.read_file_as_text function.

SELECT 'dynamic' AS component, sqlpage.read_file_as_text('shell.json') AS properties;

and shell.json would be placed at the website's root and contain the following:

{
    "component": "shell",
    "title": "SQLPage documentation",
    "link": "/",
    "menu_item": [
        {"link": "index.sql", "title": "Home"},
        {"title": "Community", "submenu": [
            {"link": "blog.sql", "title": "Blog"},
            {"link": "https//github.com/lovasoa/sqlpage/issues", "title": "Issues"},
            {"link": "https//github.com/lovasoa/sqlpage/discussions", "title": "Discussions"},
            {"link": "https//github.com/lovasoa/sqlpage", "title": "Github"}
        ]}
    ]
}

Including another SQL file

To avoid repeating the same code on multiple pages, you can include another SQL file using the dynamic component together with the sqlpage.run_sql function.

For instance, the following will include the file shell.sql at the top of the page, and pass it a $title variable to display the page title.

SELECT 'dynamic' AS component,
       sqlpage.run_sql('shell.sql', json_object('title', 'SQLPage documentation')) AS properties;

And shell.sql could contain the following:

SELECT 'shell'     AS component,
    COALESCE($title, 'Default title') AS title,
    '/my_icon.png' AS icon,
    'products'     AS menu_item,
    'about'        AS menu_item;

Dynamic shell

On databases without a native JSON type (such as the default SQLite database), you can use the dynamic component to generate json data to pass to components that expect it.

This example generates a menu similar to the shell example, but without using a native JSON type.

SELECT 'dynamic' AS component, '
{
    "component": "shell",
    "title": "SQLPage documentation",
    "link": "/",
    "menu_item": [
        {"link": "index.sql", "title": "Home"},
        {"title": "Community", "submenu": [
            {"link": "blog.sql", "title": "Blog"},
            {"link": "https//github.com/lovasoa/sqlpage/issues", "title": "Issues"},
            {"link": "https//github.com/lovasoa/sqlpage/discussions", "title": "Discussions"},
            {"link": "https//github.com/lovasoa/sqlpage", "title": "Github"}
        ]}
    ]
}
' AS properties

View the result of this query, as well as an example of how to generate a dynamic menu based on the database contents.

Dynamic tables

The dynamic component can be used to generate tables with dynamic columns, using your database's JSON functions.

For instance, let's say we have a table with three columns: user_id, name, and role. We want to create a table where each row is a user, and each column is a role. We will return a set of json objects that look like this: {"name": "Alice", "admin": true, "editor": false, "viewer": true}

SELECT 'table' AS component;
SELECT 'dynamic' AS component, 
    json_patch(
        json_object('name', name),
        json_object_agg(role, is_admin)
    ) AS properties
FROM users
GROUP BY name;