form / how to define options in a subquery based on a previous selection ?

[SebastiendOrnano]

I am not familiar with subquery and CTE... so I am stuck on a simple issue :

• 1 table "members"
• 1 table "orga"
• 1 table "orga_dept" (departements of orga)
  in a sqlite database

When creating a new member, I can chose the "orga" in a list of options. It works fine.

But I can't formulate correctly the next list of options to chose the dept within the orga selected :
(SELECT json_group_array(json_object('label', orga_dept_name, 'value', orga_dept_id)))AS options FROM orga_dept WHERE orga_id = :orga_name;

The list of options is empty
(and if I suppress the () around the SELECT I have a parsing error).
I tried some queries with IN condition but no success.

Any help is welcome and thanks in advance !

CREATE TABLE "members" (
	"member_id"	INTEGER,
    "member_last_name" as TEXT,
	"orga_id"	INTEGER,
	"orga_dept_id"	INTEGER,
	PRIMARY KEY("member_id")
)

CREATE TABLE "orga" (
	"orga_id"	INTEGER,
	"orga_name"	TEXT NOT NULL,
	PRIMARY KEY("orga_id")
)

CREATE TABLE "orga_dept" (
	"orga_dept_id"	INTEGER,
	"orga_dept_name"	TEXT,
	"orga_id"	INTEGER,
	PRIMARY KEY("orga_dept_id")
)

SELECT 
    'debug' as component,
    'Create a member' as title,
    'Create' as validate,
    'member_create.sql' as action;

SELECT
'member_last_name' as name,
'text' as type,  
'Nom' as label,
TRUE as required,
6 AS width,
'Nom de famille de l''adhérent' AS placeholder;

select 
   'orga_name' as name,
    'Organisation' as label,
    'select' as type,
    json_group_array(json_object('label', orga_name,'value', orga_id)) as options FROM orga;

select 
   'orga_dept_name' as name,
    'Departement' as label,
    'select' as type,
    'Département' as label,
    (SELECT json_group_array(json_object('label', orga_dept_name, 'value', orga_dept_id)))AS options FROM orga_dept WHERE orga_id = :orga_name;

    */ parsing error on  SELECT json_group_array(json_object('label', orga_dept_name, 'value', orga_dept_id)) AS options FROM orga_dept WHERE orga_id = :orga_name; */`
```

[lovasoa]

It looks like you need a multi-step form. You cannot generate the list of departments until the user has validated the organization.

https://github.com/sqlpage/SQLPage/tree/main/examples/forms-with-multiple-steps

Then you can use a simple query without any subquery:

select 
   'orga_dept_name' as name,
    'Departement' as label,
    'select' as type,
    'Département' as label,
    json_group_array(json_object(
      'label', orga_dept_name, 
      'value', orga_dept_id
    )) AS options
FROM orga_dept
WHERE orga_id = :orga_name;

[SebastiendOrnano]

Fine ! As always, better to keep the code simple in several steps rather to want a complex code in one file.

Thanks again

[YunusMechery]

Try
#628