Camunda 7.22 Native Query with JOIN Returns Null Values

[David_2003]

Hello there,

I am using the Camunda 7.22 Spring Engine for my Java 21 project.
I want to perform a native query on the ACT_HI_PROCINST table.

Since I need additional data, I want to apply a JOIN:

My SQL query:

SELECT h.*, 
       d.NAME_ AS processDefinitionName, 
       d.VERSION_ AS processDefinitionVersion 
FROM ACT_HI_PROCINST h
LEFT JOIN ACT_RE_PROCDEF d ON h.PROC_DEF_ID_ = d.ID_

		val result = query.sql(sqlQuery).listpage(start, end);

However, the result contains null values for these fields.

I understand that I could execute a separate query for each missing value, but that would be very inefficient.
Is there a better way to achieve this?

I am using Native Query because I need to sort by state and apply pagination at the database level.
I would be very grateful for any kind of help.

[herrier]

Hi @David_2003, assuming you mean processDefinitionName and processDefinitionVersion are null?

From my perspective your query looks ok (and returns data for me on my local install of 7.22).

Have checked the underlying tables and confirmed there is joinable data. Have you maybe cleaned up data somehow in the PROCDEF table?

[hassang]

Hi @David_2003

The native query mechanism returns data as a list of typed objects, where the type is determined by the native query used. Therefore, the columns in the SELECT list must correspond to the fields in the typed object.

[David_2003]

Thank you very much for your reply!

Yes i mean the processDefinitionName and processDefinitionVersion
I am sure that the data are correct.
Did it work in your test? did it realy return the HistoricProcessInstance with the name and version? If yes, would you mind posting a code sniped? maybe i did some other thing wrong.

I just tested it again:
the act_re_procdef raw looks like this:

"id_model11:1:1819"	1	"exmaple."	"deploymentTest"	"id_model11"	1	"1817"	"Deployment not only active Test 2.bpmn"		false	1		"15"	150	true

name: deploymentTest

but the response i get is

from this query:

SQL: SELECT h.*, 
       d.NAME_ AS processDefinitionName, 
       d.VERSION_ AS processDefinitionVersion 
FROM ACT_HI_PROCINST h
LEFT JOIN ACT_RE_PROCDEF d ON h.PROC_DEF_ID_ = d.ID_

ORDER BY START_TIME_ DESC

I tried it with an normal query, and it worked fine. i only can post 1 image, but in my other reply you can see the screenshot

But i need custom querys, because of some sort and filter options, i am considering, accessing it directly throw a normal jdbc connection, and then mapping it to a dto. but im not sure if this is the best solution

Thanks again for the response and
Best regards David

[David_2003]

Thank you very much for your response!

I think the columes in the SELECT List are corresponding.
First of all:
Its the same data structur, as the response from the normal HistoricProcessInstanceQuery.list();
There the name and version is include. so i guess the normal query, automaticly joins the tables.

The result of a native query with the join to the left and the result of the normal query to the right:

as you can see, its the same data structur and name and version are filled.

[hassang]

Hi @David_2003

This means that the column names must match the resultMap defined for historicProcessInstanceEntity in MyBatis as specified in the following mapping file:

HistoricProcessInstance.xml.

Try the following:

SELECT h.*, 
       d.NAME_,
       d.VERSION_ 
FROM ACT_HI_PROCINST h
LEFT JOIN ACT_RE_PROCDEF d ON h.PROC_DEF_ID_ = d.ID_

[David_2003]

Hello Hassang,

Thank you so much for your response, it solved my problem!

Best regards,
David