cascading select (a form with multiple fields that depend on each other)

[flosigime]

Hello everyone,

In a form, i have to select with options.
The first select lists all the table of our database

SELECT 
    'Table' as label,  
    'thes_table' as name,
    'select' as type,
     '[' || group_concat(json_object("label", nom_table, "value", nom_table), ', ') || ', null]' as options 
     FROM (SELECT name as nom_table FROM sqlite_master WHERE type='table');

I want that the second select to list only the field of the table selected in the first select.

I will certainly use javascript to do that, and probably compare the first select to a json array for example.
I could write this array in my code but i prefer using an sql query.
But i don't really understand how to do with the json component. Maybe with the dynamic ?

Thank you for your help

[lovasoa]

You could do that entirely in sql with a two-step form. A first page lets the user select a table, the user validates the selection, and is directed to a second form that lists the columns in the previously selected table.

Out of curiosity, in what context do you need this ?

[lovasoa]

Here is a small example

select_table.sql

SELECT
  'form' AS component,
  'Step 1/2: select a table' AS title,
  'select_column.sql' AS action;
SELECT 'table' AS name, TRUE AS required, 'select' as type,
  json_group_array(json_object(
    "label", name,
    "value", name
  )) as options
from pragma_table_list()
where name not like 'sqlite_%' and name != '_sqlx_migrations';

select_column.sql

SELECT
  'form' AS component,
  'Step 2/2: select a column in table ' || $table AS title,
  'finalize.sql' AS action;
SELECT 'column' AS name, TRUE AS required, 'select' as type,
  json_group_array(json_object(
    "label", name,
    "value", name
  )) as options
from pragma_table_info($table);
select 'table' as name, 'hidden' as type, $table as value;

https://replit.com/@pimaj62145/SQLPage-2-step-form#index.sql

[lovasoa]

Or, on a single page:

SELECT 'form' AS component,
  case when $table is null then '' else 'finalize.sql' end as action;

SELECT 'table' AS name, TRUE AS required, 'select' as type, $table as value,
  json_group_array(json_object(
    "label", name,
    "value", name
  )) as options
from pragma_table_list()
where name not like 'sqlite_%' and name != '_sqlx_migrations';

SELECT 'column' AS name, TRUE AS required, 'select' as type,
  json_group_array(json_object(
    "label", name,
    "value", name
  )) as options
from pragma_table_info($table)
having $table is not null;

[flosigime]

Thank you very much Ophir, For the database, we have use some specific terms. So we build a thesaurus. This thesaurus is a table with a "table_name" column, with the name of the table we use, a "field" column, with the name of the field il will be useful for, and a term column for the term itself. So, thanks to this, we can prepare all the different terms we can use for specific field in specific table. Our thesaurus contains 406 terms (datation, type of objects, type of archeological fact, context, type of layer, etc..). But, between archeologists, if we have a basic common vocabulary, some people want to use other terms, or don't use some terms, etc. So we have to let people change the thesaurus. And for that, we created a form to modify terms or to create new terms. And when you want to create a new term, you have to know where this term will be useful : in which table, and in which field. We have 70 tables... Le sam. 23 mars 2024 à 17:14, Ophir LOJKINE ***@***.***> a écrit :

…

Or, on a single page: SELECT 'form' AS component, case when $table is null then '' else 'finalize.sql' end as action; SELECT 'table' AS name, TRUE AS required, 'select' as type, $table as value, json_group_array(json_object( "label", name, "value", name )) as optionsfrom pragma_table_list()where name not like 'sqlite_%' and name != '_sqlx_migrations'; SELECT 'column' AS name, TRUE AS required, 'select' as type, json_group_array(json_object( "label", name, "value", name )) as optionsfrom pragma_table_info($table)having $table is not null; — Reply to this email directly, view it on GitHub <#273 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AOYEXY43S3ZS4NTFTUOFKPTYZWS6DAVCNFSM6AAAAABFERU75CVHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM4DQOBXHEZDS> . You are receiving this because you authored the thread.Message ID: ***@***.***>

-- ------------------------------------------------------ *Florent Mercey * *- Responsable d'opération Protohistoire, Etudes céramique Bronze/Hallstattt* *- Référent SIG, Référent NAS, collectif ramen ***@***.***> (Recherches Archéologiques en Modélisation de l'Enregistrement Numérique)* Inrap - Orléans 525 avenue de la Pomme de Pin 45590 Saint Cyr en Val tel : 02 38 69 96 58 ; port : 06 08 90 00 62 http://www.inrap.fr

[flosigime]

i think it will be very useful tio document the use of $table here in your exemple. As i understand, It means we can use a variable in the sql code, variable we can define elsewhere, in the page, ot via $POST or $GET. It's really precious. Le sam. 23 mars 2024 à 17:41, florent mercey ***@***.***> a écrit :

…

Thank you very much Ophir, For the database, we have use some specific terms. So we build a thesaurus. This thesaurus is a table with a "table_name" column, with the name of the table we use, a "field" column, with the name of the field il will be useful for, and a term column for the term itself. So, thanks to this, we can prepare all the different terms we can use for specific field in specific table. Our thesaurus contains 406 terms (datation, type of objects, type of archeological fact, context, type of layer, etc..). But, between archeologists, if we have a basic common vocabulary, some people want to use other terms, or don't use some terms, etc. So we have to let people change the thesaurus. And for that, we created a form to modify terms or to create new terms. And when you want to create a new term, you have to know where this term will be useful : in which table, and in which field. We have 70 tables... Le sam. 23 mars 2024 à 17:14, Ophir LOJKINE ***@***.***> a écrit : > Or, on a single page: > > SELECT 'form' AS component, > case when $table is null then '' else 'finalize.sql' end as action; > SELECT 'table' AS name, TRUE AS required, 'select' as type, $table as value, > json_group_array(json_object( > "label", name, > "value", name > )) as optionsfrom pragma_table_list()where name not like 'sqlite_%' and name != '_sqlx_migrations'; > SELECT 'column' AS name, TRUE AS required, 'select' as type, > json_group_array(json_object( > "label", name, > "value", name > )) as optionsfrom pragma_table_info($table)having $table is not null; > > — > Reply to this email directly, view it on GitHub > <#273 (reply in thread)>, > or unsubscribe > <https://github.com/notifications/unsubscribe-auth/AOYEXY43S3ZS4NTFTUOFKPTYZWS6DAVCNFSM6AAAAABFERU75CVHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM4DQOBXHEZDS> > . > You are receiving this because you authored the thread.Message ID: > ***@***.***> > -- ------------------------------------------------------ *Florent Mercey * *- Responsable d'opération Protohistoire, Etudes céramique Bronze/Hallstattt* *- Référent SIG, Référent NAS, collectif ramen ***@***.***> (Recherches Archéologiques en Modélisation de l'Enregistrement Numérique)* Inrap - Orléans 525 avenue de la Pomme de Pin 45590 Saint Cyr en Val tel : 02 38 69 96 58 ; port : 06 08 90 00 62 http://www.inrap.fr

-- ------------------------------------------------------ *Florent Mercey * *- Responsable d'opération Protohistoire, Etudes céramique Bronze/Hallstattt* *- Référent SIG, Référent NAS, collectif ramen ***@***.***> (Recherches Archéologiques en Modélisation de l'Enregistrement Numérique)* Inrap - Orléans 525 avenue de la Pomme de Pin 45590 Saint Cyr en Val tel : 02 38 69 96 58 ; port : 06 08 90 00 62 http://www.inrap.fr

[lovasoa]

In the example, $table could be named differently; it's just the value the user selected in the first form for the field named table.

[lovasoa]

[flosigime]

Yes, important to understand that $table is the name of the first select is there a way to purge $table ? Le sam. 23 mars 2024 à 19:34, Ophir LOJKINE ***@***.***> a écrit :

…

Screenshot_20240323-193357_Slides.png (view on web) <https://github.com/lovasoa/SQLpage/assets/552629/aaa802d6-b6fd-45a7-a652-c40c94d7090b> — Reply to this email directly, view it on GitHub <#273 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AOYEXY37EK3GSCWRKX77BVTYZXDL7AVCNFSM6AAAAABFERU75CVHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM4DQOBYGY2TS> . You are receiving this because you authored the thread.Message ID: ***@***.***>

-- ------------------------------------------------------ *Florent Mercey * *- Responsable d'opération Protohistoire, Etudes céramique Bronze/Hallstattt* *- Référent SIG, Référent NAS, collectif ramen ***@***.***> (Recherches Archéologiques en Modélisation de l'Enregistrement Numérique)* Inrap - Orléans 525 avenue de la Pomme de Pin 45590 Saint Cyr en Val tel : 02 38 69 96 58 ; port : 06 08 90 00 62 http://www.inrap.fr

[lovasoa]

If you redirect the user to the first form (via a simple button or link), the value of $table will be null again.