Unable to use STRING_AGG in View entities

0
Hi all,   After recently upgrading our Mendix application to version 10.24 we are experimenting with the "new" View entities functionality. We're having difficulty with one functional requirement, which is to concatenate strings from a single attribute in several objects. In the OQL documentation I found that I should be able to use the STRING_AGG function for this (link), but when I try this function the OQL editor returns an error: Unknown function 'string_agg'.   Is there a way to aggregate strings from multiple objects using view entities? A simplified version of my OQL query is as follows: FROM Administration.Account as A JOIN A/Administration.Account_Bestuur/MijnBesturen.Bestuur as I_B GROUP BY A.FullName SELECT DISTINCT A.FullName as Functie , STRING_AGG(I_B.Nummer,',') as SectorAgg  
asked
1 answers
0

You cannot use STRING_AGG in Mendix OQL.Although the documentation lists it, the OQL engine in View Entities does not implement this function, which is why you get:

Unknown function 'string_agg'

Mendix OQL currently does not support any string aggregation functions (STRING_AGG, LISTAGG, GROUP_CONCAT).

✔ Workarounds

  1. Microflow aggregationRun your OQL / view entity query normally, loop through the result list, and build your comma-separated string manually:

    $result + ',' + $item/Nummer

  2. Database View (recommended if using SQL Server/PostgreSQL)Create a DB view using native SQL:

    STRING_AGG(Nummer, ',')

    Then expose that view in Mendix as an External Entity.

✔ Summary

There is no direct OQL solution for string aggregation in Mendix View Entities; use a microflow or a database view

answered