A few questions about multi-select form fields

[monsieurmerle]

Hi everyone,

Thanks to the documentation, I have been able to use the option_source example given in the form component page, but I can't get it to make more complex queries.

I would like to be able to restrict the amount of options using a condition, as displayed in the example below:

My table :

create table if not exists  tu_to_gb (
    id integer primary key autoincrement,
    id_gearbox,
    id_worker integer
   );

The form itself. In it, I have defined $id_user as the id of the current user according to the session cookie. I know $id_user is not the source of the issue because I use it in many different other pages with no issues.

select 
    'form' as component,
    'Enregistrer' as validate;
select
    'gearbox' as name,
    'select'    as type,
    'form'      as value,
    '/options/gearbox?id='||$id_user as options_source,
    "Renseigner un numéro de série" as description;

The options source options/gearbox.sql

select 'json' as component;

select id_gearbox as value, id_gearbox as label
from tu_to_gb 
where id_worker=$id
and label like $search || '%';

The issue being that when I try to type in the form field, nothing is displayed, where I do know for a fact that my tu_to_gb table is not empty, and has rows with the current $id_user in them. Currently, the id_gearbox are all strings, named red1, red2, red3, etc...

If i remove the id_worker=$id condition and change the options_source URL from '/options/gearbox?id='||$id_user to '/options/gearbox' , I now can start typing in the field and options are displayed, but from the entire table.

I would like to be able to have a condition in the options_source to only display the id_gearbox where id_worker is the one I want.

[darekwojciechowski]

The ?id= is what breaks it. SQLPage appends ?search=... to whatever string you put in options_source, so the URL it actually fetches is:

/options/gearbox?id=3?search=red

That's one parameter, id, holding the value 3?search=red. There's no search parameter at all, so $search is NULL, label like NULL is NULL, and you get zero rows. Open that URL in your browser and you'll see the empty array come back.

So do the lookup inside gearbox.sql instead of passing it in. Set $id_user from the session cookie there, same as you do on the form page:

select 'json' as component;

select id_gearbox as value, id_gearbox as label
from tu_to_gb
where id_worker = $id_user
  and id_gearbox like coalesce($search, '') || '%';

Then the form just points at the bare path:

'/options/gearbox' as options_source

Keep the coalesce. $search is NULL before the user types anything.

[monsieurmerle]

Thank you for the answer !

I had figured the issue probably came from the ?id= part but with no idea of why. I now have to implement the changes...

Edit : It now works as intended, many thanks :)

[lovasoa]

Thank you for the report @monsieurmerle , and thank you for the investigation @darekwojciechowski !

I opened a PR to fix the issue. '/options/gearbox?id='||$id_user as options_source will work natively starting from the next release.

#1356