Regression with 'add an null value as an option in a select '

[DSMejantel]

Introduction

The question was solved first in discussion #95 All was fine with SQLpage 0.17.1 but with 0.18.2 we can't see the label for the Null Value in the select list anymore.

Screenshots

Expected behavior

With 0.17.1

Actual behavior

with 0.18.2

    SELECT 'souche' AS name, 'select' as type, 4 as width,
    json_group_array(json_object("label" , numero, "value", numero )) as options FROM (
  SELECT numero, numero FROM colonie
  UNION ALL
  SELECT NULL, 'Inconnue' );

Version information

• OS: Ubuntu
• Database Sqlite
• SQLPage Version : 0.18.2

[lovasoa]

How could it know which "numero" is the label and which is the value? You need a different name for the label and the value.

SELECT 'souche' AS name, 'select' as type, 4 as width,
    json_group_array(
        json_object("label" , label, "value", numero )
    ) as options
FROM (
  SELECT numero, numero as label FROM colonie
  UNION ALL
  SELECT NULL, 'Inconnue'
);

[DSMejantel]

Ohh I understand. Another argument to keep a separate id and number for the beehive, maybe ?

[lovasoa]

If the "Numero" is unique, then it's ok to use it as id. The problem here is that you need to give a different column name to NULL and to "Inconnue".

[DSMejantel]

Yes with Numero and a 'Unique' contrainst, the list select is OK. I find this soluce to have a good display and manage more information with another table : SELECT 'souche' AS name, 'select' as type, 4 as width, json_group_array(json_object("label" , numero, "value", numero )) as options FROM ( SELECT numero, numero FROM colonie UNION ALL SELECT origine, NULL FROM provenance ORDER BY origine asc )

[lovasoa]

You still have the problematic json_object("label" , numero, "value", numero You cannot give two different columns the same name and then distinguish between them.

[DSMejantel]

But I think it's working. Am I wrong ?

[lovasoa]

The thing is

select json_group_array(json_object("label" , numero, "value", numero )) as options
from anything

Cannot produce a json array containing something like {"label": "inconnue", "value": null } which is probably what you wanted. As suggested above, you need something like

SELECT json_group_array(json_object('label' , label, 'value', value)) as options
FROM something

where something has a label and a value column. For instance:

SELECT json_group_array(json_object('label' , label, 'value', value)) as options
FROM (
   select my_id as label, my_id as value from my_table
   union all
   select 'Other' as label, NULL as value
)

To explain each part of the query:

select my_id as label, my_id as value from my_table will give

label	value
1	1

select my_id as label, my_id as value from my_table union all select 'Other' as label, NULL as value will then give

label	value
1	1
Other	NULL

And the complete query will give

options
[ {"label": 1, "value": 1 }, {"label": "Other", "value": null } ]

which is probably what you needed

[DSMejantel]

I tried

`json_group_array(json_object("label" , label, "value", value)) as options
FROM (
   select numero as label, numero as value from colonie
   union all
   select 'Other' as label, NULL as value
)

and

json_group_array(json_object("label" , label, "value", value )) as options FROM (
  SELECT numero as label, numero as value FROM colonie
  UNION ALL
  SELECT origine as label, NULL as value FROM provenance ORDER BY origine asc
)

But i receive this error :

error returned from database: (code: 1) json_object() labels must be TEXT
(code: 1) json_object() labels must be TEXT

[lovasoa]

Oh sorry, I messed up the quotes above. It's json_object('label', label, 'value', value) not json_object("label", label, "value", value)

Single quotes are for Strings Literals, Double quotes are for Database Identifiers.

[DSMejantel]

Thanks. All is now ok and work properly.

SELECT 'souche' AS name, 'select' as type, 4 as width,
    json_group_array(json_object('label' , label, 'value', value )) as options FROM (
  SELECT 'colonie n°'||numero as label, numero as value FROM colonie
  UNION ALL
  SELECT origine as label, NULL as value FROM provenance ORDER BY origine asc
)