problem in editing postgis layer with value relation fields

[paolo73an]

What is the bug?

Editing ticked value relation checkboxes, no ticks appear even if data is already stored

Steps to reproduce the behavior

After adding new geometries with multiple data inserted with value relation widget, the string data is correctly written with LWC in a postgis db field in the form {data1,data2,...} but soon after if I try to edit the same layer in LWC no ticks in checkboxes related to data1,data2,etc..appear. If I insert new geometries with qfield app with the same qgs project each string data is written in the postgis db in the form {"data1","data2",...} but editing with LWC no ticks in checkboxes appear either, even if data is stored in db. I'm using a postgis table with value/description data used in the relation value widget. Any hint to solve this issue?

Expected behavior

I'd expect to get ticked checkboxes in editing already stored multiple string data

Screenshots and logs

Adding new:

Editing same geometry/db record:

Environment

Lizmap stable release 3.3.4 (same issue with 3.4.0-pre 190808), latest qgis and qgis server, postgresql 12 with postgis 3, QGIS Project version: 3.10.2, linux ubuntu 18.04.3, Browser (Firefox, Chrome…)

[paolo73an]

I've just focused that after issue #1453 was closed I upgraded postgresql version to 12 (from v.11). So I'm fairly sure it could be a compatibility issue between postgresql 12 and LWC. I'll try reverting back to postgresql version 11 and let you know...

[paolo73an]

Nope. It's not a matter of PG version. Now I've installed PG11 with PostGis 3:

POSTGIS="3.0.0 r17983" [EXTENSION] PGSQL="110" GEOS="3.7.1-CAPI-1.11.1 27a5e771" SFCGAL="1.3.5" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.2.3, released 2017/11/20" LIBXML="2.9.4" LIBJSON="0.12.1" LIBPROTOBUF="1.2.1" WAGYU="0.4.3 (Internal)" TOPOLOGY RASTER

but the problem still occurs. With the same database I've tried with several Lizmap versions (stable: 3.3.4-pre, unstable 3.3.5-pre, 3.4.0-pre.190808, 3.4.0-pre) but when I try to edit already inserted geometries with related attribute table, in all of them I don't see any ticks on checkboxes even if data are written in database :-( Please help me.

[raphaelsorlin]

Hello, I had the same problem but with a view. The problem came from the "{}" that QGIS added in the multiple response field. Lizmap used the same method to operate via QGIS server.

Context: I worked with a view, modifiable by a trigger "instead of" and a function. My multiple response field (including the string_agg function) passing on QGIS found it with square brackets and I had to remove them via the trigger function to allow modifications (postgres does not like square brackets). The solution was to concatenate the brackets natively to the multiple response field. So my string_agg(blahblah, ',') became { || string_agg(blahblah, ',') || }.

We work with Postgres 11, QGIS 3.4 and Lizmap 3.3.

I remain at your disposal if you need more details. Have a good day.

Raphaël

[paolo73an]

First of all thanks @raphaelsorlin for writing your experience. I beg your pardon but I haven't understood very well your solution. Sticking to my specific case, how do I have to modify a value relation field written as {data1,data2,data3} or as {"data1","data2","data3"} to make it editable by Lizmap web client ?? Just in the form { || data1,data2,data3 || } or { || "data1","data2","data3" || } , or how ?? Thanks

[raphaelsorlin]

According to my knowledge, the multiple field must be in character varying type and with native brackets, that is to say that we must see {data1, data2, data3} without the quotes. Do you work with PgAdmin for your Postgresql / Postgis databases?

Thanks to the view I was able to concatenate the square brackets around the text, but I don't know if this is possible in a normal table like in your case.

[paolo73an]

@raphaelsorlin I do use character varying type field and yes I use pgadmin. See picture below:

I'm trying to understand how to do the trick :-)

[raphaelsorlin]

Oh sorry, I just saw that my field was in text format and not character varying. Here are the examples of my query and my table (these are sequences of identifiers).

'{'::text || string_agg(DISTINCT l_langue_pt_vente.id_langue::text, ','::text) || '}'::text AS id_langue,

If that doesn't change anything, unfortunately I have no other solutions

[paolo73an]

even changing column data type to text, same problem still occurs: multiple values (both with both without quotes) are read by lizmap but they are not editable (no ticked checkboxes appear as in first post image)

[Gustry]

Do you still replicate this issue ? I think it has been fixed.

Try with an up to date version of Lizmap.

[github-actions[bot]]

This issue is missing some feedbacks. 👻 Please have a look to the discussion, thanks. 🦎