Allow submissions to be sorted by the number of days in stage

[NateWr]

Describe the problem you would like to solve
The new submissions table (#8880) includes a column for the number of days that a submission has been in its current stage. Editors would like to be able to sort the list by this column to find submissions that have been stuck in a stage for too long.

Describe the solution you'd like
Extend the REST API to support ordering by days in stage with the orderBy parameter. Use this to add a sort button to the table column.

Who is asking for this feature?
This was a recommendation after user tests for #7495.

[Devika008]

Clarification on "Days" Column vs. "Days Since Last Activity" in Submission Dashboard & Filters

In a discussion with @jardakotesovec and @Tribunal33 on this issue: [Redesigned Dashboard - Filters #10764](#10764), there was some confusion regarding the "Days" column on the dashboard versus "Days Since Last Activity" in the filters.

Current Situation

• As per the last discussion, both "Days" on the dashboard and "Days Since Last Activity" in the filters are currently showing the same information—Days Since Last Activity.
• To clarify user expectations, a small user research study was conducted.

Survey Results & Proposed Update

Based on the survey results, the original intention of the "Days" column on the dashboard should be retained:
✅ "Days" on the dashboard should indicate how long a submission has been in its current stage, regardless of the last action taken.
✅ "Days Since Last Activity" should remain in the filters, allowing users to filter based on when a submission was last updated.

A summary of the user research findings is provided below:

Total Respondents: 147

Question Asked: What would you prefer to see on the dashboard
Option 1: Days Since Last Activity → 21 respondents
Option 2: Days in Stage → 38 respondents
Option 3: Days Since Last Activity on Dashboard & Days in Stage as a Filter → 42 respondents
Option 4: Days in Stage on Dashboard & Days Since Last Activity as a Filter → 46 respondents

Key Takeaways:

✅ Editors find "Days in Stage" more useful than "Days Since Last Activity" when displayed on the dashboard.
✅ Some respondents wanted both metrics but preferred "Days in Stage" on the dashboard
✅ Clarity is needed on what counts as "activity" – Some asked whether simply opening a submission page would count as activity.

[Vitaliy-1]

If without data modifications, we can count the number of days from the last editorial decision. E.g., from a technical perspective we can get the submission's active stage, let's say it's copyediting, then track the last decision associated with moving to this stage: either ACCEPT or BACK_FROM_PRODUCTION, whichever is the last. And then calculate the number of days elapsed from when the decision was made (date_decided from edit_decisions) table

[Vitaliy-1]

How it could be achieved:

Associate decisions with the corresponding stage, e.g., for OJS:

1. review stage - EXTERNAL_REVIEW, BACK_FROM_COPYEDITING;
2. copyediting - ACCEPT, BACK_FROM_PRODUCTION, SKIP_EXTERNAL_REVIEW,
3. production - SEND_TO_PRODUCTION, while status isn't published
4. submission - CANCEL_REVIEW_ROUND if it was the first one (BTW the decision isn't being recorded in this case and requires fix), REVERT_DECLINE, otherwise if submission is in the stage 1 - calculate days in the stage from the date_submitted.

Build a query to retrieve dates the associated decision was made. That's could be not trivial. Maybe we can use the same approach as for views count to re-use Collector or create a separate method in the Submission Repository that aggregates needed data. It should accept an array of submissions and build a query based on that, not the individual submission - for performance reason. I'd vote for the former option with a Collector but it may not be suitable.

Finally, map this dates in the submission schema, either like raw dates or transform into days in the stage. Add orderBy = days in stage parameter into API endpoints which deal with submissions list:

pkp-lib/api/v1/_submissions/PKPBackendSubmissionsController.php

Line 534 in 470ab04

protected function getSubmissionCollector(array $queryParams): Collector