Native Query with joins on History Tables

Hi Everyone,
Is there a way to query different history tables with joins?

Requirement:
I have a to retrieve the latest completed process instances along with its variables and task assignees

@Niall @aravindhrs

@Mass_Shake, You need to write native query to join the processdefinition, processinstance, taskinstance, variable instance tables to get the results.

The built in api’s will return result from individual entities like process instance or task instance.

So you can write a facade api, in which you can aggregate the results by querying each entity using builtin api’s or you can write your own join query (select statements) and execute it as native query to fetch results.

Is there a way to map the result of the native query using joins to a custom entity?

TaskEntity will have below below references:

  // execution
  protected String executionId;
  protected transient ExecutionEntity execution;

  protected String processInstanceId;
  protected transient ExecutionEntity processInstance;

  protected String processDefinitionId;		

VariableInstanceEntity will have below below references:

  protected String processDefinitionId;
  protected String processInstanceId;
  protected String executionId;
  protected String taskId;

What i meant was, If I execute the native query using joins , I want to map the result set to a custom Java object.How can I achieve that?

processinstanceid reference will be available for process instance, task instance, variable instance. Using processinstanceid query for task instance. You need to relate process instance to task instances (1 to *), task instances with variable instances (0 to *)

If I understand correctly you mean
List processes = historyService.createNativeHistoricProcessInstanceQuery().sql(query).list();

query =“Native query with joins”.

processes->process->getTasks->getVaribales

I am thinking of
List processes = historyService.createNativeHistoricProcessInstanceQuery().sql(query).list();

okay got it. After join query, the api will return process instance alone. Either you need to query the join query directly in the db without using camunda java api or query individually for each instances and relate it