QGIS - Retrieve the most recent value from the child table

Question

I have a layer with objects (1_objects) whose status is regularly updated following visits. The updates are listed in the "Statuts" field of a table called 2_Statuts_suivi. I have set up a parent-child relationship of type 1-n. All this works well.

What I would like is to be able to retrieve the most recent value of the "statuts" field of the 2_Statuts_suivi table and use it in a legend, a layout or in a field of the parent layer 1_objects.

To achieve this, I calculate a "rank" field in a virtual field of the table 2_Statuts_suivi with the expression:

array_find(
array_sort(
array_agg( "fid" ,
group_by:= "UUID_Objet" ,
order_by:="Date_creation desc"
), ascending:=false
),
"fid"
) +1

Then in a virtual field of the 1_objets layer I retrieve the value with the expression:

aggregate(
layer:='Statuts_suivi',
aggregate:= 'concatenate',
expression:="Statuts",

filter := "rank"=1 AND "UUID_Obs" = attribute(@parent, 'UUID'))

The 1_objets layer and the 2_Statut_suivi table are saved in the same geopackage. It works, but it is very very slow.

Is there a more elegant solution to retrieve the most recent value from the child table?

Answer 1

You don't need 2 expressions if you use relation_aggregate where my_relation is the name of the 1:n relation definded in project properties, something like the following expression:

 array_last(relation_aggregate( 
            'my_relation',
            'array_agg'
            ,"Statuts_suivi",
             order_by:="Date_creation"))