We are going to create a new table that represents the many-to-many relationship, between the way signs and the destinations, in which the user can create the combinations between both of them.
Then, in a virtual layer, we will dinamically reproduce all the waysigns with an additional field that contains a concatenation of each chosen destination, and the total cost to it.
The constraints that define the new relationship, and the virtual layer, will be saved within the project and managed by QGIS.
Both the normal labeling and the layout item will be done from this virtual layer.
Create a new temporary scratch layer and save it. I will name it waysigns_destinations.
In the new layer, create two integer type columns for the foreign keys. I will name them: waysigns_fk and destinations_fk.
Then, create two relations in the Project Properties window, Relations tab.
For each relation: the parent layer is Way Signs or Destination; the parent field is fid; the child layer is waysigns_destinations; and the child field is waysigns_fk or destinations_fk.
Now, define the relation in the parent's layer widget. The cardinality is not Many to one. For the Way Signs widget, it is Destinations (fid).
Do the same for the Destinations layer.
In the waysigns_destinations layer widgets I am going to ask only that a NOT NULL restriction be respected.
Note that the expression to display in the widget will be the name, not the fid. However, remember that the field is linked to the fid, so that is the real value saved in the attribute.
Now, we can start to create some features.
Save the changes and get ready that here comes the magic.
Relationships are just a set of restrictions and triggers that apply to the fields of the tables, so that they retain referential integrity. That is, you can not store a value that does not meet the indicated reference.
This referential integrity is required so that the queries that combine several tables have the desired result.
We will create, through a Virtual layer, a query that involves all four tables, and hopefully everything works fine.
WITH magic AS (
SELECT
a.waysigns_fk,
group_concat(d.destination_id || ': ' || round(d.total_cost,1) || ' Km', ' <\n> ') label
FROM waysigns_destinations a
INNER JOIN "Way Signs" b
ON a.waysigns_fk = b.fid
INNER JOIN Destinations c
ON a.destinations_fk = c.fid
INNER JOIN "OD-Matrix" d
ON (b.name = d.origin_id AND c.Name = d.destination_id)
GROUP BY a.waysigns_fk
)
SELECT
a.fid,
a.name,
b.label,
a.geometry geom
FROM "Way Signs" a
LEFT JOIN magic b
ON a.fid = b.waysigns_fk;
First, we are taking all the waysigns_destinations rows and joining them with the Way Signs, Destinations and OD-Matrix matching rows.
For that table, we are aggregating a concatenation of the destination name and the total cost, with some characters, grouped by signways_fk.
Then we are taken all the Way Signs rows and matching them with these magic rows.
For that table, we are taking the way sign fid, the way sign name, the concatenation and the way sign geometry.
I would think that you are not going to have problems in labeling or creating the map item you want from this new table.
Notes:
The relationship is not the most important: the table that relates the way signs to the destinations could be filled out by hand, simply taking the precaution of placing valid values (the drop-down lists or expressions in the widget could help).
GeoPackage have some limitations in the implementation of foreign keys. QGIS seems obstinate in bringing the PRAGMA foreign_keys value to False for the database contained in the GeoPackage file.
So I did not include the establishment of foreign keys. The implementation of triggers in its replacement exceeds my knowledge, even so it does not seem necessary at this time (I leave that investigation pending).
If the relation provided in this answer brings you problems, you can get rid of it.
A reading discovered:
