Count Objects by a Attribute Value

0
I’d need to prepare data for a chart and I need to count the objects in one of my entities and group the count by one (or more) attributes. In SQL terms this would be a count with a group by e.g. select car_colour, count(*) from table_xyz group by car_colour; Which would produce something like: Car_Color, Count(*) --------------,-------------- Red, 104 Blue, 45 Green, 12 etc etc As the attribute’s decreet values can increase over time, this cannot be hard coded in a MF.  How is this achieved in Mendix? 
asked
2 answers
3

Hi Warren,

Would you be able to do the same sort of count in OQL? You could use the OQL module in the appstore and use the “execute OQL statement (count rows)” activity. 

Here is a link to the module:

https://appstore.home.mendix.com/link/app/66876/Mendix/OQL

 

 

Another option would be to create a mf that retrieves, filters, and counts in batches. You would store the statistics in a NPE as Ronald suggested but you would build your structure to be able to handle the dynamic amount of colors. 

 

Something like this to store your statistics:

and then your microflow can retrieve in batches, filter by a color, count, and then create a color object. Heres an example:

https://modelshare.mendix.com/models/b90dae6e-3640-473d-96e2-938ed6ee9fe0/batch-count-a-table

 

In terms of performance the OQL approach would be best but the microflow way would be fine if your dataset is not ridiculously huge.

 

Hope this helps!

answered
0

You can count lists. So retrieve all the Red objects and do a count of that. You can then store this value or create a non persistent object for the chart. Check the documentation here: https://docs.mendix.com/refguide/aggregate-list

Regards,

Ronald

 

answered