QGIS aggregate - filter by newest date

Question

i have two one-to-may related layers (relation name: tree_observation) parent: trees and child: obeservations. The trees should be revisited on a regular base and for each observation a new child feature should be created. I set up the child layer with fields "treeID" (=connecting field), "Date" and "Employee" - date auto fills with the current date, employee is filled by the user doing the observation.

In the parent layer, i want to create a virtual field "last_observation_done_by" where the employee name who did the last observation should appear.

This is causing me trouble. I set up the virtual field this way:

aggregate(layer:='observations',aggregate:='concatenate',expression:="Employee",filter:=maximum("Date"))

-> in my understanding, this should only give the name of the employee of the latest observation, but only returns NULL

Another approach was to work with relation_aggregate:

relation_aggregate('tree_observation','concatenate',"Employee") 

but it seems to lack a filter function. Is there any other way to get the employee name of the latest observation from the child layer into my parent layer?

Answer 1

The filter needs to return a boolean. Therefore you need to add a comparison:

filter:="Date"=maximum("Date")

Additionally I figured out a little strange behavior. In my testcase maximum("Date") returns a DateTime format, not a Date format, even if the field is in Date format. So you may need to cast it to a Date via to_date(maximum("Date")) to return the correct value which will evaluate to true in comparison with the field "Date".

Try:

aggregate(  
   layer:='observation',
   aggregate:='concatenate',
   expression:="employee",
   filter:="Date"=to_date(maximum("Date"))
)

Answer 2

I think there are two questions to be addressed here. The first is can this be done, and the second is whether it should be done. To answer the first question, the following approach has worked for me in similar situations:

attribute(
  get_feature(
    'observations',
    map(
      'treeID', attribute('treeID'),
      'Date', to_date(aggregate(
        'observations',
        'max',
        "Date",
        "treeID"=attribute(@parent, 'treeID')
      ))
    )
  ),
  'Employee'
)

Similar to MrXsquared, I have noticed that aggregate returns Date values as DateTime values, so recasting using to_date allows for comparing Date values between parent and child.

The code above has worked for me, but I have also found its performance scales terribly with data set size, think of SQL CROSS JOIN with no indexes type of situation. So if the code works for you, great, but be forewarned you will notice its performance drop as the observations table grows.