Custom Column with ManyToMany Relationship

Hi everyone! 😊

I'm working with Filament PHP and have a many-to-many relationship between Machines and Tests:

Machine belongsToMany Tests

Test belongsToMany Machines

The goal is to track regular inspections of machines. Each Test has a next due date (next_time). In the Machines table, I want to display the next due date (defined from the latest test) for each machine. Ideally, I should also be able to sort by this column.

What I have so far:

In my Machine model, I retrieve the last test like this:

public function last_test(): ?Test
{
    return $this->tests()
        ->orderBy('next_time', 'desc')
        ->first();
}

I also created a custom Filament Table Column to display the next due date and link to the latest test:

use Exception;
use Filament\Facades\Filament;
use Filament\Tables\Columns\TextColumn;
 
class NextTestColumn extends TextColumn
{
    protected iterable $viewTypes = ['view', 'edit'];
 
    protected function setUp(): void
    {
        parent::setUp();
        $this->state('-');
        $this->setUpLink();
    }
 
    protected function setUpLink(): void
    {
        $this->url(function ($record) {
            $relatedRecord = null;
            if ($record === null) {
                return null;
            }
            $relatedRecord = $record->next_test();
            if ($relatedRecord === null || $relatedRecord->next_time === null) {
                return null;
            }
            $this->state($relatedRecord->next_time->format('d.m.Y') ?? '-');
 
            $selectedResource = collect(Filament::getResources())->first(fn ($resource) => $relatedRecord instanceof ($resource::getModel()));
 
            if ($selectedResource === null) {
                return null;
            }

            foreach ($this->viewTypes as $viewType) {
                try
                {
                    return $selectedResource::getUrl($viewType, [
                        'record' => $relatedRecord->getKey(),
                    ]);
                }
                catch(Exception $e)
                {
                    continue;
                }
            }
        });
    }
}

Issues I'm facing

• Sorting doesn't work on this column.
• A new query is executed for each row, which isn't optimal.
• The data appears in the next row instead of the correct one and I don't understand why).

Can someone guide me on the correct approach to achieve this? 🤔

Thanks in advance! 😊