Add way to specify secondary sort on unique column

[trevorgehman]

Problem

One issue with sorting on a SQL column that's not unique, such as first_name, is that the order of returned rows isn't predictable if there are duplicate values. This can cause issues with pagination, where the same row is returned on multiple pages, even without any data changing in the database.

I've seen this happen frequently when sorting on relationship counts, where a lot of posts have comments_count: 0 for example.

Solution

It would be nice to have a way to always apply a secondary sort on a unique column. So if the http query is ?sort=-name then the database query looks like this:

->orderBy('name', 'desc')
->orderBy('id', 'desc');

Currently I'm just creating a bunch of custom sorts, but it would be nice to have something like:

->allowedSorts('name', 'comments_count')
->secondarySort('id');

Alternatives

I've thought about manually adding the sort:

->allowedSorts('name', 'comments_count')
->orderBy('id', 'desc')

The problem with doing that is that I'd need to specify the sort direction, and SQL performance isn't great if the sort directions are different when you're sorting on multiple columns. Here's more information on that:

Sort in one direction. If you have ORDER BY col1, col2 it can be optimized using index, if you have
ORDER BY col1 DESC, col2 DESC same thing, however if you would have ORDER BY col1, col2 DESC MySQL will have to use filesort.
https://www.percona.com/blog/2006/09/01/mysql-order-by-limit-performance-optimization/

[trevorgehman]

Thinking more about this:

1. What would be a good name? Ideas:

• ->secondarySort('id')
• ->uniqueSort('id')
• ->additionalSort('id')

2. Functionally, I think the best way for this to work is that it is:

• Only applied if there is another sort, default or otherwise
• It takes the sort direction of the primary sort.

Would you be open to a PR for this functionality? We could explain in the sorting docs when you would want to use this.

[hivokas]

1. What about ->deterministicSort('id') for the name?
2. The signature could be public function deterministicSort($sort, string $fallbackDirection = 'asc')

$fallbackDirection will be used if neither sorts are provided nor default sort is specified.

[AlexVanderbist]

Can't this be solved with 1 custom sort as well? Extending SortsField and adding the additional orderBy? It could look something like this:

->allowedSorts([
     AllowedSort::custom('comments_count', new DeterministicSort('id')),
])