Hi,
I am trying to lookup the historic processes, but I would like to use a queryProperty with it.
I implemented the QueryProperty in a somewhat similar fashion:
public class HistoricalProcessQueryProperty implements QueryProperty {
private static final Map<String, String> PROPERTIES;
private static final String START_TIME_PROPERTY_NAME = "RES.START_TIME_";
static {
Map<String, String> props = new HashMap<>();
props.put("name", "lower(RES.NAME_)");
props.put("definitionName", "lower(DEF.NAME_)");
props.put("createdOn", START_TIME_PROPERTY_NAME);
props.put("endedOn", "RES.END_TIME_");
PROPERTIES = Collections.unmodifiableMap(props);
}
private String name;
public HistoricalProcessQueryProperty(final String name) {
this.name = PROPERTIES.getOrDefault(name, START_TIME_PROPERTY_NAME);
}
@Override
public String getName() {
return this.name;
}
}
Based on the parameter of the input name, the instance will return with different properties.
The code where I use this class:
String property = order.getProperty();
processQuery.orderBy(new HistoricalProcessQueryProperty(property));
if (order.isAscending()) {
processQuery.asc();
} else {
processQuery.desc();
}
Where processQuery is a HistoricalProcessInstanceQuery
The very same implementation works fine when I use it with the current processes, that’s because different SQL query runs than what I pasted in here below. But it doesn’t order the result when I use this code with the historical processes.
I enabled the debug logging in flowable and if I am reading it correctly, then this is the query that runs whenever I call the API:
select * from ( select a.*, ROWNUM rnum from ( select distinct RES.*, DEF.KEY_ as PROC_DEF_KEY_, DEF.NAME_ as PROC_DEF_NAME_, DEF.VERSION_ as PROC_DEF_VERSION_, DEF.DEPLOYMENT_ID_ as DEPLOYMENT_ID_,
VAR.ID_ as VAR_ID_, VAR.NAME_ as VAR_NAME_, VAR.VAR_TYPE_ as VAR_TYPE_, VAR.REV_ as VAR_REV_, VAR.PROC_INST_ID_ as VAR_PROC_INST_ID_, VAR.EXECUTION_ID_ as VAR_EXECUTION_ID_,
VAR.TASK_ID_ as VAR_TASK_ID_, VAR.BYTEARRAY_ID_ as VAR_BYTEARRAY_ID_, VAR.DOUBLE_ as VAR_DOUBLE_, VAR.TEXT_ as VAR_TEXT_, VAR.TEXT2_ as VAR_TEXT2_, VAR.LAST_UPDATED_TIME_ as VAR_LAST_UPDATED_TIME_,
VAR.LONG_ as VAR_LONG_ from ACT_HI_PROCINST RES left outer join ACT_RE_PROCDEF DEF on RES.PROC_DEF_ID_ = DEF.ID_ left outer join ACT_HI_VARINST VAR ON RES.PROC_INST_ID_ = VAR.EXECUTION_ID_ and VAR.TASK_ID_ is null
WHERE RES.END_TIME_ is not NULL order by VAR.LAST_UPDATED_TIME_ asc, lower(DEF.NAME_) asc, VAR.LAST_UPDATED_TIME_ desc ) a where ROWNUM < 20001) where rnum >= 1
The relevant section is at the end:
order by VAR.LAST_UPDATED_TIME_ asc, lower(DEF.NAME_) asc, VAR.LAST_UPDATED_TIME_ desc
My query: “lower(DEF.NAME_) asc” which fits into the two VAR.LAST_UPDATED_TIME orders.
Now since the first LAST_UPDATED_TIME is there, my order doesn’t do much in my case, it won’t order the resultset.
If I take it out, then it works fine.
Flowable version: 6.3.1
Can somebody tell me what am I doing wrong?
Thanks,
Tozo