How to filter an aggregation query properly

Question

I have three layers:

• vegaufnahmen (polygon): includes general data on the vegetation plot. Attribute "FFH_LRT" (string) is needed for filtering
• artenliste_vegaufn (no geometry): is the species list related to vegaufnahmen. Field "Art" (integer) is used for joining layer taxaliste; "taxaliste_ffh_lrtt" is the name of the joined field
• taxaliste (no geometry): contains data on all taxa, among them a field "ffh_lrtt" (string) with numbers and commas, e.g. 6210,6510,6230; 91E0; 6210 coding the habitats in which the taxon typically occurs

vegaufnahmen and artenliste_vegaufnahmen are related layers (vegaufnFID <-> fid). taxaliste is joined to artenliste_vegaufn ("ArtID" <->"Art")

I would like to count the number of habitat-typical species from artenliste_vegaufn per vegetation plot (vegaufnahmen) and display it in a virtual field.

What I tried was following aggregation query:

aggregate(
 layer:='artenliste_vegaufn',
 aggregate:='count', 
 expression:="fid",
 filter:= attribute(@parent, 'FFH_LRT' ) like "taxaliste_ffh_lrtt"
)

The problem was that only taxa with an exact match are counted, i.e. when there is only one number in "ffh_lrtt". I tried "%taxaliste_ffh_lrtt%", but that didn’t work)

Any suggestions how to solve it?

• Erik: Quick idea: Try filter:= attribute(@parent, 'FFH_LRT' ) IN "taxaliste_ffh_lrtt". If that doesn't work, you need to create an array or a list from "taxaliste_ffh_lrtt", on which you can run the IN expression.
• Erik: Oh, additional question: Does "FFH_LRT" only contain a single value, or does it contain several values like "taxaliste_ffh_lrtt"?
• harry komposch: Hi Erik, I tried your solution, but got an error as well.
• harry komposch: And, thanks Erik, I forgot to describe "FFH_LRT": it contains a single value, e.g. 6210

Answer 1

The expression attribute(@parent, 'FFH_LRT' ) like "taxaliste_ffh_lrtt", means to compare the field FFH_LRT of the parent with the content of the field taxaliste_ffh_lrtt of the current record. Since there is no placeholder (like %), it looks for an equality of values.

If it was a string and not a field content, one would simply write like '%mytext%', but since the value is stored in a column, you have to concatenate the % character to the column content like '%' || "taxaliste_ffh_lrtt" || '%'.

That being said, if taxaliste_ffh_lrtt contains multiple values and FFH_LRT a single one, you will have to swap the two conditions "taxaliste_ffh_lrtt" like '%' || attribute(@parent, 'FFH_LRT' ) || '%'

PS: code not tested

• harry komposch: The swapped term "taxaliste_ffh_lrtt" like '%' || attribute(@parent, 'FFH_LRT' ) || '%' worked quite well. And yes, "FFH_LRT" contains a single value. Thanks a lot!