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?