Improve tags ordering

[duker33]

• Currently we sort tags by names lexicographically. But in some cases we should sort numerically. See the screen.
• Search mech binded to not necessary (as i see now) TAGS_ORDER settings option.
• Maybe remove TagQuerySet.filter_by_products and _.exclude_by_products from SE. We already done it at stb in catalog.py:20: Adapt views to Options stroyprombeton#419

Screen taken from this link: https://www.shopelectro.ru/catalog/categories/akkumuliatory-270/

[ArtemijRodionov]

We can make a plain query to postgres, that will order by chars and than by floats:

select * from shopelectro_tag
  order by
    substring(name, '[a-zA-Zа-яА-Я]+'),
    substring(name, '[0-9]+\.?[0-9]*')::float;

  id   |    name    | position | group_id |                 uuid                 |         slug
-------+------------+----------+----------+--------------------------------------+----------------------
     7 | Apple      |        1 |        4 | 7d2c7d35-6059-4764-b015-66ade0a86dcf | apple
     8 | Microsoft  |        2 |        4 | 9cc667df-3ef3-4c48-bdbb-684a57b3840e | microsoft
     3 | 1.2 А      |        1 |        2 | 7c1f2b04-f576-4b81-a83c-e61ced91a14c | 1-2-a
     4 | 10 А       |        2 |        2 | 2dc8dcb5-06e7-4190-8923-acd31473d28a | 10-a
     1 | 6 В        |        1 |        1 | ab972e93-45ee-4cfe-a356-9500ab8d5031 | 6-v
     2 | 24 В       |        2 |        1 | 41b3a065-9dcb-4801-b821-c921743313ea | 24-v
     5 | 7.2 Вт     |        1 |        3 | 510507d1-9255-4efe-9b40-878b78d40e8e | 7-2-vt
     6 | 240 Вт     |        2 |        3 | 170c32a2-e49d-4e04-bcc1-4ea4bdbd326d | 240-vt

Also to avoid performance gaps we can create an index for the expression:

se_prod=# explain ANALYSE select * from shopelectro_tag order by name;
                                                                  QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using tag_ordering on shopelectro_tag  (cost=0.42..8240.48 rows=100009 width=53) (actual time=0.024..821.284 rows=100009 loops=1)
 Planning time: 0.174 ms
 Execution time: 1504.217 ms
(3 rows)

se_prod=# explain ANALYSE select * from shopelectro_tag order by substring(name, '[a-zA-Zа-яА-Я]+'), substring(name, '[0-9]+\.?[0-9]*')::float;
                                                                   QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using tag_ordering_2 on shopelectro_tag  (cost=0.42..9004.64 rows=100009 width=53) (actual time=0.039..1205.072 rows=100009 loops=1)
 Planning time: 0.378 ms
 Execution time: 1915.029 ms
(3 rows)

The difference between order by name and this expression isn't to big for 100 000 rows, so it won't be an issue for our 773 tags :) It will take ~3 ms

[ArtemijRodionov]

I am going to implement the query in this issue

[0pdd]

@duker33 2 puzzles #282, #283 are still not solved.

[0pdd]

@duker33 2 puzzles #282, #292 are still not solved; solved: #283.

[0pdd]

@duker33 2 puzzles #282, #292 are still not solved; solved: #129, #283.

[0pdd]

@duker33 2 puzzles #292, #294 are still not solved; solved: #129, #282, #283.

[0pdd]

@duker33 the puzzle #292 is still not solved; solved: #129, #282, #283, #294.