How to fetch mutiple parameters from one query ?

[anayrat]

I try to do something like this : https://github.com/lovasoa/SQLpage/blob/main/examples/master-detail-forms/edit_user.sql

But I want to avoid sending a query for each parameter.
I wonder if we can run one query to fetch all parameters, then use them in the form later ?

SELECT * FROM "user" WHERE id = CAST($id AS INT)

SELECT 'form' AS component,
    'Edit user' AS title,
    'insert_user.sql' || COALESCE('?id=' || $id, '') AS action;

SELECT 'First name' AS name,
    TRUE AS required,
    :first_name  AS value;

SELECT 'Last name' AS name,
    TRUE AS required,
   :last_name AS value;

SELECT 'Email' AS name,
    'email' AS type,
    :email  AS value;

As a side note, it is not easy to understand how to pass parameters between pages or components :)

[lovasoa]

Simple select queries that are entirely static (= don't fetch data from the database) are resolved directly inside SQLPage, and not sent to your database, so you don't have to worry about reducing their number.

As a side note, it is not easy to understand how to pass parameters between pages or components :)

This feedback is very welcome. Could you give some insights about what you were trying to do, what you read in the docs, and what you would have expected instead?

[anayrat]

Simple select queries that are entirely static (= don't fetch data from the database) are resolved directly inside SQLPage, and not sent to your database, so you don't have to worry about reducing their number.

Wow, that's great ! I am still going to try to store information has a json object in one call. Code will be clearer.

As a side note, it is not easy to understand how to pass parameters between pages or components :)

This feedback is very welcome. Could you give some insights about what you were trying to do, what you read in the docs, and what you would have expected instead?

Here are some thought :

• Sometimes, I want to fetch information from the database in one page and reuse them in another page. I don't know how to do that without using forms ? I have to append all parameters in the url ?
• Another thing I want to do, when someone fills a form, I created a second step to validate the form. If the user goes back, he loses all the fields. I can use the value properties, but I don't know how to recover information.
• It is hard to understand differences between $variable and :variable. If I understand, behavior has changed recently. Information is spread in components documentation. Maybe a dedicated page would be better. I tried to follow examples, but I've seen different methods :
  • https://github.com/lovasoa/SQLpage/blob/9c47ec94cc909029853391a5c5d7b297530a18ef/examples/CRUD%20-%20Authentication/www/header_shell_session.sql#L63-L68
  • https://github.com/lovasoa/SQLpage/blob/9c47ec94cc909029853391a5c5d7b297530a18ef/examples/user-authentication/protected_page.sql#L1

[lovasoa]

• Sometimes, I want to fetch information from the database in one page and reuse them in another page. I don't know how to do that without using forms ? I have to append all parameters in the url ?

What I would do is just refetch it in the second page. Passing an id referencing data that is stored in the database. If the query to get the data is complex, create a view.

• Another thing I want to do, when someone fills a form, I created a second step to validate the form. If the user goes back, he loses all the fields. I can use the value properties, but I don't know how to recover information.

Usually browsers handle that natively when you press the back button.
Otherwise, you can always store unvalidated data separately in the db.

• It is hard to understand differences between $variable and :variable. If I understand, behavior has changed recently. Information is spread in components documentation. Maybe a dedicated page would be better. I tried to follow examples, but I've seen different methods :

In short: $variable for URL parameters and :variable for form fields.

[anayrat]

Here is the trick :

-- Store all column in a json

SET _user = (SELECT to_json(users.*) from users where username = $username);

-- access each column in the json
SELECT 
    'text' as component,
    ($_user)::json -> 'username' as contents;
SELECT 
    'text' as component,
    ($_user)::json -> 'first_name' as contents;

• I had to use _user because user is a reserved keyword.
• I don't know why I had to use $ instead of :, still a mystery for me :)

[lovasoa]

or you can do that with a single query with a cte at the beginning with the where condition and multiple UNION ALL for your fields.

[lovasoa]

For anyone stumbling upon this now, here is what I would suggest instead, which sends just one query to the database instead of three:

with u as (select * from users where username = $username)
SELECT 'text' as component, username as contents FROM u
UNION ALL
SELECT 'text', first_name;

If the different lines have different properties, you can use the dynamic component

with u as (select * from users where username = $username)
select 'dynamic' as component, json_build_array(
   json_build_object('name', 'First name', 'value', (select first_name from u)),
   json_build_object('name', 'bio', 'type': 'textarea', 'value', (select bio from u)),
) as properties;