Generate dates between a range with OQL / View Entity

0
I'd like to know if it's possible to generate dates between a specific range (for example, the last 30 days) using an OQL query or include it in a view entity. (Similar way as you can do that in SQL with recursive CTEs or using ROW_NUM as a basis.)
asked
3 answers
0

You’re on the right track 🙂 A View Entity can sometimes feel like a WITH block in the sense that it lets you encapsulate a reusable query shape, but there are two important limitations:

 

A View Entity cannot generate rows out of thin air, and it can’t be recursive. So while you can layer View Entities (A uses B, B uses C) to modularize logic, you can’t use them to replicate a recursive CTE that produces a date series or a numbers set.

 

In practice, that’s why the two workarounds you mentioned are the go-to options:

  • Calendar/DateDimension entity for date ranges (and left join to show “zero days”)

  • Sequence/Numbers helper entity (1..N) for “row number / series” style needs, or doing indexing in a microflow after retrieval

 

So yes: View Entities can replace some “WITH-like” structuring, but not the recursive/row-generation part.

answered
0

Mendix OQL is intentionally very close to SQL in terms of syntax and querying concepts, but it does not provide everything that SQL offers. Features such as recursive CTEs, generate_series, and window functions like ROW_NUMBER() used to generate rows are not supported in OQL. Because of these limitations, certain use cases require alternative patterns rather than a pure OQL solution.

 

For example, when you need to “generate” dates (such as the last 30 days), the common Mendix approach is to create a persisted Calendar entity and pre-fill it with daily records for a range of years. You then simply filter this entity (for example last 30 days) and optionally left join it with your fact data so that days without data still appear with zero values.

 

Similarly, when you would normally rely on ROW_NUMBER() in SQL, the usual workaround in Mendix is to use a helper Sequence entity (pre-filled with 1..N) or to handle indexing and ordering in a microflow after retrieving the data.

 

This way, you stay within OQL’s capabilities while still achieving the same functional outcome.

answered
0

Thanks Ahmet, I was thinking about exactly those two solutions if this is not achievable with OQL.

 

I guess you can use another view entity as a replacement for a WITH block, but maybe not recursively.

answered