When sorting by recorded_date for example, if two dates are equal then the database is free to choose which order the rows are returned in.
This is not an issue for a single request, but when a user pages through hundreds of pages of results, where sub-groups (in this case of sizes up to 6 or more items) are unstably sorted, the end result is duplication of some records, and omission of others.
I think the filtering API should add an explicit secondary sort to any filter query to ensure stable sorting in sub-groups always occurs. I.e. ORDER BY recorded_date, id instead of ORDER BY recorded_date whenever order_by is used in filter requests.
Alternative idea: implement cursor-based searches. Probably too costly to be useful.
When sorting by
recorded_datefor example, if two dates are equal then the database is free to choose which order the rows are returned in.This is not an issue for a single request, but when a user pages through hundreds of pages of results, where sub-groups (in this case of sizes up to 6 or more items) are unstably sorted, the end result is duplication of some records, and omission of others.
I think the filtering API should add an explicit secondary sort to any filter query to ensure stable sorting in sub-groups always occurs. I.e.
ORDER BY recorded_date, idinstead ofORDER BY recorded_datewheneverorder_byis used in filter requests.Alternative idea: implement cursor-based searches. Probably too costly to be useful.