QGIS – Using Expressions with Fields from Related Layer

dbmanagerfield-calculatorqgisrelates

In QGIS I have:

  • a point layer;
  • a table;
  • a relation (Project > Properties > Relations) 1 to many between the points and the table;

What I need to do is to set a graduated symbology on the layer points using the related data stored in the table. You can think of the layer points as representing the offices of a company, each one with a unique id, and the records in the table as the persons working in the different offices. I'd like to set the size of the points depending on the number of persons for every office.
Following this answer, I used the db-manager, run a sql query, upload the query results in the project and made a join with the point layer.
I tried to use the expression builder with aggregate(), but in order to get the result I'm looking for I think I should also use count() and then group_by, and in my knowledge this is not allowed in aggregate().

Is it the db-manager the only possible solution in such cases of 1 to many relations?

Best Answer

Yes, there is an easier way using expressions.

In Layer Styling select the expression builder next to the attribute dropdown (circled red in image below). (For the example expression using aggregate() that I explain below, the layer I would be styling here is 'offices', not 'tree_pt' as shown in the image).

enter image description here

The you can build your expression. This will depend very much on what you are trying to do. But for instance, for something similar to the example you link to from your post you might need to use the aggregate function. Let's assume you have

  • a point layer "offices" with attributes 'fid' and 'office'
  • a table "people" with attributes 'fid', 'officeID' and 'name'
  • a relation (1 => n) between "offices".'fid' => "people".'officeID'

And you want to style "offices" based on the number (count) of "people" for each office.

Once in the expression builder dialog (See image below), you can search for functions in the middle pane. When you select a function, its syntax is shown in the right hand pane. If you read the syntax notes for aggregate carefully (always a good tip) then you will see that for the filter argument, unless otherwise stated, fields and geometery are from the joined layer, i.e. in this case 'people'. The source feature is accessed using the @parent variable. So, following this syntax, and in order to 'count' the number of people per office, the expression would be

aggregate(
     layer:='people', 
     aggregate:='count', 
     expression:='fid',
     filter:="officeID"=attribute(@parent,'fid')
)

enter image description here

As an alternative example, you might be styling the child layer based on an attribute value of the parent layer. To do this you could use get_feature() to return the parent feature, and nest this inside of attrbutes() to access its attributes. This might look something like: attributes(get_feature('parentlayer','parentlayer_primarykey',"childlayer_foreignkey"))['parentattribute']

In the above example, get_feature() returns the first feature on the layer named 'parentlayer', where the attribute named 'parentlayer_primarykey' has a value equal to the value of the attribute 'childlayer_foreignkey'. (I use the terms 'primary key' and 'foreign key' loosely here - they only need be the primary key or foreign key for your current purpose). This is then nested into the attributes() function, with attribute to be returned defined at the end, in this case ['fid'].

Related Question