Ordering and Pagination

Ordering, Grouping, Limits, and Pagination

orderBy(), orderByDesc(), latest() (by created_at), inRandomOrder() and reorder() sort; groupBy() with having() or havingRaw() groups as in GROUP BY, HAVING, and ROLLUP; limit() and offset() (or take() and skip()) page by hand. Laravel 2,157 's MySQL 524 connection sets a strict sql_mode that includes ONLY_FULL_GROUP_BY, so an ungrouped column fails as in MySQL.

Offset pagination and cursor pagination over the catalogPHP

DB::listen(fn ($e) => print(wordwrap($e->toRawSql(), 95)."\n"));
$page = DB::table('products')->orderBy('price')->paginate(3, ['sku'], 'page', 2);
echo "page {$page->currentPage()} of {$page->lastPage()}, {$page->total()} rows\n";
$byPrice = fn () => DB::table('products')->orderBy('price')->orderBy('id');
$next = $byPrice()->cursorPaginate(3, ['id', 'sku', 'price'])->nextCursor();
echo collect($byPrice()->cursorPaginate(3, ['sku'], 'cursor', $next)->items())->pluck('sku');

Output

select count(*) as `aggregate` from `products`
select `sku` from `products` order by `price` asc limit 3 offset 3
page 2 of 3, 8 rows
select `id`, `sku`, `price` from `products` order by `price` asc, `id` asc limit 4
select `sku` from `products` where (`price` > '29.00' or (`price` = '29.00' and (`id` > 3)))
order by `price` asc, `id` asc limit 4
["BK-UX-01","BK-PHP-01","BK-LNX-01"]

paginate() runs a count(*) plus the page, normally reading ?page= from the request; simplePaginate() skips the count. Both use OFFSET, which walks past every skipped row. cursorPaginate() is Keyset Pagination's keyset pagination: the cursor is base64 JSON of the last row's sort values, and an index on (price, id) seeks straight to them. It needs a unique final sort column, all sort columns selected (without price the cursor held null), and gives up page numbers. All three render links with {{ $page->links() }}.

MENU

Ordering and Pagination