2

I have a main postgresql table called "site" and another non geometrical local table called "values".

I used the value relation widget on a text[] column named "choices" from the "site" table to show up multiple choices values from the "values" table. So my choices column shows entries like this : {value1, value2, value3, value4} or this : {value2}

The problem is that I can't seem to understand how to do searches using the values of that column.

If I use the search function of the QGIS form, I can choose only one the drop down values from the "choices" column and when I try to filter using one of the values the result is always empty even if some entries have only one value.

I would like to be able to filter my entries using one or multiple entries from that "choices" column using the QGIS search form.

Is that possible?

Here is some pictures of what im trying to do here.

3 Answers 3

1

Use the Advanced filter (Expression) option with an expression that converts the string to an array (1) and then uses array functions to find/filter for an inupt (2).

Step 1: convert the content of the field choices to an array,

  • Remove the curly brackets {} as well as the double quotes " from the string (line 2 in the following expression)

  • Convert the string to an array (line 1), based on commas as delimiter character (line 3)

    The expression for that looks like:

     string_to_array ( 
         regexp_replace (choices,('{|}|"'),''),
         delimiter:=','
     )
    

Step 2: Array functions to search the string

There are different options to search this string, e.g. the following two:

1. Option 1

If you want to check if a certain text is contained (e.g. filter in a way as to return only features containing Europa), use an array_contains() function (replace [@array] in line 2 with the expression from above):

    array_contains (
        [@array],
    'Europa'
    )

2. Option 2

If you want to return all features that contain at least one of several elements, use an array_intersect() function (replace [@array] with the expression from step 1). This expression returns all features that contain either Creole or Europa (or any of the other name in line 2):

    array_intersect( 
       array('Creole','Europa','Australia','Germany','USA'),
       [@array]
    )

3. Screenshots

Left, filtering with option 1: showing all 11 features with the ones containing Europa highlighted in yellow; right: filtered with the expression from above, only the 5 features containing Europa are show up: enter image description here

Filtering with option 2: showing the 8 (from totally 11) features that contain Europa and/or Creole: enter image description here

0

To store multiple choices, it is a good idea to make an array type field.

This new field can therefore store several values. Then for filtering you can use the function: Array_contains = 'value'

3
  • So i turned the "choices" field to text[] but searching in this still doesnt work. I still can't filter my entities using multiple values in the qgis form. any ideas?
    – tonyduky
    Dec 23, 2021 at 14:37
  • Ideally what i would like to do is display the value with checkboxes in the search mode of the qgis form so that you would have to check the selected values and then filter it.
    – tonyduky
    Dec 23, 2021 at 14:48
  • 1
    Can you send a picture please
    – LM10
    Dec 23, 2021 at 15:10
0

Antother option is to use Regular Expressions. To define a filter condition that finds all entries that contain Europa and/or Creole looks like this:

regexp_match(choices, ('Europa|Creole'))>0

enter image description here

4
  • So i already tried the array_... fonctions but I was looking for a way to not have to use field calculator to do filtering on arrays. What i would like is from the qgis form filter mode, being able display the values with checkboxes (like in the form browse mode) and use the checkboxes to build the query (probablement an array_intersects) to filter the results. The thing is that I'm trying to make "random user" friendly QGIS form so that even noob in QGIS could use it. Thanks a lot
    – tonyduky
    Dec 26, 2021 at 13:03
  • OK, but if you check two boxes, how should QGIS know the logical connection/operator, like and, or? Should only features be returned where both elements are contained? Or all features where at least one element is contained?
    – Babel
    Dec 26, 2021 at 13:06
  • If i check two values/boxes it should give me results where an array contains these two values (amongst others or not). if i check one value, it should give me results where an array contains this value (amongst others or not) and so on. Do you think its possible?
    – tonyduky
    Dec 26, 2021 at 13:44
  • I don't know of any direct method to do that. For sure, at least programatically using PyQGIS, there should be a possibility.
    – Babel
    Dec 26, 2021 at 15:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.