-2

I have some records in two databases (MySQL and MariaDB), that I must sort using natural sort. In mysql I use function and triggers from this: https://stackoverflow.com/a/12257917/1408681 but not sure, how can I generate such string in MongoDB. I also use PHP, so it isn't a problem to generate such string before update record, and then use for sorting.

But... how? I'm looking for function to generate special string from string, which can be then used to natural sorting.

lukasamd
  • 651
  • 2
  • 8
  • 17
  • Please give an example of your string and your 'special' string – LLJ97 Jul 08 '19 at 10:44
  • Example, my string: 2018-D4-0023 Special string: 0000002018-D0000000004-0000000023 It's from linked mysql function and works fine. I need something similar for php, or just better explain, how to write this. – lukasamd Jul 08 '19 at 10:49
  • So, take it apart into its components, and then re-assemble them in the needed format … Exploding at `-` gives you the three “basic parts”, the `D4` you still need to split (or access the two parts via string index, if this is really only ever two characters, and they are within the ASCII range) - and then either use str_pad, or sprintf, to get the desired result. – misorude Jul 08 '19 at 11:06
  • @misorude yes, but it's only for this small example. What if I have "ABC123" or "ZYS.**#XKS-23" or "TEST:12:C"? It must generate proper string always. – lukasamd Jul 08 '19 at 15:03
  • What exactly is supposed to be the common “pattern” between those then? – misorude Jul 09 '19 at 06:41
  • @misorude there is no patter between them - it can be any string. I just need to transfer that string into another, that will work in standard sorting. Example: 1, 2, 8, 10, 12 - sort them as string, it will be not natural (1, 10, 12, 2, 8). After such tranformation, I can use new field to sort properly. – lukasamd Jul 09 '19 at 07:08
  • Then at least explain what the “proper string” would be for those last examples you have given. – misorude Jul 09 '19 at 07:17
  • @misorude it was that special string from comment above. Result of using function udf_NaturalSortFormat from mysql, but I need something similar in php – lukasamd Jul 09 '19 at 07:20

1 Answers1

0

In my case, solution is to use https://docs.mongodb.com/manual/reference/collation/ - native option for sorting in MongoDB. It much better than create next field.

lukasamd
  • 651
  • 2
  • 8
  • 17