Laravel SubQuery in Filament Tables: Show Relationship Value with Condition

In Filament tables, you may want to show a relationship value with a conditional query. How do you use the Laravel subquery mechanism in Filament?

The Scenario

Here's an example table:

You're showing a list of products and want to show the rating by the logged-in user.

The DB structure only has the name field on the Product Model.

Schema::create('products', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->timestamps();
});

The ratings table has a rating column and relationship columns for users and products.

Schema::create('ratings', function (Blueprint $table) {
    $table->id();
    $table->integer('rating');
    $table->foreignId('user_id')->constrained()->cascadeOnDelete();
    $table->foreignId('product_id')->constrained()->cascadeOnDelete();
    $table->timestamps();
});

So, how do we show a user's rating for each product? In this case, we will use a subquery select.

The Solution

First, you need to know you can customize Filament table query by using the modifyQueryUsing() method. This method accepts a closure where you can use all Eloquent features.

One of Eloquent's features you can use here is subqueries.

So, in the modifyQueryUsing() method, you can use the addSelect() method to add an attribute to the Model being retrieved.

use App\Models\Review;
use App\Models\Product;
use Filament\Tables\Table;
use Filament\Resources\Resource;
use Illuminate\Database\Eloquent\Builder;
 
class ProductResource extends Resource
{
    // ...
 
    public static function table(Table $table): Table
    {
        return $table
            ->modifyQueryUsing(function (Builder $query) { 
                return $query->addSelect(['users_rating' => Rating::select('rating')
                        ->whereColumn('product_id', 'products.id')
                        ->whereColumn('user_id', auth()->id())
                        ->limit(1),
                    ]);
            }) 
            // ...
    }
 
    // ...
}

This subselect will add the users_rating attribute to the Product Model.

Now, how do we show this value in the TextColumn? Well, in the same way as any field, pass its name.

use App\Models\Review;
use App\Models\Product;
use Filament\Tables;
use Filament\Tables\Table;
use Filament\Resources\Resource;
use Illuminate\Database\Eloquent\Builder;
 
class ProductResource extends Resource
{
    // ...
 
    public static function table(Table $table): Table
    {
        return $table
            ->modifyQueryUsing(function (Builder $query) {
                return $query->addSelect(['users_rating' => Rating::select('rating')
                        ->whereColumn('product_id', 'products.id')
                        ->whereColumn('user_id', auth()->id())
                        ->limit(1),
                    ]);
            })
            ->columns([
                Tables\Columns\TextColumn::make('name'),
                Tables\Columns\TextColumn::make('users_rating') 
                    ->label('My Rating')
                    ->placeholder('Not Rated'), 
            ])
            // ...
    }
 
    // ...
}

Here's the result:

This tutorial's code comes from our Project Example Product Star-Based Rating with Custom Popup