Numerical sort too?

I'm not currently up to the task as I am working on a few other modules, but there is some preliminary code in the older natsort (http://drupal.org/project/natsort) module for 5.x. It appears to be able to do numeric sorting (including numbers within a line of text) using SQL functions.

It should be possible to port some code and absorb the features of said module.

A feature sorting like:

ORDER BY CAST(mid(c1, 1, LENGTH(c1) -5) AS unsigned)

Would be great.

Anyone knows how to accomplish this with a module?

Wanted to share a 'cheat' for theming the column to not show the leading zeros while still sorting to look like natural sort:
in the Views template file for the field -- example views-view-field--field-myfield-value.tpl.php (copy views-view-field.tpl.php from views/theme to your theme)
you could enter

<?php $numeric = trim($output, "cat "); ?>
<?php $numeric = ltrim($numeric, "0"); ?>
<?php $alpha = rtrim($output, "0123456789"); ?>
<?php $output = $alpha . $numeric; ?>

<?php print $output; ?>

to get rid of leading zeros. Hope that helps.

+1 for this feature!

Possible solution here: Issue 558668

This is not a complete solution because it does not address negative number. I believe decimal numbers (positive non-integers) will work, although I haven't tested it. Scientific notation definitely won't work. It also hasn't been tested (since I'm not actually using this module).

I'm not sure that scientific notation would be a big enough use-case, but this does present an interesting problem set.

DanCadwick's solution would work for Quartz's usecase, but I'm thinking I'm going to need to think this one through a bit to get it to work for decimals and negative numbers (assuming there is a use case for that). I'll keep this up to date with my progress.

Now this feature won't support something crazy such as

1 cat
cat 2
ca3t

and order it that way... just so everyone is clear... it would in fact, order based on number placement. So if you place all your numbers at the beginning of words, they will all be ordered together regardless of the word. such as:

2 apples
20 oranges
35 apples
112 mangos

instead of

2 apples
35 apples
112 mangos
20 oranges

Notice the words are ordered alphabetically and the numbers are ordered in increasing order.

Hope that makes sense and is acceptable. Any objections to these limitations?

This was originally posted to #1791950: Numeric Sort and #1711948: Numeric Sort . All I did was turn it into a patch file and test 6.x-1.x so mgbellaire should get the git attribution.

This is actually a step back from DanChadwick's work from #558668 where he implemented a fix in conjunction with adding a few other words. From what I can tell, it looks as if the numeric sorting can only be at the end of the word group. Unfortunately this is unacceptable for the feature.

With that said deekayen, I really appreciate you bring my attention back to this project. I've learned a lot since April and I can probably whip something out that does what I posted in post 8 above.

#9 does what I needed. I'm not entirely clear what scenario isn't covered, but the view I tested has node titles like this, which #9 showed "naturally".

1st Day
Classic Graphics
P7 Training
P12 Requests
P30 Kitting
P123 Oversight

"|^([a-zA-Z]*[\.]*)([0-9]+[\.]*[0-9]*)|",

So if you read this... it says At the beginning of the string, check for any or no letters, and possibly a period, then make sure there is at least 1 number. then any other periods (like a decimal) and then more numbers

so anything like

A1.0
A22.00
A.0.1

will match

But anything that has a space will not...

With that said then
A 1.0
A 22.00
A 0.1

will not match and therefore break the sorting order.

If you add the space, then you get this problem...

I only takes in to account that you will only want to ever sort by the first number you come across... However, this will cause complications in something like the following (edge case I know)...

Book of 1337 skills Vol 1
Book of 1337 skills Vol 10
Book of 1337 skills Vol 2

I hope this makes sense... I'm not trying to be harsh, I'm just stating the limitation of using that particular regex.

I don't use this module, but I keep seeing difficulty with this. I updated the code I use in my module, and I thought that I'd share. For a contrib module, I'd put some things in the variable table to allow admins to adjust them for other languages / countries. These would include the regular expression for leading articles, punctuation, and small words to ignore, plus the decimal and thousands character.

function _views_natural_sort_encode($match) {
  // $match[0] is the entire matching string
  // $match[1], if present, is the optional dash, preceded by optional whitespace
  // $match[2], if present, is whole number portion of the decimal number
  // $match[3], if present, is the fractional portion of the decimal number
  // $match[4], if present, is the integer (when no fraction is matched)

  // Remove commas and leading zeros from whole number
  $whole = (string)(int)str_replace(',', '', $match[2]. $match[4]);   
  // Remove traililng 0's from fraction, then add the decimal and one trailing 0
  $fraction = trim('.' . $match[3], '0') . '0';
  $encode = sprintf('%02u', strlen($whole)) . $whole . $fraction;
  if (strlen($match[1])) {
    // Negative number. Make 10's complement. Put back any leading white space and the dash
    // Requires intermediate to avoid double-replacing the same digit. str_replace seems to
    // work by copying the source to the result, then successively replacing within it,
    // rather than replacing from the source to the result.
    $digits       = array('0', '1', '2', '3', '4', '5', '6', '7', '8', '9');
    $intermediate = array('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j');
    $rev_digits   = array('9', '8', '7', '6', '5', '4', '3', '2', '1', '0');
    $encode = $match[1] . str_replace($intermediate, $rev_digits, str_replace($digits, $intermediate, $encode));
  }
  return $encode;
}

/**
 * Encodes a string into an ascii-sortable such:
 *  - Leading articles in common languages are ingored: The A An El La Le Il
 *  - Unimportant punctuation is ignored: # ' " ( )
 *  - Unimportant words are ignored: and of or
 *  - Embeded numbers will sort in numerical order. The following possiblities
 *    are supported
 *    - A leading dash indicates a negative number, unless it is preceded by a
 *      non-whitespace character, which case it is considered just a dash.
 *    - Leading zeros are properly ignored so as to not influence sort order
 *    - Decimal numbers are supported using a period as the decimal character
 *    - Thousands separates are ignored, using the comma as the thous. character
 *    - Numbers may be up to 99 digits before the decimal, up to the precision
 *      of the processor.
 *
 * @param $string string
 *   The string to be encoded
 * @return string
 *   The encoded string
 */
function views_natural_sort_encode($string) {
  $string = preg_replace(array(
    '/^(The|A|An|El|La|Le|Il)\s+/i',
    '/[\#\'\"\(\)]/',
    '/\s(and|of|or)\s+/i',
  ), array(
    '',
    '',
    ' ',
  ), $string);
  // Find an optional leading dash (either preceded by whitespace or the first character) followed
  // by either:
  //   - an optional series of digits (with optional imbedded commas), then a period, then an optional series of digits OR
  //   - a series of digits (with optional imbedded commas)
  $string = preg_replace_callback(
    '/(\s-|^-)?(?:(\d[\d,]*)?\.(\d+)|(\d[\d,]*))/',
    '_views_natural_sort_encode',
    $string
  );
  return $string;
}

and for informal testing:

function test($string) {
  $tests = array($string, '103', '103.0', '-103.0', '-1', '0', '1', '10', '9', '95', '99.9', '100', '-100', '-100.01', '-100.99', '-101', '-101.0', '-99.9', '-99.999');
  $results = array();
  foreach ($tests as $x) {
    $results['encoded: ' . cocktail_natural_sort_encode($x) . ' raw: ' . $x] = $x;
  }
  ksort($results);
  print_r($results);
}

Calling test('123,456,789.123456,789000') results in:

Array
(
    [encoded: -96896.9 raw: -103.0] => -103.0
    [encoded: -96898.9 raw: -101] => -101
    [encoded: -96898.9 raw: -101.0] => -101.0
    [encoded: -96899.009 raw: -100.99] => -100.99
    [encoded: -96899.9 raw: -100] => -100
    [encoded: -96899.989 raw: -100.01] => -100.01
    [encoded: -9700.0009 raw: -99.999] => -99.999
    [encoded: -9700.09 raw: -99.9] => -99.9
    [encoded: -988.9 raw: -1] => -1
    [encoded: 010.0 raw: 0] => 0
    [encoded: 011.0 raw: 1] => 1
    [encoded: 019.0 raw: 9] => 9
    [encoded: 0210.0 raw: 10] => 10
    [encoded: 0295.0 raw: 95] => 95
    [encoded: 0299.90 raw: 99.9] => 99.9
    [encoded: 03100.0 raw: 100] => 100
    [encoded: 03103.0 raw: 103] => 103
    [encoded: 03103.0 raw: 103.0] => 103.0
    [encoded: 09123456789.1234560,06789000.0 raw: 123,456,789.123456,789000] => 123,456,789.123456,789000
)

I have no patch to offer, but it should be trivial to turn this into a patch / patches. I hope this helps.

So it looks like you are doing a simular thing I am with negative numbers. I just haven't gotten the code committed yet. I'll take a closer look at it since I used your work from one of my other issues.

Ok Here is a patch for d7. I'll roll one for d6 shortly. When this code gets taken, attribution will be given to DanChadwick. I've made some modification because of notices, and wanted to make the doc blocks a little nicer, but for the most part this is a port of Dan's code nearly to the letter. I just wanted to run it by all of yall before I committed it. In my tests I came up with the following:

It may not be a complete set of use cases, but Dan's informal test most certainly would be for the number sorting alone. What says ye, the members of the community?

I also wanted to note that this is a huge first step, but that I will work on the other bits like separators and what not when this goes in.

@generalredneck: May I ask what notices you found? I didn't get any and the only change I can find is the stylistic one of concatenating two mutually-exclusive strings v. using a trinary operator "empty()?" test. I would like to roll fixes back into my code.

What other bits you want to add? I'd like to backport them too. One might try to strip anything that isn't a unicode character or number using \Pxxx, but I'm not sure if all PHP environments are compiled with this support. Or one could use str_replace to strip out some common punctuation.

I found this interesting reference: Unicode v. RegEx

actually it's not just stylistic. I was getting notices when I was dealing with numbers that had decimals in them that said "Notice: Undefined offset: 4 in _views_natural_sort_number_encode_match_callback() (line 160 of /var/www/example/sites/all/modules/views_natural_sort/views_natural_sort.module).". Therefore, with this way, if 4 isn't available, 2 will be displayed and vise versa. Unless this is logically wrong of course... THe way I saw it, we would either have 2 or 4... but never both, which is what you were relying on when you concatenated the 2.

Most of it was doc block obviously. I think for the commas and stuff I'm going to use a variable like you said, but default it to what ever the local says it's supposed to be. We will see if I can get this all going...

Good thoughts on the extra strip outs...

Interesting. Must be a difference in PHP version, platform, or compilation options. I don't believe the preg_XXX spec specifies what will be returned if an alternative match path is not taken. On my development machine, it returns empty strings. Apparently on yours it returns nothing (missing array elements).

Alas, empty() isn't right either. On a machine like yours that returns nothing (rather than empty string), when $match[4] == "0", your code will then select $match[2], which would of course be NULL. empty("0") is FALSE. Casting NULL to an int would throw a notice on your machine.

So to handle all situations, I think we need:

  ... (isset($match[4]) && strlen($match[4]) > 0) ? $match[4] : $match[2]

Phew. Learned something today.

Wouldn't a simple isset($match[4]) && $match[4] !== NULL work? I would think it would be slightly more performant as well as more readable. What you think?

Ooooooh I see. K yours it is... We don't want it to match an empty string either.

Alright, I think I've got these worked out... Reviews are welcome... If these go in... this will be a 1.1 release for D6 & 7. There is a patch for both here.

Alright guys, I've done some extensive testing on this. Any issues you all find with it in the new 1.2 release, please make a new issue.

Want to say a big thanks to both DanChadwick and deekayen for all the help they have been! And of course Quartz for bringing up the issue originally. Over 3 years ago! (before my time as maintainer I might add).

Btw DanChadwick... Congratz

yay! applause, congrats, etc - this is really helpful