Column built from hasOne() relationship results in duplicate rows

[lrljoe]

Originally from dirtbikr

Example User model has a relationship with a Purchase model:

    public function latestPurchase()
    {
        return $this->hasOne(Purchase::class)->latestOfMany();
    }

Users may have many purchases, outside of the package, the latestPurchase relationship correctly returns one result. Within the package, it returns as many purchases as exist.

public function columns(): array
    {
        return [
            Column::make("ID", "id")
                ->sortable()->searchable(),
            Column::make("Name", "name")
                ->sortable()->searchable(),
            Column::make("Email", "email")
                ->sortable()->searchable(),
            Column::make("State", "state")
                ->sortable(),
            Column::make("Last Purchase", "latestPurchase.created_at"),
        ];
    }

Query outside of the package is as follows: An initial select for the users:

select
    `id`,
    `name`,
    `email`,
from
    `users`

Then a self-joined query for the latest purchase:

select
    `purchases`.*
from
    `purchases`
    inner join (
        select
            MAX(`purchases`.`id`) as `id_aggregate`,
            `purchases`.`user_id`
        from
            `purchases`
        where
            `purchases`.`user_id` in (1, 2, 3, 4, 5)
        group by
            `purchases`.`user_id`
    ) as `latestOfMany` on `latestOfMany`.`id_aggregate` = `purchases`.`id`
    and `latestOfMany`.`user_id` = `purchases`.`user_id`

Query within the package is as follows:

select
    `users`.`id` as `id`,
    `users`.`name` as `name`,
    `users`.`email` as `email`,
    `purchases`.`created_at` as `latestPurchase.created_at`
from
    `users`
    left join `purchases` on `purchases`.`user_id` = `users`.`id`
limit
    10 offset 0

Example repo: https://github.com/lrljoe/livewire-tables-latestofmanyissue/tree/Showing-Issue Interim Fix Interim fix is as follows, with recommendation to use Builder to eager load the relationship. https://github.com/lrljoe/livewire-tables-latestofmanyissue/blob/Fixed-Issue/app/Http/Livewire/UserTable.php

Column::make('Last Purchase', 'id')
            ->format(
                fn ($value, $row, Column $column) =>  $row->latestPurchase->created_at
            ),

Originally posted by @dirtbikr in https://github.com/rappasoft/laravel-livewire-tables/discussions/1004

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[apydevs]

Same issue here

[lrljoe]

The fix for this was actually a relationship level one in the model if I remember correctly! Will take a look in a week or two.

Which Laravel version are you using?

[apydevs]

Laravel Framework 9.52.15 Livewire V2.12.6

Just to put abit more context to this

Relationships `

   public function businessLicence(): \Illuminate\Database\Eloquent\Relations\HasOne
    {
        return $this->hasOne(Licence::class, 'tenant_id', 'id')->latest('created_at');
    }

public function deviceLicence(): \Illuminate\Database\Eloquent\Relations\HasOne
{
    return $this->hasOne(DeviceLicence::class,'tenant_id','id')->latest('created_at');
}

**Livewire Table Component**

   public function configure(): void
    {
            return [
              Column::make('ID', 'id')->isHidden(),
              Column::make('Tenant Name', 'tenancy_name') ->sortable()->searchable(),
              Column::make('Businesses used','businessLicence.current_used_total')->sortable()->unclickable()
              ->deselected()->searchable()
              ]
    }

   public function columns(): array
    {

        return [
             Column::make('ID', 'id')->isHidden(),
              Column::make('Tenant Name', 'data->tenancy_name') ->sortable()->searchable(),
              Column::make('Business used','businessLicence.current_used_total')->sortable()->unclickable()
              ->deselected()->searchable()]
`
public function builder(): Builder
    {
        return Tenant::query()
            ->with(['businessLicence','deviceLicence']);
}

I have multiple references to the tenant id , within the business licence table (Licence::).

if i dd(Tenant::query() ->with(['businessLicence','deviceLicence'])->get()); I see the last records in the relationships as expected and no duplication

Any help would be greatly appreciated

[mohamad-supangat]

@rappasoft can you help me how to fix this

i have same issue there in hasOne relationship only