We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
MODX Community Forums Archive

Answered How to sort by just the numbers in a field

    • 45118
    • 123 Posts
    redhen Reply #1, 8 years, 1 month ago
    Hello everyone!

    I have a list of product names that need to be sorted. Unsorted example:
    BRA012345
    BLK123456
    COR001234
    BLK234567

    In my snippet I have the following code for sorting: 
    $sortby = 'name';
$c = $modx->newQuery('Products');
$c->sortby($sortby, 'ASC');
    This sorts my products alphabetically like this:
    BLK123456
    BLK234567
    BRA012345
    COR001234

    What I want is that the list gets sorted by just the numbers and disregard the tree starting letters, like this:
    COR001234
    BRA012345
    BLK123456
    BLK234567

    Is this possible? All help is greatly appreciated!

    This question has been answered by Bruno17. See the first response.

      • 4172
      • 5,888 Posts
      Bruno17 Reply #2, 8 years, 1 month ago
      this is something like this one, and should work the same way:
      http://forums.modx.com/thread/?thread=99327
        -------------------------------

        you can buy me a beer, if you like MIGX

        http://webcmsolutions.de/migx.html

        Thanks!
        • 45118
        • 123 Posts
        redhen Reply #3, 8 years, 1 month ago
        Quote from: Bruno17 at Mar 02, 2016, 04:39 PM
        this is something like this one, and should work the same way:
        http://forums.modx.com/thread/?thread=99327
        Sorry Bruno, but I don't know how to translate that situation into mine. Can you help some more?
        • discuss.answer
          • 4172
          • 5,888 Posts
          Bruno17 Reply #4, 8 years, 1 month ago
          I think, you will need

          $c->sortby('CAST(SUBSTRING(name, 4) AS SIGNED)', 'ASC');


            -------------------------------

            you can buy me a beer, if you like MIGX

            http://webcmsolutions.de/migx.html

            Thanks!
            • 45118
            • 123 Posts
            redhen Reply #5, 8 years, 1 month ago
            Great! That works. Thanks Bruno!!
            In my application I have to define $sortby, so this is how it'll look in my snippet: 
            $sortby = 'CAST(SUBSTRING(name, 4) AS SIGNED)';
$c = $modx->newQuery('Products');
$c->sortby($sortby, 'ASC');