Rule-based symbology based on related layer in QGIS

Question

I'm working with QGIS I have two layers:

• Layer 1: has the id and geom columns (multypolygon layer)
• Layer 2: has the id, gid and type columns (table layer).

The value gid of layer 2 is the foreign key and it is related with id of layer1, so for each id of layer 1 I have many gid of layer 2. I added the relation also in the Qgis project properties.

Now I need to do a rule-based symbology based on this relation 1:N. In layer 1 I want to show a different colour based on Type column of layer2. I suppose I have to do a rule-based for each Type but I don't know the expression to use.

I tried relation_aggregate(relation:='gid_fk', aggregate:='array_agg'("Type", group_by:="Type", filter:="xxx"), expression:="Type") but probably it is not the solution and there is a syntax error.

I try to do a more specific example: layer1 with id=0 is a geometry, layer2 gid0-Type1 is an economic activity and in gid0-Type2 is a touristic accomodation. So I have more information related to one geometry. I was wondering if i can assign a rule-based symbology using the relation_aggregate or if I should create a virtual layer with overlapped geometry, in that case I would obtain 1:1 and no problem of symbology.

Answer 1

For the usecase you have now added, I recommend using a virtual layer. Create one with this query:

select 
layer2.gid, 
layer2.Type, 
layer1.geometry 
from layer2
left join layer1
on layer1.id = layer2.gid

Now you can setup a point displacement symbology on the virtual layer and categorize it on the type of layer2:

You will now have duplicated points from layer1, each having a differnt type from layer2 as attribute. Example result:

Answer 2

This expression will return an array of all types in layer2 for the current id in layer1:

array_foreach(
 array_filter(
  aggregate(
   layer:='layer2', -- grab values from layer2
   aggregate:='array_agg', -- create an array from the layer2 values
   expression:="gid"||'_'||"Type"), -- this array should contain layer2 gid followed by an underscore followed by layer2 type. Kind of immitating a dictionary: gid is the key and Type the value
  regexp_substr(@element,'[^_]*')="id"), -- filter the array, so we only keep the values in the array, which match the current id of layer1. In dictionary speach it would say: keep all values, where the key is equal to the current layer1 id
regexp_substr(@element,'_(.*)')) -- remove the key and only keep the value

So for example on id=0 it will return ['Type1','Type2']. Or on id=1 it will return ['Type1'] and so on.

Since you did not specify, which type you want to use if there is more than one, it is now up to you which one to choose. You can turn the result into a string by using array_get(), array_to_string() or similar functions.