Column built from hasOne() relationship results in duplicate rows

[dirtbikr]

My User model has a relationship with my Purchase model:

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

Our users have many purchases, and I am getting one row per purchase instead of one row per user. If user "Brad Pitt" made three purchases with us, three identical (except for the "Last Purchase" date) rows appear.

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"),
        ];
    }

Is there anything I can do to stop this or enforce one row per user?

[lrljoe]

Can you post your full Component please, should make diagnosing it a little easier.

[dirtbikr]

It's very bare bones. I created an example repo with a fresh Laravel installation, Breeze, Alpine, and Livewire Tables.

https://github.com/dirtbikr/livewire-tables-test

The project uses sail, so you can pull it down, rename .env.example to ..env, then run the command to pull the dependencies:

docker run --rm \
    -u "$(id -u):$(id -g)" \
    -v "$(pwd):/var/www/html" \
    -w /var/www/html \
    laravelsail/php81-composer:latest \
    composer install --ignore-platform-reqs

You can then run ./vendor/bin/sail up -d, then ./vendor/bin/sail artisan migrate --seed to migrate with the seeder that creates a user with two purchases and the creds testuser@testuser.com/password.

It ends up looking like this:

Since you also asked for the full component, here it is from the test project linked above:

<?php

namespace App\Http\Livewire;

use Rappasoft\LaravelLivewireTables\DataTableComponent;
use Rappasoft\LaravelLivewireTables\Views\Column;
use App\Models\User;

class UserTable extends DataTableComponent
{
    protected $model = User::class;

    public function configure(): void
    {
        $this->setPrimaryKey('id');
    }

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

The dashboard view:

<x-app-layout>
    <x-slot name="header">
        <h2 class="font-semibold text-xl text-gray-800 leading-tight">
            {{ __('Dashboard') }}
        </h2>
    </x-slot>

    <div class="py-12">
        <livewire:user-table />
    </div>
</x-app-layout>

The relationship:

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

I feel this may not be expected behavior?

[lrljoe]

Firstly, thank you for doing that, it made my life much easier in replicating the issue!!!

Your issue is certainly not what I'd expect to happen! I've opened an issue so that it can get fixed.

In the interim, the way I'd recommend doing it, is the following, ignore the reference to "id", it's just to avoid the package trying to do unnecessary joins. I've created a pull request into your example repository so that the below makes more sense!

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

However! When you do so, I'd recommend implementing the Builder approach, and using a with to reduce the number of queries.

Ensure you reference the Eloquent builder at the top of the page:
use Illuminate\Database\Eloquent\Builder;

Remove your reference to "protected $model = User::class" and add the following function:

/**
 * @return Builder
 */
public function builder(): Builder
{
    return User::query()->with('latestPurchase');
}

In the meantime I'll take a look at what's going on!

Notes on findings so far:

Can see the query firing which is the following (which isn't the right join anyway)

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

This is what I'd have expected to see, and what happens when you return the relevant values outside of the table component:

select
    `id`,
    `name`,
    `email`,
from
    `users`
Then
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`

[dirtbikr]

Thanks for the response!

I tried the band-aid you suggested, but it also appears to have its own problems.

Since "id" is being passed in for "Last Purchase" when sorting the "Last Purchase" column, it actually sorts by "id" (the sorting pill title confirms this, and passing ->setSortingPillTitle('Last Purchase'), on the "Last Purchase" column has no effect.

If I take away the ->sortable() property on the "ID" column, the "Last Purchase" column will no longer sort, so I am quite certain it's just sorting by ID when clicking the "Last Purchase" column header. This issue was not immediately apparent in my small example project, and I apologize for that; I should have loaded it with more data.

I appreciate you creating an issue based on this, and I hope this provides some more information to fix this functionality. If you have any other ideas on how to work around this and make the "Last Purchase" column sortable, I'm all ears. I'm also happy to test or provide any other information or data you may need to help with development 🤙.