How to change sort order for due date column on "Issues assigned" page

Added by Rob D almost 6 years ago

Allow me to try to explain exactly what I'm trying to do.

I'm a Systems Admin at a University, and we use Redmine primarily as a problem tracker. We do not, for the most part, use it as a bug or project tracker, so most of our issues are one-off items without any parent-child relationships, versions, or the like. We do, however, use the due date field extensively.

The shortcoming I've noticed is that, when in the "Issues Assigned to me" view and one sorts due dates, for any issue that has a due date assigned, the issues are sorted with the latest due date at the top. In other words, the due date furthest from the present is listed at the top, then due dates closer to the present, then items without due dates. Example:

2015/04/01
2015/03/01
2015/02/01

Reversing the sort reverses this order and places issues with due dates closest to the present first (at the top), but it also lists any issues without due dates above those, so issues without due dates are the topmost items in the list.

Neither of these sorts makes logical sense to me or my colleagues, so I'm trying to find a way to create the following type of sort order for issues (where the dates are due dates):

2015/02/01
2015/03/01
2015/04/01
2016/01/01
etc.
....
[issues without due dates]

In other words, issues without due dates would always be treated as if those due dates are infinitely out into the future.

I've searched the forums and Google extensively for a means of doing this and found nothing exactly applicable to our situation. I would very much appreciate any help the community could offer.

One note: please do /not/ suggest assigning due dates to every item. We've considered and attempted that approach, and we've determined that we need to retain the ability to save issues without due dates, for a variety of reporting and workflow reasons.

Here is the information on our environment:
CentOS 6.5
ruby 1.8.7 (2011-06-30 patchlevel 352) [x86_64-linux]
Rails 3.2.19
mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

output of about script:
Environment:
Redmine version 2.5.2.stable
Ruby version 1.8.7-p352 (2011-06-30) [x86_64-linux]
Rails version 3.2.19
Environment production
Database adapter MySQL
SCM:
Subversion 1.6.11
Git 1.7.1
Filesystem
Redmine plugins:
redmine_local_avatars 0.1.1

Thanks in advance for your help!

Rob

Replies (8)

RE: How to change sort order for due date column on "Issues assigned" page - Added by Jan Niggemann (redmine.org team member) almost 6 years ago

The sort order is the result of the underlying SQL statement's ORDER BY.

To order in the way you describe:
- you need to hack redmine (ActiveRecord), but I doubt that this would be possible.
- exclude all empty due dates in your query and only order on those non-empty ones.

One note: please do /not/ suggest assigning due dates to every item. We've considered and attempted that approach, and we've determined that we need to retain the ability to save issues without due dates, for a variety of reporting and workflow reasons.

A classic fallacy on your part (IMHO). Not all problems caused by peoples' inadequate working attitudes can be solved by technical means.

RE: How to change sort order for due date column on "Issues assigned" page - Added by Jesús M. Navarro over 4 years ago

Jan Niggemann (redmine.org team member) wrote:

The sort order is the result of the underlying SQL statement's ORDER BY.

This certainly explains why it happens, but says very little about if it makes sense in this situation or not.

To order in the way you describe:
- you need to hack redmine (ActiveRecord), but I doubt that this would be possible.
- exclude all empty due dates in your query and only order on those non-empty ones.

One note: please do /not/ suggest assigning due dates to every item. We've considered and attempted that approach, and we've determined that we need to retain the ability to save issues without due dates, for a variety of reporting and workflow reasons.

A classic fallacy on your part (IMHO). Not all problems caused by peoples' inadequate working attitudes can be solved by technical means.

Then, there's that other fallacy of the developer thinking to know better than the user. Sometimes that's true, sometimes it isn't. Anyway, the good developer (IMHO) knows that his software is better if it allows to properly work in unanticipated (while sensible after described) ways.

I know that a kind of project manager on duty will most probably add starting/due dates for issues when planning a new project, but I also know that a sysadmin working more or less kanban-style will not, since his work-mode is that of queuing plus best effort (unless there is an externally set due date for whatever reasons). I fail to see how one of these use cases is any more valid than the other.

Given that sorting by a field with undefined contents can only happen by convention, I'll share my thoughts based on my experience:
  • If I'm requesting issues to be sorted by due date, it means due date is of importance to me, so issues with an assigned due date should be somehow highlighted over those without (i.e.: by sorting before).
  • Not having an assigned due date on an issue means I don't know/it doesn't matter and, again, known info should prevail over "don't knows/doesn't matters".
  • Excluding in the query issues without due date may make sense sometimes but I can think of a lot of scenarios when it doesn't (i.e.: multifilter: I still want to see issues at critical priority sorted before any issues at normal priority, even if no due date is assigned to them).
  • An issue can have an implicit due date as long it has a target version assigned (that of the target version).
  • I can't think of an user case where things with no due date should be meaningfully presented before those with a due date when sorting ascending order, but I'm open to consider it if you can present it to me.
So, in sorting by due date (ascending), I would expect the issues sorted like this:
  1. 1/MAR/2016
  2. 2/MAR/2016
  3. no due date [or have it set to its target version's one and sort accordingly]

TIA

RE: How to change sort order for due date column on "Issues assigned" page - Added by Daniel Scott over 4 years ago

Rob D: One note: please do /not/ suggest assigning due dates to every item. We've considered and attempted that approach, and we've determined that we need to retain the ability to save issues without due dates, for a variety of reporting and workflow reasons.

Jan Niggemann: A classic fallacy on your part (IMHO). Not all problems caused by peoples' inadequate working attitudes can be solved by technical means.

Jesús M. Navarro: Then, there's that other fallacy of the developer thinking to know better than the user.

I'm inclined to agree with Jesús here. This kind of ordering (due date ascending, but anything with no due date bumped to the end of the list) is fairly typical of task lists. Back in the day my Palm Desktop application used to sort tasks this way (and I relied on it). I thought outlook did too, but I've just looked and it takes the unintuitive approach of listing dateless tasks first (like redmine currently does). I'm pretty sure others are out there which work the way Palm Desktop did though.

Anyhow, it shouldn't be too big a hassle to tweak the SQL query for date sorting should it? I'm not an expert in MySQL's queries and performance specifically, but wouldn't something like this work (for the ascending date sort order anyway, descending date order could be left as-is so that dateless issues continue to appear at the bottom of the results)?

SELECT COALESCE(issue_date, '2038-01-19'xx) AS sort_date, {other fields} 
FROM Issues
ORDER BY sort_date ASC

xx (or whatever the largest date MySQL can handle is)

Anyhow, consider this a +1 from me for ordering issues like this :) - it's how I work, and I was surprised to find today that redmine sorts on due-date in the way it does.

RE: How to change sort order for due date column on "Issues assigned" page - Added by Daniel Scott over 4 years ago

As a follow up to my last reply, it's interesting to note that OpenProject (which I only learned about recently, and began as a fork of Redmine) has clearly addressed this issue in their time.

Sorting on due date ascending in OpenProject does what you'd reasonably expect (as a user, rather than a developer) and lists the dateless issues last:

An example:
https://community.openproject.com/projects/openproject/work_packages?query_props={%22c%22:[%22id%22,%22type%22,%22status%22,%22subject%22,%22assigned_to%22,%22due_date%22],%22t%22:%22due_date,parent:desc%22,%22f%22:[{%22n%22:%22status_id%22,%22o%22:%22o%22,%22t%22:%22list_status%22}],%22pa%22:1,%22pp%22:100}

NOTE: You'll need to copy and paste that URL - something in the big long list of query args isn't translating properly as a clickable link.

RE: How to change sort order for due date column on "Issues assigned" page - Added by Daniel Scott over 4 years ago

As a last follow-up, it seems someone has addressed this issue for Redmine in a plugin (perhaps it'll help the original poster).

https://www.redmine.org/plugins/redmine_smart_issues_sort

Unfortunately this plugin is not compatible with Redmine 3.x (which is the version I run)

RE: How to change sort order for due date column on "Issues assigned" page - Added by Benjamin Billon about 4 years ago

Quick suggestion of update (not sure where it should goes in Redmine's code, and I don't know ruby):

ORDER BY ISNULL(due_date), due_date ASC

(assuming the date is NULL when not provided)

And it should actually work with any other column to sort ; we probably could apply Jesus' principles to all columns, not only dates.

RE: How to change sort order for due date column on "Issues assigned" page - Added by Jesús M. Navarro about 4 years ago

So, in the end, what should we do next? Opening an issue requesting this as a new feature, maybe?

RE: How to change sort order for due date column on "Issues assigned" page - Added by Jesús M. Navarro almost 4 years ago

Jesús M. Navarro wrote:

So, in the end, what should we do next? Opening an issue requesting this as a new feature, maybe?

Done: See #24529.

(1-8/8)