Get all related attribute values from features in list (QGIS)
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:
- https://sqlite.org/foreignkeys.html
The Expression Way
I see no way to query the features of the OD-Matrix layer by matching two attributes at the same time. So I propose the creation of a new combined key field with the origins and destinations in that table.
Having each feature of the OD-Matrix layer unequivocally identified, we can access those rows with the combination of Way-Signs and Wanted Destinations, from the Way Signs layer.
I don't know if it's simpler to do it through an expression than by creating a virtual layer based on an SQL query, but it's another option.
As soon as I wanted to add a virtual field in the OD-Matrix layer, a succession of "Invalid Relation" messages were issued from the panel, so I dared to export all layers to a new GeoPackage and create a new QGIS project without the addition of relationships.
In this process I changed the name of the layers, consider that change when importing the expression into your project.
Create a new field in the od_matrix layer, with the following expression:
"origin_id" || '-' || "destination_id"
I named it od. The expression concatenates the content of those fields with a hyphen in the middle. This new field will be used as a combined key to search for features.
Now, create a new field in the way_signs layer, with the following expression:
array_to_string(
array_foreach(
array_foreach(
array_filter(
string_to_array(
replace(
"wanted_dest",
array( '{', '"', ', ', '}'),
array( '', '' , ',' ,''))),
@element <> ''),
"name" || '-' || @element),
concat(
attribute(
get_feature(
'od_matrix',
'od',
@element),
'destination_id'),
': ',
round(
attribute(
get_feature(
'od_matrix',
'od',
@element),
'total_cost')/1000,
1),
' km')),
'\n')
Is not easy to explain, but you will know what are we doing there:
We are taking the value of the wanted_dest field and replacing some remaining characters, so that they are left in a way that can be translated into an array. Immediately followed we convert it to an array.
We filter that array so that it does not have non-null but empty elements.
We create a new array with one element for each concatenation of the name attribute of the same row, a hyphen and each of the wanted_dest elements.
We use that array to search features in the od_matrix.
For each element we return the destination_id value and the total_cost value.
We transform costs from meters to kilometers, and concatenate with more characters to create a multiline string.
I named that field: label
