In the ICanUp Admin Posts table, sorting looked like a normal UI feature: click a column, send sort to the backend, and change the ORDER BY.
The date columns exposed a more important contract problem. The frontend worked with createdAt and publishedAt, while the backend sorting map used created_at and published_at.
The result was not only incorrect ordering. Sorting could end with a server error, and the generic sorting path allowed an external sort key to get too close to the SQL layer.
The Frontend Sends a Column ID, Not a Database Field
A sortable TanStack Table column has its own ID. For Posts, those IDs include publishedAt and createdAt.
When sorting changes, the frontend produces a value such as publishedAt:asc. That is the public UI contract received by the backend. The browser does not send posts.published_at and should not need to know the database structure.
const initialSortParts = typeof props.filters?.sort === "string" ? props.filters.sort.split(":") : []; const sortableIds = [ "id", "title", "status", "slug", "publishedAt", "createdAt",]; const sorting = ref( initialSortParts.length === 2col.accessor("publishedAt", { header: () => t("admin.posts.published_at"), cell: (info) => formatAdminDateTime( info.getValue(), locale.value, ) || "-", enableSorting: true,}); col.accessor("createdAt", { header: () => t("admin.common.created_at"), cell: (info) => formatAdminDateTime( info.getValue(), locale.value,The Contract Mismatch Was createdAt vs created_at
Before the fix, the backend date map used snake_case keys. The frontend sent createdAt, while the map knew created_at. The same mismatch existed between publishedAt and published_at.
This is a good example of why a UI column name, a JSON field, and a SQL column are not the same contract.
protected array $baseOrderColumns = [ 'id' => 'posts.id', 'title' => 'pt.value', 'slug' => 'posts.slug', 'status' => 'posts.status', 'created_at' => 'posts.created_at', 'published_at' => 'posts.published_at',];The Allowlist Translates Between the UI and SQL
After the fix, the mapping contains exactly the keys the Admin UI is allowed to send.
| Client sort key | SQL expression |
|---|---|
id |
posts.id |
title |
pt.value |
slug |
posts.slug |
status |
posts.status |
createdAt |
posts.created_at |
publishedAt |
posts.published_at |
The request now chooses only a logical key. The server always decides which real SQL field or expression corresponds to it.
protected array $baseOrderColumns = [ 'id' => 'posts.id', 'title' => 'pt.value', 'slug' => 'posts.slug', 'status' => 'posts.status', 'createdAt' => 'posts.created_at', 'publishedAt' => 'posts.published_at',];Both the Key and Direction Need Validation
A column allowlist solves only half of the problem. The part after the colon also comes from the request.
The direction therefore accepts only asc or desc. An unknown key, invalid direction, array instead of string, or malformed value should never reach SQL.
When that happens, the table simply falls back to a predictable default order.
protected function order(): void{ $sort = $this->request->input('sort'); [$key, $direction] = array_pad( explode( ':', is_string($sort) ? $sort : '', 2, ), 2, null, ); $column = $this->baseOrderColumns[$key]A request may choose one of the allowed sorting options. It should not construct the SQL sorting expression.
Why reorder Matters Here
builderInit() starts with the default ORDER BY posts.id DESC. That is useful for the normal list, but it creates a trap for server-side sorting.
If another orderBy(posts.published_at) is simply appended, the original sorting does not disappear. posts.id DESC remains the first criterion and the requested date becomes secondary.
reorder() clears the existing ORDER BY before applying the requested, validated sort.
protected function builderInit(): void{ $this->builder = Post::query() ->select($this->columns) ->leftJoin( 'post_translations as pt', function ($join): void { $join ->on( 'pt.post_id', '=', 'posts.id', ) ->where( 'pt.name', '=',Title Shows Why a Client Field Cannot Equal a Database Field
With dates, the difference looks simple: publishedAt maps to posts.published_at. The title field shows the real value of the allowlist.
The Posts table does not have a posts.title column. The localized value comes from post_translations, joined as pt. The public sort key title therefore maps to pt.value.
The UI should not know the translation table name, join alias, or localization storage strategy.
Date Columns Should Sort by Raw Datetime Values
The Admin UI displays dates through formatAdminDateTime(), but that presentation value is not used for sorting.
The backend orders directly by posts.created_at or posts.published_at. Formatted date text depends on locale and is intended for people, not for establishing chronological order.
it( 'sorts posts by raw publication datetime', function (string $direction): void { $later = Post::factory()->create([ 'published_at' => '2026-09-30 17:45:00', ]); $earlier = Post::factory()->create([ 'published_at' => '2026-09-30 08:00:00', ]); [$first, $second] = $direction === 'asc' ? [$earlier, $later]Equal Values Need a Tie-Breaker
Two Posts can have the same published_at. If SQL knows only that criterion, their relative order is not part of our contract.
The implementation therefore adds posts.id DESC after the main field. This makes the result deterministic even when timestamps are equal.
it( 'uses newest ID to break publication datetime ties', function (): void { $older = Post::factory()->create([ 'published_at' => '2026-09-30 12:00:00', ]); $newer = Post::factory()->create([ 'published_at' => '2026-09-30 12:00:00', ]); $this->get( route( 'admin.posts.index',The Most Important Test Sends Values That Must Never Become SQL
The happy path with publishedAt:asc proves the feature works. The reliability and security boundary is clearer when the request is deliberately wrong.
The tests send an unknown column, a real database column name, an SQL-looking expression, an invalid direction, multiple directions, and an array instead of a string.
Every case must keep the endpoint available, return the default ordering, and avoid using request input as an SQL expression.
it( 'falls back to newest post for unsupported sorting without using request input in SQL', function (string|array $sort): void { $older = Post::factory()->create(); $newer = Post::factory()->create(); $queries = []; DB::listen( static function ( QueryExecuted $query, ) use (&$queries): void { $queries[] =Sort State Must Survive Pagination and Filters
Server-side sorting does not end with a correct ORDER BY. If sort disappears after searching, filtering by status, or changing the page, the user suddenly gets a different order.
The regression coverage therefore checks both the database order and that filters.sort remains in the Inertia payload across pagination.
$params = [ 'sort' => 'slug:desc', 'search' => 'sorting', 'status' => PostStatusEnum::PUBLISHED->value, 'per_page' => 1,]; $this->get( route(What I Took Away From This Bug
At first this looked like a small bug affecting two date columns. In practice it exposed a weak point in the contract of the whole Admin DataTable.
A frontend sort ID is not a database column. A visible field may map to a snake_case column, a join alias, an aggregate, or another server-side expression. The backend should own that mapping.
After this case, I expect a good sorting contract to have five properties:
- every client key belongs to an explicit allowlist;
- direction is independently restricted to
ascanddesc; - unknown input never reaches SQL;
- the previous default order is cleared before custom sorting;
- equal values have a deterministic tie-breaker.
At that point the table is not only sorted correctly. The boundary between the UI and SQL is explicit, testable, and substantially safer.
