Issue with search results when using RT::Extension::PriorityAsString

We’re using RT::Extension::PriorityAsString to give “friendly” names to priorities in RT (so “Normal” and “Urgent” rather than just numbers). Underneath is still the number that can be escalated as before, so “Normal” might encompass priorities 0 to 49 for example. Folk like it.

One of our users has noticed a problem though: when you make a search with Priority as the first sorting field and Id as a second sorting field, the output looks odd, with the Id fields out of order. The reason is that under the hood RT is doing the right thing and generating a SQL statement with an “order by priority, id” clause but the priority field is just a number, not the strings covering a range of numbers. Unfortunately, as we have multiple real priority numbers inside the “Normal” category, we end up with an order such as:

2063, Normal
2172, Normal
2286, Normal
2525, Normal
2606, Normal
2727, Normal
2549, Normal
2924, Normal
2532, Normal

This is because the first six have a priority of 4 (which is Normal) whilst the last three have a priority of 6 (which is also Normal). SearchBuilder doesn’t understand this of course because it (and SQL underneath) are just working on the numbers, so lumps the first six results under priority 4 and the next three under priority 6, which is right (from its point of view). The 4 and 6 then get turned back in the string “Normal” by the extension when rendering the results.

Not sure how (if!) I’m going to be able to work round this, but I thought I’d flag it up for others using RT::Extension::PriorityAsString and having odd Priority based search results ordering.

Some suggested options for working round this that we’ve considered so far (not yet implemented as management are having a think about how much of an issue this really is for us):

a) Have a cron job that resets the priority values to be a single fixed value within the range that the PriorityAsString categories give us. This would be escalation wouldn’t work though.

b) Have a hidden PriorityString custom field that is update whenever Priority is changed to match the PriorityAsString value, and then hack the OrderBy to replace “Priority” with “PriorityString” before passing it to DBIx::SearchBuilder. Might even be able to do this with some cheeky callbacks. Escalation would still work behind the scenes.