home-launcher/util-sqlpage/code/form.sql

SET ":filter_options" = (
  SELECT json_group_array(json_object('name', q.k, 'options', q.o))
  FROM (
    SELECT options.k, jsonb_group_array(
      jsonb_object('label', v, 'value', v, 'selected', s OR CASE :autofill WHEN TRUE THEN c = 1 ELSE FALSE END)
      ORDER BY v) o
    FROM (
      SELECT DISTINCT k, v, s, count(v) OVER (
        PARTITION BY k ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
      ) c FROM (
        SELECT 'store'||'[]' k, value v, TRUE s FROM (SELECT value FROM json_each($store))
        UNION
        SELECT 'store'||'[]', store, NULL s FROM code_detail
        UNION
        SELECT 'type'||'[]' k, value v, TRUE s FROM (SELECT value FROM json_each($type))
        UNION
        SELECT 'type'||'[]', type, NULL s FROM code_detail
        UNION
        SELECT 'value', value, CASE COALESCE($value, '') WHEN '' THEN FALSE ELSE value = :value END s FROM code_detail
      )
    ) options
    WHERE v IS NOT NULL
    GROUP BY options.k
  ) q
);

SELECT 'form' AS component
, '/code/' AS action
, :validate AS validate
, :tabler_color AS validate_color
, :method AS method
;
SELECT COALESCE(c.v->>'name', j.v->>'name') AS name
--, fo.j#>>'{options}' AS label
, COALESCE(c.v->>'label', j.v->>'label') AS label
, COALESCE(c.v->>'type', j.v->>'type') AS type
, COALESCE(c.v->>'dropdown', j.v->>'dropdown') AS dropdown
, COALESCE(c.v->>'create_new', j.v->>'create_new') AS create_new
, COALESCE(c.v->>'multiple', j.v->>'multiple') AS multiple
, COALESCE(c.v->>'placeholder', j.v->>'placeholder') AS placeholder
, COALESCE(c.v->>'width', j.v->>'width') AS width
, o.j->>'options' AS options
, COALESCE(c.v->>'prefix', j.v->>'prefix') AS prefix
, COALESCE(c.v->>'value', j.v->>'value', v.v) AS value
, CASE COALESCE(c.v->>'type', j.v->>'type')
    WHEN 'checkbox' THEN COALESCE(c.v->>'checked', v.v) = 'true'
    WHEN 'radio' THEN COALESCE(c.v->>'checked', v.v) = 'true'
    ELSE NULL
  END AS checked
, j.v->>'formaction'
, COALESCE(c.v->>'required', j.v->>'required') AS required
FROM (SELECT value v FROM json_each(sqlpage.read_file_as_text('code/json/filters.json'))) j
FULL JOIN (SELECT value v FROM json_each(:filter_config)) c
ON (j.v->>'name' = c.v->>'name')
LEFT JOIN (SELECT value j FROM json_each(:filter_options)) o
ON (o.j->>'name') = j.v->>'name'
LEFT JOIN (SELECT "key" k, value v FROM json_each(sqlpage.variables())) v
ON v.k = j.v->>'name' OR v.k = c.v->>'name'
;