How to make a chart to count list of objects per creation date?

0
There is an entity “Paintings”, each painting has a creation date. I want to draw a chart, that would display the number of paintings created per year.    In a microflow I can manually create an empty list for each date, and iterate through the list of paintings in order to put them in this list and count. But it sounds wrong and would take forever (and probably would impact the performance). Is there a better way to implement it in Mendix?
asked
2 answers
0

What you need is an OQL query, so you can use Group By.

For example: 

select creation_date, count(*) 

from painting

group by creation_date

having <your condition> (optional)

 

You could have a look at the OQL module in the appstore.

Or maybe look for charts based on (OQL) Datasets. I don’t know if they exist.

answered
0

First thought:  I don’t think you can group by creation date because dates are a point in time.  So this would give you one record for each painting (unless by some chance more than one painting was created at the exact same millisecond)

Beyond that, the answer depends on how many paintings you have in the database.  If its a small number (say less than 1000), your method is probably fine. 

For a larger dataset, you can do this using database retrieves.  Here is how I would do this:

The first retrieve:

 

Get earliest year

Get current year

Retrieve shipments in the current year

Change Variable

I think that would do it, without having to loop through all paintings.

Hope that provides some food for thought.

Mike

answered