TL;DR when paginating it's best to always order your query using the primary key, a column with a unique constraint, or at very least a column which is reasonably likely to be different on each row, eg created_at.
Pagination uses limit and offset to return a subset of rows from a query, and that same query could be called multiple times if navigating between result pages. We must therefore ensure the rows are ordered consistently so upon each query call it moves through the result set as expected, otherwise rows may appear to the user to be missing or duplicated.
Remember that SQL can return rows in any order it likes, so even if we specified something like ->orderBy('name') if the values in the name column are identical across several rows we could end up with things sorted in a random order.
This is mostly likely to be of concern if there's a large number of rows and/or many identical values being ordered by, as when navigating between the pages the rows will appear to "move" between page numbers. This problem will become more apparent if the pagination row count is a low number.
Top comments (0)