Sphinx and natural sorting


Warning: count(): Parameter must be an array or an object that implements Countable in /home/styllloz/public_html/qa-theme/donut-theme/qa-donut-layer.php on line 274
0 like 0 dislike
3 views
Such a task is: implement a sort (preferably organic) in the sphinx attribute.

The problem is that in Sphinx Delta indexes, sorting by attribute sql_attr_str2ordinal is absolutely not suitable. Because that's what we said documentation: Note that the ordinals are by construction local to each index, and it's therefore impossible to merge ordinals while retaining the proper order. The processed strings are replaced by their sequential number in the index they occurred in, but different indexes have different sets of strings. For instance, if the 'main' index contains strings "aaa", "bbb", "ccc", and so on up to "zzz", they'll be assigned numbers 1, 2, 3, and so on up to 26, respectively. But then if 'delta' only contains "zzz" the assigned number will be 1. And after the merge, the order will be broken.

Is the following idea:
to calculate the numerical equivalent of the row, calculate the name in the first 8 characters
But with this algorithm:
In fact, we are dealing with a limited range of characters — digits, Latin and Russian alphabets in lower case (68 characters). This character set can be represented as number 68, we used the hexadecimal number system. Everything we do, we set the number of 68, we used the hexadecimal 10 hexadecimal. The symbols we translate into decimal codes, and then consider the formula a(n) * (68 ** 0) + a(n-1) * (68 ** 1) +... + a(1) * (68 ** n)

Question: is it right? Can anyone have sensible suggestions, all in the ideal would be natural sorting.
by | 3 views

1 Answer

0 like 0 dislike
probably in the section q&a of the pros do not look =)
\r
tentatively agreed to do so, but it is clear that natural sort is not seen, because this is necessary anyway to compare all strings with each other
a function in postgresql to convert a string to a number, who could be useful:
\r
CREATE OR REPLACE FUNCTION string_ordinal(str character varying) RETURNS double precision AS $BODY$ DECLARE _norm VARCHAR; _ln SMALLINT; _max_ln SMALLINT; i SMALLINT; o DOUBLE PRECISION; CHAR sym; code SMALLINT; norm_code SMALLINT; BEGIN _max_ln := 100; _norm := regexp_replace(lower(str), '[^A-Za-z0-9a-za-Eee]', ", 'g'); _ln = length(_norm); IF _ln < _max_ln THEN _norm = rpad(_norm, _max_ln, '.'); _ln = length(_norm); END IF; o := 0.0; FOR i IN 1.._ln LOOP sym := SUBSTRING(_norm FROM i FOR 1)::CHAR; code := ascii(sym); norm_code := 0; IF code = 46 THEN norm_code := 1; END IF; IF code >= 48 AND code <= 57 THEN -- 0-9 norm_code := code - 46; END IF; IF code >= 97 AND code <= 122 THEN -- a-z norm_code := code - (96 - 10); END IF; IF code >= 1072 AND code <= 1105 THEN-and-I + e IF code = 1105 THEN-ie the code is greater than I, it's bad code := 1078; ELSIF code > 1077 THEN code := code + 1; END IF; norm_code := code - (1071 - 36); END IF; o := o + norm_code * power(68, (_ln - i)); END LOOP; RETURN o; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; 
by

Related questions

0 like 0 dislike
2 answers
asked Mar 22, 2019 by DoctorDemon
0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
110,608 questions
257,186 answers
0 comments
27,212 users