Slow Query - Slow Endpoints in Filamentphp v3
Screenshots shows the issues that im getting.
php public static function table(Table $table)
{
return $table->columns([
Tables\Columns\TextColumn::make('user.name')
->label('Requested By')
->searchable(),
Tables\Columns\TextColumn::make('template.title')
->label('Requested Type'),
Tables\Columns\TextColumn::make('created_at')
->label('Requested On')
->formatStateUsing(function ($state) {
return Carbon::parse($state)->diffForHumans();
}),
])
->filters([
Tables\Filters\SelectFilter::make('status')
->options([
'pending' => 'Pending',
'verified' => 'Verified',
'approved' => 'Approved',
]),
Tables\Filters\TrashedFilter::make(),
])
->actions([
Tables\Actions\ViewAction::make()
Tables\Actions\EditAction::make(),
Action::make('download')
->label('Download')
->url(fn(Letter $record): string => Storage::url($record->file_path))
->disabled(fn(Letter $record): bool => $record->status !== LetterStatus::Approved),
])
->selectCurrentPageOnly()
->paginated([10, 25, 50, 100]);
}
php public static function table(Table $table)
{
return $table->columns([
Tables\Columns\TextColumn::make('user.name')
->label('Requested By')
->searchable(),
Tables\Columns\TextColumn::make('template.title')
->label('Requested Type'),
Tables\Columns\TextColumn::make('created_at')
->label('Requested On')
->formatStateUsing(function ($state) {
return Carbon::parse($state)->diffForHumans();
}),
])
->filters([
Tables\Filters\SelectFilter::make('status')
->options([
'pending' => 'Pending',
'verified' => 'Verified',
'approved' => 'Approved',
]),
Tables\Filters\TrashedFilter::make(),
])
->actions([
Tables\Actions\ViewAction::make()
Tables\Actions\EditAction::make(),
Action::make('download')
->label('Download')
->url(fn(Letter $record): string => Storage::url($record->file_path))
->disabled(fn(Letter $record): bool => $record->status !== LetterStatus::Approved),
])
->selectCurrentPageOnly()
->paginated([10, 25, 50, 100]);
}
4 Replies
php class ListLetters extends ListRecords
{
protected static string $resource = LetterResource::class;
protected function getHeaderActions(): array
{
return [
Actions\CreateAction::make()->label('New Request')->icon('heroicon-o-plus-circle'),
];
}
protected function paginateTableQuery(Builder $query): Paginator
{
return $query->simplePaginate(($this->getTableRecordsPerPage() === 'all') ? $query->count() : $this->getTableRecordsPerPage());
}
}
php class ListLetters extends ListRecords
{
protected static string $resource = LetterResource::class;
protected function getHeaderActions(): array
{
return [
Actions\CreateAction::make()->label('New Request')->icon('heroicon-o-plus-circle'),
];
}
protected function paginateTableQuery(Builder $query): Paginator
{
return $query->simplePaginate(($this->getTableRecordsPerPage() === 'all') ? $query->count() : $this->getTableRecordsPerPage());
}
}
php public static function getRelations(): array
{
return [
//
];
}
public static function getPages(): array
{
return [
'index' => Pages\ListLetters::route('/'),
'create' => Pages\CreateLetter::route('/create'),
'view' => Pages\ViewLetter::route('/{record}'),
'edit' => Pages\EditLetter::route('/{record}/edit'),
];
}
public static function getEloquentQuery(): Builder
{
return parent::getEloquentQuery()
// ->with(['user', 'template', 'verifiedBy', 'approvedBy'])
// ->where(function ($query) {
// $user = auth()->user();
// $userId = $user->id;
//
// $query->where('user_id', $userId);
//
// if ($user->can('verifyAny', Letter::class)) {
// $query->orWhereIn('status', ['pending', 'verified', 'approved']);
// } elseif ($user->can('approveAny', Letter::class)) {
// $query->orWhere('status', 'verified');
// }
// })
// ->latest('created_at')
->withoutGlobalScopes([
SoftDeletingScope::class,
]);
}
php public static function getRelations(): array
{
return [
//
];
}
public static function getPages(): array
{
return [
'index' => Pages\ListLetters::route('/'),
'create' => Pages\CreateLetter::route('/create'),
'view' => Pages\ViewLetter::route('/{record}'),
'edit' => Pages\EditLetter::route('/{record}/edit'),
];
}
public static function getEloquentQuery(): Builder
{
return parent::getEloquentQuery()
// ->with(['user', 'template', 'verifiedBy', 'approvedBy'])
// ->where(function ($query) {
// $user = auth()->user();
// $userId = $user->id;
//
// $query->where('user_id', $userId);
//
// if ($user->can('verifyAny', Letter::class)) {
// $query->orWhereIn('status', ['pending', 'verified', 'approved']);
// } elseif ($user->can('approveAny', Letter::class)) {
// $query->orWhere('status', 'verified');
// }
// })
// ->latest('created_at')
->withoutGlobalScopes([
SoftDeletingScope::class,
]);
}
php lass Letter extends Model
{
use HasFactory;
use SoftDeletes;
protected $guarded = [];
protected $casts = [
'status' => LetterStatus::class,
'verified_at' => 'date',
'approved_at' => 'date',
];
protected static function booted(): void
{
static::creating(function ($model) {
$model->user_id = Auth::user()->id;
$model->status = LetterStatus::Pending;
});
}
public function user()
{
return $this->belongsTo(User::class);
}
public function template(): BelongsTo
{
return $this->belongsTo(LetterTemplate::class, 'template_id');
}
public function verifiedBy(): BelongsTo
{
return $this->belongsTo(User::class, 'verified_by');
}
public function approvedBy(): BelongsTo
{
return $this->belongsTo(User::class, 'approved_by');
}
protected function daysRemaining(): Attribute
{
return Attribute::make(
get: function ($value, $attributes) {
if ($this->status === LetterStatus::Approved) {
return '';
}
$createdAt = Carbon::parse($this->created_at);
$dueDate = $createdAt->addDays($this->template->eta);
$daysRemaining = $dueDate->diffInDays(now(), false);
if ($daysRemaining < 0) {
return 'Overdue in '.abs($daysRemaining).' days';
} elseif ($daysRemaining == 0) {
return 'Due today';
} else {
return $daysRemaining.' days';
}
}
);
}
}
php lass Letter extends Model
{
use HasFactory;
use SoftDeletes;
protected $guarded = [];
protected $casts = [
'status' => LetterStatus::class,
'verified_at' => 'date',
'approved_at' => 'date',
];
protected static function booted(): void
{
static::creating(function ($model) {
$model->user_id = Auth::user()->id;
$model->status = LetterStatus::Pending;
});
}
public function user()
{
return $this->belongsTo(User::class);
}
public function template(): BelongsTo
{
return $this->belongsTo(LetterTemplate::class, 'template_id');
}
public function verifiedBy(): BelongsTo
{
return $this->belongsTo(User::class, 'verified_by');
}
public function approvedBy(): BelongsTo
{
return $this->belongsTo(User::class, 'approved_by');
}
protected function daysRemaining(): Attribute
{
return Attribute::make(
get: function ($value, $attributes) {
if ($this->status === LetterStatus::Approved) {
return '';
}
$createdAt = Carbon::parse($this->created_at);
$dueDate = $createdAt->addDays($this->template->eta);
$daysRemaining = $dueDate->diffInDays(now(), false);
if ($daysRemaining < 0) {
return 'Overdue in '.abs($daysRemaining).' days';
} elseif ($daysRemaining == 0) {
return 'Due today';
} else {
return $daysRemaining.' days';
}
}
);
}
}
Add ->with(['user', 'template'])
Already tried
You can try EXPLAIN ANALYZE in DB for the specific query. Usualy, if You order by created_at and this column is not in index, then the sequential scan can be runniung..