Increase amount of order by fields

[cappadaan]

Currently the max amount of fields to order by is 5.
It would be really useful to have this number increased.

Hope this is something to consider.

[sanikolaev]

It's not a big deal to change the behaviour, but could you please elaborate more on why it's important for you? I.e. what is the case when it's important to be able to sort by more than 5 attributes.

[cappadaan]

We simply use more than 5 order by's to sort the search results because of the business logic on our website, but are restricted now. If there is no real reason for the limitation than it would be useful if the amount was increased.

[sanikolaev]

BTW have you tried CONCAT() as a workaround? E.g.:

mysql> select i, i1, i2, i3, i4, i5, concat(to_string(i),',',to_string(i1),',',to_string(i2),',',to_string(i3),',',to_string(i4),',',to_string(i5)) s from t order by s desc;
+------+------+------+------+------+------+-------------+
| i    | i1   | i2   | i3   | i4   | i5   | s           |
+------+------+------+------+------+------+-------------+
|    2 |    3 |    4 |    5 |    6 |    7 | 2,3,4,5,6,7 |
|    1 |    2 |    3 |    4 |    5 |    6 | 1,2,3,4,5,6 |
+------+------+------+------+------+------+-------------+
2 rows in set (0.01 sec)

mysql> select i, i1, i2, i3, i4, i5, concat(to_string(i),',',to_string(i1),',',to_string(i2),',',to_string(i3),',',to_string(i4),',',to_string(i5)) s from t order by s asc;
+------+------+------+------+------+------+-------------+
| i    | i1   | i2   | i3   | i4   | i5   | s           |
+------+------+------+------+------+------+-------------+
|    1 |    2 |    3 |    4 |    5 |    6 | 1,2,3,4,5,6 |
|    2 |    3 |    4 |    5 |    6 |    7 | 2,3,4,5,6,7 |
+------+------+------+------+------+------+-------------+
2 rows in set (0.01 sec)

[cappadaan]

this solution doesnt work for combinations of desc and asc

[barryhunter]

You can invert the directionality of numeric attributes pretty easily

concat(to_string(10000-i),... 

Actully staying as a numeric value may be worthwhile as manticore is not doing 'natural sorting' so numbers would technically need zero padding converted to string.

I've done something liek this before...

select id,  i3*100000000+i2*10000+i1 as compound from ... order by compound asc

Can even fudge the expression, to make it non-linear sorting.

In fact one case, there was a couple of very common sort orders so actully created a real attribute (in sql_query) that could sort by, rather than doing the 'expression' at runtime.

[cappadaan]

Or they can just increase the amount of order by, which seems 100x easier and simpler. But I have no insight in the code.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions. Feel free to re-open the issue in case it becomes actual.