Improper sorting when using pagination

[leggebroten]

It seems that sorting and pagination don't work as I expect.
I had expected that the sort would be applied BEFORE choosing a page from the sorted results.
Instead the sort seems to be applied AFTER getting the specified page.
Behavior is consistent between both REST and the Java library

For example:
REST
   http://{{server}}/activiti-rest/service/runtime/tasks?excludeSubTasks=true&start=1&size=100&sort=due...

Java
        TaskQuery taskQuery = taskService
                .createTaskQuery()
                .processDefinitionKey(processDefinitionKey)
                .taskDefinitionKey(taskDefinitionKey)
                .orderByDueDate().asc();
               
        List<Task> tasks = taskQuery.listPage(pageable.getOffset(), pageable.getPageSize());

Is my expectation wrong?  (Does this mean that if I want to do pagination I will have to get full result set back and do my own pagination)

Thanks in advance!

-Lee

[leggebroten]

A unit test to demonstrate the behavior.
Output 100 tasks sorted by dueDate as one page.
Followed by same query paginated via "listPage" by 10 pages of 10 tasks each.

[leggebroten]

bump

[trademak]

In your unit test you use includeProcessVariables. There's an issue with database sorting with the variables query on some databases. That's why we do that bit in memory. If you don't included the process variables in the query it will use database sorting.

Best regards,

[leggebroten]

Interesting.  I guess that would explain why Activiti-Explorer was showing the same bad behavior when trying to sort completed tasks by due date

[tgebeyehu]

Hi All

i have the same issue on sorting while using pagination, specifically sorting by id.
I tested it in all task, process instances and deployment queries. If i understand it correctly the id's in activiti are set as strings and sorting based on id gives improper results. Is there any reason why there is no check on <java> orderby***Id </java> methods to make sure that string is cast in to long or int before sorting then? i believe that would solve the issue.

is there any currently available way to resolve this?


- i did not include<java>includeProcessInstanceVariables </java> in my queries
- i am using activiti 5.19.0.1

Thank you

[jbarrez]

>  Is there any reason why there is no check on  orderby***Id methods to make sure that string is cast in to long or int before sorting then? i believe that would solve the issue.

No, as this would need to be done on the database level. I'm not sure if this 1) is possible 2) is good for performance

What's the use case for sorting on id? Generally, there shouldn't be a real need for it as it's 'internal detail'.