Add option to append final order column to get stable sort for pagination

[karlshea]

I'm having an issue with pagination where I have disappearing rows between pages because only ordering on one column doesn't achieve a stable sort.

Would it be possible to add an option for a column name to append after every sort (for instance, the ID of the row) so the rows come back in the same order each time?

[Chumper]

i am not sure if i understand your issue, can you elaborate a little bit more an show examples?

[karlshea]

Sure. The problem is that if you have a couple of rows that have the same value that you're sorting on, MySQL (or any database actually) can return those rows in any order between queries. The side effect is that going from one page to the next loses rows.

Example

Let's say I click sort on last name.

4 rows (unpaged):

Last	First	Date
Smith	John	1-1-2015
Smith	John	1-2-2015
Smith	John	1-3-2015
Smith	John	1-4-2015

2 per page

Page 1

Last	First	Date
Smith	John	1-1-2015
Smith	John	1-2-2015

Page 2

Last	First	Date
Smith	John	1-2-2015
Smith	John	1-4-2015

Since the order of the rows between queries isn't guaranteed, I'm getting the row with 1-2-2015 as the last row on page one, and the first row on page two. 1-3-2015 never appears in the results.

If I could specify a column that would always be appended to the order clause in the query, I could specify "date" (or maybe the primary key) in this example and then it would be a stable sort since the rows would be ordered on unique values.

I found the accepted answer on this StackOverflow question also does a good job of explaining it.

[Chumper]

So as far as i see it would aslo be an option to allow multiple orderings on the serverside as well as enable the option for a stable sort column like this:

->setStableOrderOn("your_unique_column")

so it will be added to the ordering clause

[karlshea]

Yeah, that would work. I think as long as whatever the column name is gets added to the builder right before returning it in doInternalOrder() in QueryEngine, that would solve the problem. I think it would be possible as well for CollectionEngine, but I'm not totally sure.