Filtering an aggregate function in a many to many relationship to only records related to current record in QGIS

Question

I have two tables related as an N-M relationship:

Sites: id, geometry

Jobs: id, jobnum, .....

Sites_Jobs: id, site_id, job_id

I want to define a label for the Sites layer that displays all the Jobs linked to that Site.

Note - most jobs only link to one site and most sites only link to one job. Having >5 relations would be highly unlikely so there is not a concern with "overloading" the number of labels being displayed.

It appears that the aggregate function using concatenate is what I want, and I can find lots of examples for a 1-N relationship, but am struggling with the "filter" part of an N-M relationship.

I am trying to say "return the values from Jobs table where id (from Jobs table) = job_id (from Sites_Jobs table) and site_id (from Sites_Jobs table) matches current id from current layer Sites". I an unsure how to include two different tables in the filter

aggregate(
    layer:='jobs',
    aggregate:='concatenate',
    expression:="jobnum",
    filter:= ??????,
    concatenator:='\n',
    order_by:="jobnum"
)

Maybe there is a whole different approach I should be using for the N-M relationship case?

Answer 1

If I understand the correct expression will be this:

aggregate(
    layer:='jobs',
    aggregate:='concatenate',
    expression:="jobnum",
    filter:= "id" = attributes(@parent)['id'],
    concatenator:='\n',
    order_by:="jobnum"
)

When using the aggregate function you can access the feature from the layer where you are performing the calculation by the @parent variable. Then you can use the attributes() function to get a dictionary of attributes.

This part filter:= "id" = attributes(@parent)['id'] signify: if the id field from the jobs layer is equal to the id field of the Sites layer concatenate the jobnum.