Snippets for laravel database load diagnostics

List of the longest queries

use Laravel\Telescope\Storage\EntryModel;
use Laravel\Telescope\EntryType;
use Carbon\CarbonImmutable;
 
$entries = EntryModel::query()
    ->where('type', EntryType::QUERY)
    ->whereDate('created_at', '>=', CarbonImmutable::now()->subDay())
    ->whereDate('created_at', '<=', CarbonImmutable::now())
    ->where('content', 'not like', '%telescope_entries%')
    ->orderByRaw("trim(both '\"' from (content::jsonb->'time')::text)::float8 desc")
    ->limit(10)
    ->get();
 
$entries->map(fn ($e) => [$e->content['sql'], $e->content['time'], $e->created_at->format('H:i d.m.Y'), $e->uuid])->all()

List of the most frequent queries

use Laravel\Telescope\Storage\EntryModel;
use Laravel\Telescope\EntryType;
use Carbon\CarbonImmutable;
  
$entries = EntryModel::query()
    ->where('type', EntryType::QUERY)
    ->whereDate('created_at', '>=', CarbonImmutable::now()->subDay())
    ->whereDate('created_at', '<=', CarbonImmutable::now())
    ->where('content', 'not like', '%telescope_entries%')
    ->selectRaw("trim(regexp_replace((content::jsonb->'sql')::text, '[\d+]', '', 'g')) as sql, COUNT(*) as count")
    ->groupBy('sql')
    ->orderByDesc('count')
    ->limit(10)
    ->get();
 
$entries->map(fn ($e) => [$e->sql, $e->count])->all()