Custom field sorts only as text

[aroach@…]

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.

[sid]

What is the type you have defined for the field in trac.ini? Looking at TracTicketsCustomFields, there is no integer type 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.

[cboos]

More custom field types are needed.

[richard.dunlap@…]

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.

[bobbysmith007@…]

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?

[anonymous]

+1 for this fix. The lack of sorting impacts the efficiency of ​http://trac-hacks.org/wiki/TimingAndEstimationPlugin

[jhn@…]

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.

[anonymous]

recommend changing the summary from "Custom field sorts only as text" to "add numeric type to custom field types".

[CraigBarber@…]

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.

[cboos]

Milestone 1.0 deleted

[achton@…]

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.

/achton

[cboos]

#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.