Getting dublicated rows when using "hasOne" in model

[oberator]

I am using the Rappasoft Laravel Livewire Tables package to display a table of employees in my Laravel application. The table is supposed to display each employee along with their latest compensation details. Although the query works as expected in terms of fetching data, I am encountering an issue where each employee is displayed multiple times if they have more than one compensation record.

EmployeeTable Component (shortened):

<?php

namespace App\Livewire;

use App\Models\Employee;
use Illuminate\Database\Eloquent\Builder;
use Rappasoft\LaravelLivewireTables\DataTableComponent;
use Rappasoft\LaravelLivewireTables\Views\Column;


class EmployeeTable extends DataTableComponent
{
    public function builder(): Builder
    {
            return Employee::query();
    }

    public function configure(): void
    {
        $this->setPrimaryKey('id')
            ->setTableRowUrl(function ($row) {
                return route('employee.show', $row->id);
            });
        $this->setAdditionalSelects(['employees.id']);
    }

    public function columns(): array
    {
        return [
            /**
             * @var Column
             */
            Column::make("Firstname", "firstname")
                ->sortable()
                ->searchable(),
            Column::make("Lastname", "lastname")
                ->sortable()
                ->searchable(),
           Column::make("Compensation", "latestCompensation.amount")
                ->format(
                    fn($value, $row, Column $column) => number_format($value,0, '.', '.') . ' EUR')
                ->sortable(),
        ];
    }
}

Employee Model

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\HasOne;

class Employee extends Model
{
    public function latestCompensation(): HasOne
    {
        return $this->hasOne(Compensation::class)->latest('startdate');
    }
}

Problem:

When an employee has multiple compensation records, the EmployeeTable displays one row for each compensation record instead of displaying only the latest compensation. This results in duplicate employee rows in the table view.

What I've Tried:

Ensured that the latestCompensation relationship uses ->latest('startdate') to fetch the latest record.
Attempted to use a subquery in the builder() method to limit the join to the latest compensation record, but the issue persists.

Environment:

Laravel version: 10.48
PHP version: 8.1.2
Rappasoft Laravel Livewire Tables version: 3.4.5

Expected Behavior:

Each employee should appear only once in the table, showing the most recent compensation information.

Additional Information:

If anyone has experience with handling similar issues using Rappasoft Livewire Tables or has insights on how to effectively limit the result set to include only the latest related record, your assistance would be greatly appreciated.

Thank you for your help!

Edit:
Seems to be related to [[https://github.com//discussions/1004]

[lrljoe]

Yep, I've not yet got to the bottom of the best way to handle this, so there's no official fix as of yet!

The latest() is not something I actually use day-to-day, so any suggestions are appreciated.

[thomasgalue]

Out of the box the columns support hasOne, belongsTo, and MorphOne relationships for display, sorting, and searching. The component will automatically join the necessary tables.
https://rappasoft.com/docs/laravel-livewire-tables/v3/columns/relationships

So, if you are working with HasMany, because, if an Employee can have more that one compensation is a HasMany relationship, the table will show duplicated records for that, now, in my own app i don't handle employees but i have products with prices, it's almost the same structure, in your case you need to filter that relationship with another options.

I solve my problem a few weeks ago, my solution was to focus in the builder() function.
In my case I "hide" the records i don't want in the table with the softdeleted() Trait. After some research and tests, I only changed the builder() function in my datatable class.

From this:
protected $model = Product::class;

to this:

return Product::query()
            ->with('pricing', 'setting', 'stock')
            ->where('stock.deleted_at', '=', null)
            ->where('profit.deleted_at, '=', null)
            ->where('pricing.deleted_at', '=', null);

Now my price Column looks like this:

Column::make("Price", "pricing.sell_price"),

Of course, for other parts of my app I need to use the withTrashed() method to get all the records, but until this packages add support for HasMany relationships "Out of the box", this is the way.

Maybe is not the best, but is the first solution that works for me.
I hope it helps to find your own way to fix the problem.
Sorry for my bad english, spanish is my mother language.

[lrljoe]

You can use a HasMany/MorphMany and return a list of values with an ArrayColumn

The issue here is when you're trying to return a "latest" of a HasMany/MorphMany

[thomasgalue]

Then I need to read more about it. Thanks!

[lrljoe]

Check my answer below which gives you an interim solution at least :)

[lrljoe]

This approach should work for the interim.

This assumes that

"User"
HasMany
"Things"

Things having:
id
name
user_id (foreign key for User)

And the "latestThing" is the most recent created_by "Things"

            Column::make('Latest Thing')
            ->label(
                fn ($row, Column $column) => $row->things->first()->name
             ),

And in your builder:

    public function builder(): Builder {

        return User::query()->with(['things' => function ($query) {
                $query->select(['id','user_id','name'])->orderBy('created_at', 'desc')->limit(1);
        }]);
    }

[oberator]

Thank you for your help and the suggested workaround.

In fact, I’ve implemented a similar solution in the production system. However, when I use the “Label” method, I cannot sort the columns.
This is why I’m trying to resolve the problem directly within the query itself.

During my tests, I noticed that “eager loading” remains activated, even when I disable it in the configuration section.

public function configure(): void { $this->setEagerLoadAllRelationsStatus(false); }

While this shouldn’t be a major issue—since the linking is done via a “left join,” which should prevent duplicates from appearing in the relationships—I’m still encountering them.

Thanks again for your assistance.
I plan to investigate further when I have more time to understand why these duplicates are occurring.

[lrljoe]

@oberator -

LABEL Columns are not sortable with the default sortable() method.

Instead, you should define how the sort should work with a callback, e.g.

    ->sortable(
        fn(Builder $query, string $direction) => $query->orderBy('things.name', $direction)
    ),

Otherwise - please share your full Table Component

AND

The Underlying Model/Relations

[oberator]

How could I have overlooked the fact that the sortabel method also allows a callback. Thank you very much for this hint.

My column now looks like this and works as expected:

Column::make('Compensation')
    ->label(function ($row) {
        return (number_format($row->getCompensation(Carbon::now()->toDateString()), 0, '.', '.') . ' EUR');
    })
    ->sortable(
        fn(Builder $query, string $direction) =>
        $query->orderBy(
            Compensation::select('amount')
                ->whereColumn('compensations.employee_id', 'employees.id')
                ->orderBy('startdate', 'desc')
                ->limit(1),
            $direction
        )
    ),

[lrljoe]

Awesome, glad it's all working for you now :)

[lrljoe]

@oberator - There will almost certainly be more efficiencies than your current approach as well by the way.

While it will work, the native of Laravel/Livewire may cause things to slow down as everything starts to grow.

I'd be happy to give you more tips on the official Discord, you'll just have to catch me at a moment when I have 15 minutes to spare.