Uploaded image for project: 'RAML Module Builder'
  1. RAML Module Builder
  2. RMB-381

Natural sort order for a string field that may contain numbers

    XMLWordPrintable

    Details

    • Type: New Feature
    • Status: Open (View Workflow)
    • Priority: P4
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Template:
    • Sprint:
      CP: Roadmap backlog
    • Development Team:
      Core: Platform

      Description

      Sort strings using the number value if they contain numbers. Example:

      1-1
      1-2
      1-10
      1-11
      1-100
      2-1
      10-1
      11-1
      100-1
      a 1 b 1 c 1 d
      a 01 b 1 c 2 d
      a 1 b 1 c 10 d

      Currently this is sorted character by character:

      1-1
      1-10
      1-100
      1-11
      1-2
      10-1
      100-1
      11-1
      2-1
      a 01 b 1 c 2 d
      a 1 b 1 c 1 d
      a 1 b 1 c 10 d

      The natural sort order is also called humanized sort order: https://en.wikipedia.org/wiki/Natural_sort_order
      Code examples exist for Postgres: https://stackoverflow.com/questions/12965463/humanized-or-natural-number-sorting-of-mixed-word-and-number-strings

      Extend schema.json to allow the index section of a field to allow "naturalSortOrder": true (defaults to false). If true create the index using natural sort order.
      Extend cql2pg to use natural sort order for that field if it is in the `sortBy` clause of a CQL query.

      Out of scope: Supporting more than one sort order for a given field and picking one of them using a sort modifier in the cql query, for example sortBy poLineNumber/sort.natural and sortBy poLineNumber/sort.string

      Suggested SQL function:

      CREATE OR REPLACE FUNCTION natural_sort(text, int) RETURNS text AS $$
        -- Return $1 with each sequence of digits expanded to length $2 by prepending leading zeros or
        -- shrunken to length $2 by deleting leading zeros. Other digits get never deleted.
        SELECT regexp_replace(
          regexp_replace($1, '\d+', repeat('0', $2 - 1) || '\&', 'g'),
          '0*(\d{' || $2 || ',})', '\1', 'g')
      $$ LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE; 

      This function does not truncate numbers that exceed the max length configured by $2.

      RMB currently supports sqlExpression and sqlExpressionQuery but it doesn't support them in the sortBy clause.

        TestRail: Results

          Attachments

            Issue Links

              Activity

                People

                Assignee:
                Unassigned Unassigned
                Reporter:
                julianladisch Julian Ladisch
                Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                  Dates

                  Created:
                  Updated:

                    TestRail: Runs

                      TestRail: Cases