Search code examples
mysqlknppaginatorbundle

Ordered pagination with MySQL

UPDATE: In the following question, I thought when you select rows, MySQL create a kind of row index, and then the LIMIT and OFFSET clause cuts off this list by its numbers. The problem is I'm using wrong values or field combination to sort the rows as the have almost the same value.

Here is my mistake, assuming to much "intelligence" form MySQL ;-)

The solution is always to have a more reliable sorting field as a fallback, like the ID, like so ORDER BY priority DESC, id ASC

This is a strict MySQL question. Why does it seem the LIMIT OFFSET clause is applied before the ORDER BY? or what am I missing?

Here is the example, first we select a list of rows ordered by a field called priority:

SELECT d0_.name, d0_.id AS id_0, d0_.priority AS priority_1 FROM destination d0_ WHERE d0_.active = 1 ORDER BY d0_.priority DESC;

The results looks like this:

enter image description here

Then I want to select the first 10 rows from this list using the following query:

SELECT d0_.name, d0_.id AS id_0, d0_.priority AS priority_1 FROM destination d0_ WHERE d0_.active = 1 ORDER BY d0_.priority DESC LIMIT 10 OFFSET 10;

And I've got this result:

enter image description here

Why does not the list goes from "Grandvalira" to "Sierra nevada"?

The problem of this, is not the actual order but some rows are never reached! like "Vallnord Ordino-Arcalís". As I change the OFFSET value, it does not go through all the rows, and it event repeats some rows.

This is the basic question. But this is giving me problems at the end when using the "KnpPaginatorBundle (2.5.3)" of Symfony. I thought was a problem of the php code, but mysql queries are giving this unexpected results for me.

Any help or clue of whats going on?

Thanks!

Solution

  • You are not getting the results you are expecting because your data has many rows with the same value for priority.

    When you use 'order by' on priority, all the rows with priority can come in any order. There is no guarantee about the ordering with the same value of priority. To resolve the tie, you can add additional fields to your order by clause. Depending on your choice you might choose to include name or id field in the 'order by' clause.