Does SQLpage support caching of CTE data for forms ?

[chris-bragg-sift]

Example rather than making a call for each form input is there a way of making one call storing the data than accessing that for form elements ? maybe I am missing something out as I am using a CTE query per input as I want to populate with existing values.

set cache_result=( 'with prep as ( SELECT ... )' )

then

select 
'text' as type,
'existing_name' as name,
data.value as value
from $cache_result as data

etc
etc

[lovasoa]

Hey !
If I got it right, what you want is create a form that can be used to edit a record in a database table. You want each column in the table to be represented by a field in the form, and each field to be pre-filled with the current value of the column.

The simplest way to do that is to just make one query per form field, which is fine most of the time:

select 'form' as component;
select 'username' as name, username as value from users where user_id = $user_id;
select 'email'    as name, email    as value from users where user_id = $user_id;

But it looks like you would like to avoid making one query per form field.
This is definitely possible, and does not need any special SQLPage feature, you can do that in pure SQL:

select 'form' as component;
with user as (select * from users where user_id = $user_id),
fields as (
  select 'username' as name, username as value from user
  union all
  select 'email', email from user
)
select name, value, name as placeholder, true as required
from fields;

(try it on db-fiddle)

[chris-bragg-sift]

Thanks, of course UNION, I imagine it is possible to create "select" types as well by creating a json() name, value pair within the SQL too.
I found making complex queries multiple times for the same form was starting to slow everything down.

[lovasoa]

Yes, you can include any of the supported attributes. For instance:

with user as (select * from users where user_id = 1),
fields as (
  select 'text' as type, 'username' as name, username as value, null as options from user
  union all
  select 'email', 'email', email, null from user
  union all
  select 'select', 'country', country, (select json_group_array(json_object('label', country, 'value', country)) from countries) from user
)
select
	name, value, name as placeholder, true as required, options
from fields;

https://www.db-fiddle.com/f/pH1pEjnEDuJJXKv9Roxvw3/0

[chris-bragg-sift]

And for postgresql users out there, a few things took me a while to work out, here is the equivalent:

with user as (select * from users where user_id = 1),
fields as (
  select 'text' as type, 'username' as name, username as value, '4' as width, CAST(NULL AS json) as options from user
  union all
  select 'email', 'email', email as value, '4' as width, CAST(NULL AS json) as options from user
  union all
  select 'select', 'country', country, '4' as width, (select JSON_AGG(json_build_object('label', country, 'value', country)) from countries) as options from user
)
select type, name, value, width, options from fields;

Many thanks again @lovasoa