Laravel Nova sorting oneToMany shows duplicates

Question

When I sort my related entities, I get duplicate values, when there are null values or same values in the column being sorted out.

The problems comes up in the Index page of the relation, when I click on "Next" : in the next 5 results of the relationship, there are results that were already there in the previous 5 results. The pagination seems to be broken.

For example, if I try to sort on the column "Total Applications", and in the corresponding field applies there are null values among the result set, then I get duplicated results on the subsequent pages when I click on "Next".

Other example : I try to sort on the column "Unpublished At", and there are many values with the exact same unpublished_at date. Then I get duplicated results on the next pages.

It looks to me that Nova is not capable of handling properly the logic "sorting/pagination" when there is no differences in the column being sorted. In that case, it should sort by another column, let's say the ID I think, which is always unique.

EDIT : I would like to add that JobOnJobboard is based on a pivot table ('instances_offers') which have other meaningful domain information. But I am linking Job to it as a simple HasMany relationship (not as a BelongsToMany relationship), because I don't need to access the other side of the Many To Many relationship. I just need the information on the pivot table.

Any Idea ?

In this image, some of the highlighted IDs on the left will be present in the next 5 results as well, which is very confusing :

---

Laravel 6.17.1
Nova 2.12

App\Job => the entity being related

App\JobOnJobboard => the relationship

---

App\Nova\Job :

class Job extends Resource
{
    /**
     * The model the resource corresponds to.
     *
     * @var string
     */
    public static $model = 'App\Job';

    /**
     * The single value that should be used to represent the resource when being displayed.
     *
     * @var string
     */
    public static $title = 'title';

    /**
     * The per-page options used the resource index.
     *
     * @var array
     */
    public static $perPageOptions = [10, 20, 25];

    public function fields(Request $request): array
    {
        return [
            HasMany::make('JobOnJobboard')
                ->hideFromIndex(),
        ];
    }
}

App\Nova\JobOnJobboard :

class JobOnJobboard extends Resource
{
    /**
     * The model the resource corresponds to.
     *
     * @var string
     */
    public static $model = 'App\JobOnJobboard';

    /**
     * The columns that should be searched.
     *
     * @var array
     */
    public static $search = [
        'id',
    ];
    public static $title = 'id';


    /**
     * Get the fields displayed by the resource.
     *
     * @return array
     */
    public function fields(Request $request)
    {
        return [
            ID::make()->sortable(),
            Text::make('Hits', 'hit')->readonly(true)->sortable(),
            Text::make('Total applications', 'applies')->readonly(true)->sortable(),
            Text::make('Status')->readonly(true)->sortable(),
            DateTime::make('Published At')
                ->readonly(true),
            DateTime::make('Unpublished At')
                ->readonly(true),
        ];
    }
}

App\Job :

class Job extends Model
{
    use SoftDeletes;
    use Filterable;
    use HasJsonRelationships;

    protected $table = 'offers';

    protected $dates = [
        'created_at',
        'updated_at',
        'archived_at',
        'published_at',
        'unpublished_at',
    ];

    protected $casts = [
        'published_at' => 'date:Y-m-d H:i:s',
        'unpublished_at' => 'date:Y-m-d',
    ];

    protected $appends = [
        'location_iso',
    ];

    public function jobOnJobboard(): HasMany
    {
        return $this->hasMany(JobOnJobboard::class, 'offer_id', 'id');
    }
}

App\JobOnJobboard :

class JobOnJobboard extends Pivot
{
    /**
     * {@inheritdoc}
     */
    protected $table = 'instances_offers';

    /**
     * {@inheritdoc}
     */
    protected $dates = [
        'created_at',
        'updated_at',
        'published_at',
        'unpublished_at',
    ];

    public function job(): BelongsTo
    {
        return $this->belongsTo(Job::class, 'offer_id');
    }
}

Answer 1

So I found the solution. The problem was that MySQL positions, when using ORDER BY, is non-deterministic. "And since you execute a new query each time you access a page, the rows will be "scrambled" anew".

Source of that information : https://stackoverflow.com/a/27803177/10767428

Since all rows had the same values in the ordered column (be it "null" or some other value), MySQL could not keep consistency between each page. Hence the duplicated results.

To solve that, following above stackoverflow response, I did on my Base Nova Resource :

app/Nova/Resource.php

abstract class Resource extends NovaResource
{
    public static function indexQuery(NovaRequest $request, $query): Builder
    {
        $table = $query->getModel()->getTable();

        if (Schema::hasColumn($table, 'id')) {
            return $query->orderBy('id');
        }

        return $query;
    }

}

I am just checking that my current model table has the "id" column, and add it as a new order item if it is the case.

That way, in case I have rows with same values, MySQL will still order them by ID column.

---

For additional information, the sql query before this patch was :

select * from `instances_offers` where `instances_offers`.`offer_id` = ? and `instances_offers`.`offer_id` is not null order by `applies` asc limit 6 offset 5

After the patch, it becomes :

select * from `instances_offers` where `instances_offers`.`offer_id` = ? and `instances_offers`.`offer_id` is not null order by `applies` asc, `id` asc limit 6 offset 5

Notice the id asc that add an aditional layer of ordering for preventing the results to be 'scrambled' in next page.