Yesterday I was writing a report in Mendix for one of my clients and I hit a few different walls. For those that have worked with OQL and Mendix, you've undoubtedly hit the same walls. I searched for some help and frankly found very little. That's probably because it's a lightly used feature in the platform and therefore hasn't gotten a lot of attention from the development team for quite a while, which I completely understand, but I figured that as long as I'm using it and learning more about it, I should capture what I'm learning to help others out there that are hitting those same walls.
I come with over a decade of experience writing SQL in ANSI standards and Native client tools, so I've been around the block on some of the nuances of SQL as well as developed complex queries, so I come at this from knowing what I want to accomplish and how to accomplish it in those tools, but no background in using OQL.
Joins
I really, really like the way you utilize object associations to handle your join statements. The fact that the editor allows you path your way to the entity your are trying to retrieve using the same shortcut auto-fill that the XPATH provides is really powerful and saves a lot of time. However, if you have table that isn't directly related to your data but you want to join it based on values in the tables, you can still use the traditional methods of joining using the ON operator.
CASE Statements
This was one of the biggest pains I had to deal with. It could have just been my lack of Java development experience, but apparently you need to leverage the CAST command on your inputs and outputs. From my research, this is similar to the MERGE command in OQL, wherein it doesn't inherent the datatypes in a CASE statement. Once I cast my WHEN expressions explicitly in a SIMPLE CASE statement, it ran perfectly on my local machine.
However, when I published it to the cloud node, it failed again and through additional research, I figured out that I needed to CAST the attribute I was using. I ended up transitioning it to a SEARCHED CASE statement and CAST everything to get it to work. Strange, but at least I know.
Lastly on the CASE statements, when testing a Boolean, the result of the test will be a string equal to 'true' or 'false', inclusive of those single quotes. So CAST your boolean attribute as a string and then test for = 'false' or = 'true'.
Handling Enumerations
You need to CAST your Enumeration as a string, and the result will be the NAME not the CAPTION.
Default Variables
The same default variables (e.g. '[%CurrentDateTime%]') used in XPATH are available for use in your query, useful in your SELECT or WHERE statements. However, more on dates next.
Dates
This is without a doubt the most frustrating part of using OQL in Mendix. Virtually none of the useful functions that those used to writing SQL are available for use, and that especially applies to dates. As we know, one of the most common ways to constrain a query is by some date or date range. I won't go off on this too badly and just detail what is:
If you use a parameter for a date, it forces you to use it as a range. You can't just prompt the user for a single date if using OQL, you'll have to prompt them to select a range. Keep in mind that the time on the range is always midnight, so if you wanted to retrieve yesterday's results, the user will need to select yesterday as the 'From' date and today as the 'To' date.
You can leverage the DATEPART function to get creative, though. For instance, I've compared the attribute three times to get the right Year, Month, and Day through some elaborate schemes but its really not that useful. Why we can't just have a non-ranged date prompt I don't understand.
Strings
You can count the LENGTH, COALESCE, ...and that's about it. I wanted to substring and spent a full hour trying to figure out a way to do it, thinking maybe there is an undocumented compiler function that might be used such as SUBSTR, RIGHT, SUBSTRING, EXTRACT,...you get the idea. Point is save yourself some time. It does not exist. The answer is to setup a field in your entity in the manner you need it.
Your first thought would be to make it a calculated field, but don't do that because in case you forgot, those aren't stored in the database and therefore can't be retrieved through a db query like OQL. That means that if it is a derivative of another attribute, you will need to setup an event like a before commit that calculates the derivative and maps it to that field. Then you can retrieve it in your statement.
Or, you build your report using a Microflow that populates a DataGrid...
I really do hope Mendix prioritizes this a little bit more and gives it just a little bit of love. More functions like SUBSTRING would go a VERY long way. Being able to pass parameters through the ZOOM feature would be outstanding to enable drill-down. Being able to use DATASETS in a Microflow would be AMAZING!!!
OQL is much faster than the Microflow/Datagrid approach because it just fetches the results from the db instead of creating all of the Java objects, checks rights, etc. that goes into the latter approach. If you need performance, and have the time and skill, OQL is definitely the way to go if you aren't exposing the data as ODATA to a BI tool or ETL'ing it into your warehouse. Think of reporting in Mendix as providing statistic-level reporting, like counts and lists. Anything beyond that and I would recommend the ODATA/ETL approach. But don't write off the usefulness of OQL. Hopefully this guide can help you overcome some of the walls I hit and figured out how to overcome (or avoid!)