Opened 17 years ago
Last modified 8 years ago
#3080 new enhancement
Custom field sorts only as text
|Reported by:||Owned by:|
I've created a custom field to enable me to manually order tickets. I've used integers as the value, but it seems to be sorting as text.
For instance, the order shows up like 1, 10, 11, 2, 3, 4, 5, 6, 7, 8, 9
Would like to be able to sort numerical values through custom fields.
Change History (13)
comment:1 by , 17 years ago
comment:2 by , 16 years ago
More custom field types are needed.
comment:3 by , 14 years ago
In particular, types for userID (resulting in a combo box populated by Trac user ids) and datetime (entry TBD, but displayed as an offset from current datetime a la the creation and last update datetimes) would be very useful.
comment:4 by , 14 years ago
I will second the need for numeric custom fields.
If they were added and a reasonable patch for the query module were submitted, would this be likely to be accepted?
comment:5 by , 14 years ago
+1 for this fix. The lack of sorting impacts the efficiency of th:TimingAndEstimationPlugin
comment:6 by , 14 years ago
i'd like to see this too. i've read through FieldRefactoring and GenericTrac, and while they sound interesting, they are long-term. one of the best things about trac is its extensibility and customizability, and this enhancement could have so many different applications.
in our installation, we would use this for ranking of tickets. we use priority but it is not granular enough. our client ranks their top 20 tickets and we work our way down the list. we used the 00, 01, 02 workaround suggested by sid but we would prefer to have real numeric sorting.
this approach makes sense to me:
add a new type to TracTicketsCustomFields, "number" or "integer".
if a field is set to this type, just use a numeric sort on it with some simple error handling. the data could still be stored as a string in the backend so it seems that the changes would be limited.
comment:7 by , 14 years ago
recommend changing the summary from "Custom field sorts only as text" to "add numeric type to custom field types".
comment:8 by , 13 years ago
As a workaround, you can modify the ORDER BY clause in your reports with something similar to the following (for SQLite):
LEFT JOIN ticket_custom as some_custom_field ON some_custom_field.name='some_custom_field' ... ORDER BY CASE WHEN some_custom_field.value ISNULL THEN 99999999 ELSE CAST(some_custom_field.value AS INTEGER) END
The CASE/ISNULL/99999999 sorts NULLs after all other defined values. You could just use CAST(…) if you don't care about NULL order.
comment:9 by , 13 years ago
|Milestone:||1.0 → unscheduled|
Milestone 1.0 deleted
comment:10 by , 12 years ago
If you only have numeric custom fields, another (dangerous) workaround is to alter the column type of the 'value' column in ticket_custom to float/integer. It may have adverse effects on the precision and visual output of the column content, but should allow proper numeric sorting. Use with care.
comment:11 by , 12 years ago
|Milestone:||unscheduled → next-major-0.1X|
#9929 proposes to introduce pluggable sort orders, for version and milestone fields. Something we could fit into the FieldRefactoring spec and generalize to any field.
comment:12 by , 9 years ago
comment:13 by , 8 years ago
What is the
typeyou have defined for the field in trac.ini? Looking at TracTicketsCustomFields, there is no
integertype for a custom field, so the sorting you are seeing is probably the expected behavior.
A hack to get around this is to pad the field with zeros. So 01, 02, 03, …, 10, 11.