Incorrect sorting by columns with non-unique value

[TheDoctor0]

• Laravel Version: 12.17.0
• Nova Version: 5.7.2
• PHP Version: 8.4.8
• Database Driver & Version: MySQL 8
• Operating System and Version: Ubuntu 24
• Browser type and version: Chrome
• Reproduction Repository: None

Description:

When ordering the table in Nova by a column that has the same value more than once, you will likely see the same record twice, or some not at all, when switching between pages.

The explanation is quite simple here, and it just comes down to how the database handles sorting in such cases. It does not guarantee that you will see the same order when limiting + offsetting (paginating) records. To mitigate that, a secondary column with unique values must be provided for sorting to ensure consistency, which Nova surprisingly does not do by default.

I found that this issue was already reported back in 2022, but closed: #4481

Detailed steps to reproduce the issue on a fresh Nova installation:

Add a Nova resource with a field that has multiple non-unique values, enough to have at least 2 pages, sort by that column, and switch pages. As a result, you will see some records appear for the second time, and some will be missing.

Fix:

Logic in applyOrderings needs to be changed to apply ordering by model key at the end by default.
I also added an option to specify default ordering for a resource using a static property called $orderBy,
following the general Nova convention. It was requested in #156.

protected static function applyOrderings($query, array $orderings): Builder
{
    $orderings = array_filter($orderings);

    if (empty($orderings) && property_exists(static::class, 'orderBy')) {
        $orderings = array_filter(static::$orderBy);
    }

    if (empty($orderings)) {
        return empty($query->getQuery()->orders) && ! static::usesScout()
            ? static::defaultOrderings($query)
            : $query;
    }

    foreach ($orderings as $column => $direction) {
        $query->orderBy($column, $direction);
    }

    return ! static::usesScout()
        ? static::defaultOrderings($query)
        : $query;
}

[crynobone]

Unable to reproduce the issue, please provide full reproducing repository based on fresh installation as suggested in the bug report template (or you can refer to https://github.com/nova-issues for example)

[TheDoctor0]

@crynobone have you actually tried to reproduce it in a way that I described?
It would become immediately obvious that there is an issue.

Even without it, I provided a rather lengthy explanation of why this bug exists.
This is the same issue as covered, for example, here or here.
It's clear that the current code does not handle it adequately.

I already fixed it in my project in the 2nd base class for resources with the overridden function posted above, and I won't waste time proving that the bug indeed exists.

[crynobone]

I prefer not to assume based on your description above, a reproduction repository make it easy to understand the issue and allows us to add it as a regression test.

[crynobone]

Additionally, Nova will need to make the use case compatible with MySQL, MariaDB, Postgres and MSSQL. Even if a solution exists, we still need to tests and verify that it doesn't cause any regression, or can even be implemented.

[TheDoctor0]

@crynobone, this comes down to the SQL standard, making it the default behavior across all SQL databases, including MySQL, MariaDB, Postgres, MSSQL, and even SQLite.
In case of ordering by a non-unique column, the actual order will depend on the scan and join plan types and the order on disk, making it non-deterministic.

If you want to reproduce and test it, create a table with a large enough dataset, like 100 rows. It can be a simple table with just an auto-increment column (id), and the second with a non-unique value like a one-letter string or a boolean.
Paginate the results by 25 or 50, and every time you will get missing or duplicated rows between pages in all database engines without ordering by the ID column as well.

If you still do not acknowledge this problem, then you can close this issue. I have fixed it in my project anyway.