Michael MacDonald

Pseudo Natural Sort with Sphinx

In Rails on July 29, 2009 at 8:00 am

The Background:

  • items have a system code
  • all system codes begin with the same string of text eg S-
  • existing system codes have zeros to pad out the string so that all codes are of equal length eg S-001, S-011
  • users need to be able to search for system codes
  • using Sphinx and the awesome ThinkingSphinx gem

The Problem:

  • the business wanted to ditch the unnecessary zeros in the system codes to match the format of other codes in the system eg X-1, C-20, S-14
  • a search results table needed to be sortable by the system codes column eg S-1, S-2, S-10 instead of S-1, S-10, S-2 (the latter is what you get since it is an alpha sort on the whole string)
  • I flippantly proclaimed that we were smart enough to sort the system codes correctly and could easily ditch the redundant zeros

The Options:

  1. eat my words and keep the system codes as they are eg S-01, S-02, S-10
    • it would work but I don’t like letting the computer win
  2. as above but use a view helper to hide the zeros used for padding
    • not acceptable since it would display system codes as S-1 and users would need to search for S-01
  3. remove the prefix from the system codes, change them to integers and use a view helper to add back the prefix
    • not acceptable since the users need to search on the system codes eg S-1 instead of just 1
  4. or somehow get Sphinx and ThinkingSphinx to sort them properly
    • Sphinx can’t handle a natural sort but maybe there is a hack

The Hack:

has "CAST(MID(systems.code, 3, LENGTH(systems.code) - 2) AS UNSIGNED)", :type => :integer, :as => :system_code_sort

Yes, it is only a “pseudo” natural sort as it doesn’t sort the alpha characters but in my specific case it did the job and solved the specific need.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: