1

I am using Sphinx Search (http://sphinxsearch.com/) in combination with SphinxQL and need to sort by a string attribute:

I configured the attribute as sql_attr_string = myattribute.

Example query: SELECT * FROM myindex ORDER BY myattribute ASC;

The order I get with order by myattribute ASC: 1a, 100b, 101c, 2a, 3a

The order I want: 1a, 2a, 3a, 100b, 101c

Is there a way to achieve this?

koseduhemak
  • 523
  • 2
  • 4
  • 19
  • I no zero about SphinxQL. Does it have regex replacement capabilities? If it doesn't, then your best bet might be to maintain two separate columns for the alpha and numeric components of `myattribute`. – Tim Biegeleisen Jan 23 '18 at 11:01
  • I think I cannot use this as a solution, because I can also have values like `a200, c345, a345-2, b495-2a, 495-2a`. But I will try and report back. – koseduhemak Jan 23 '18 at 11:06
  • No...then change your table structure. Mixing numeric and text in the same column is going to lead to a large headache down the road. – Tim Biegeleisen Jan 23 '18 at 11:08
  • Well unless get a sphinx UDF to do it on the fly, you could store a string in an additional attribute, such suitable for sorting. Similar to what natsort would do internally. Usually it just involves zero padding numbers, eg store `1a` as `00001a`, and `100b` as `00100b` - the trick is padding it enough to capture your largest number is handled, used 5 digits in example, but you may need many more! – barryhunter Jan 24 '18 at 16:21

1 Answers1

1

What you can do is add another attribute containing length of the string attribute and then sort first by the length ASC, then by the string ASC. This fixes the order in the example you've provided in the question: mysql> select * from idx_min order by l asc, myattribute asc; +------+-------------+------+ | id | myattribute | l | +------+-------------+------+ | 1 | 1a | 2 | | 4 | 2a | 2 | | 5 | 3a | 2 | | 2 | 100b | 3 | | 3 | 101c | 3 | | 6 | a200 | 3 | | 7 | c345 | 3 | | 8 | a345-2 | 5 | +------+-------------+------+ 8 rows in set (0.00 sec)

However you may want to have a200, c345, a345-2 (from your comment) before 1a. Then I guess the only way you can do it in Sphinx / Manticoresearch is by using UDFs (user defined functions). You need to create a function which will convert your string into a number given that a-z should have higher values than 0-9. Then you can use the result of the function in your query. Like this:

select *, natsort(myattribute) ns from idx_min order by ns asc;

Manticore Search
  • 1,462
  • 9
  • 9
  • Thank you for pointing me at user defined functions. I will definitely have a look at it. I will also try your provided solution regarding the `length` parameter. – koseduhemak Jan 30 '18 at 13:59