I’m trying to query data from an embedded Process Engine. I quickly found out that the provided queries do not contain all needed filter and order possibilities which are necessary for my needs. For example, in the embedded approach it is not possible to sort Process Instances for their start time. So I decided to write custom SQL queries and execute them as native queries to perform the more Special queries. Here I found out that here is not always an Option to execute a native SQL query. So for example for external Tasks, it’s not possible to create a native query.
Did I miss something to do that or is there another possibility to execute a custom SQL query via the given persistence layer?
Yes, you are right, the most services provide a native query option. Unfortunately, the ExternalTaskService, for example, does not. Therefore my question if there is a more general way to execute SQL queries or if there is a way to create a native query for external tasks.
For example, it’s not possible to filter the external tasks for local variables or sort them by creation time.
The external service tasks are just one example to get my point across. There are many limitations in the normal query approach. For example to sort process instances by there start date which is only possible over the rest endpoint. That’s why I try to get this additional information with custom SQL queries over the given native queries.
Maybe you have some tips or better approaches to do that.
Many thanks for your help so far
Have not tested, but I think what you want to do can be done with mixing a few different queries together.
Example for your local variables you can use the runtime service to do variable query which will give you the executions which you can get the process instance id from and thus get the external tasks for.
Same goes for your start date. You can use the runtime service to query for these tasks and once you have the Ids you can use the external task service and filter by specific ids
Ok its possible by fetch all necessary information and merge them by yourself. I think you’re right, this is a legit approach. But this can cause performance issues for a large amount of data. Therefore, I tried the native option with a more optimized query.This just feels a little bit inconsistent to not provide a native query in the external task service but in all the others.
The external task service is prob doing the same steps as described. If you were doing a local variable lookup, a native query is not really going to change the need to access the variables tables, etc
It’s a difference if you gather the information via two single queries and additionally compute this information, in contrast, that two tables will be joined by (for example) the ids and select the needed rows. But i dont want to contradict you. I think its the best option here. Thanks
You should still be able to join the tables with the runtime. You just have a limit of what entity type will be used as the returned objects. So yes you would have two queries, query 1: process instance native query to get list of IDs. Query 2: external task service to filter based on ids.
If your data is so big that the list of ids are too big or causing performance issues, I would argue you have to many records being returned per request.