QGIS - Group by expression in attributes form completion

Question

With QGIS 3.22.4 I build a relational value between two tables: a reference table (called culture here) and a geographical layer (called field here). In my reference table, I have several features who sometimes have the same culture value.

In my geographical layer, I configure a relational value in the attributes form to display culture value from culture table in the crop field. I activate auto completion mode to help users.

When I edit or create a feature, I have the same repeated values

Can I group them ? I only want to display unique values.

Edit: I found this solution, with a query and virtual layer but a solution may exist without this extra table.

Answer 1

Try using this expression (QGIS 3.22) in the 'Filter expression' part of your Value Relation widget -

$id=array_first(array_agg($id,"culture"))

It groups the feature id value from the other layer by the desired column, then just grabs the feature with the first id value from that list. The list is therefore filtered to just the first instance of each "culture" value. It's similar to the 'first value' aggregate that is present in several languages/tools, I'm not sure if Q 3.24 has introduced this as its own function.

I'm not sure if it will be very efficient if the source table is quite large though.

Example:

If you know your source layer will be updated regularly/dynamically, $id (QGIS interpreted feature id) can be a little unpredictable (esp with unsaved features), so instead of using $id in the above expression, you could also use a column from the source layer that you know will be unique and non-null every time a new feature is created (e.g. "uuid" or "id")