Simplifying Widget Queries in Reimpact/platform

When developing widgets in Reimpact/platform, efficiency is key. A recent update addresses an issue with Filament's default sorting behavior in subqueries, specifically within the TopExceptionsWidget. Let's explore the problem and the solution.

The Problem

Filament, a PHP framework, automatically applies ORDER BY model.id when using HasRecords. This default behavior becomes problematic when a subquery, such as the one used in TopExceptionsWidget, lacks an id column. This leads to errors and prevents the widget from displaying data correctly.

The Solution

The fix involves disabling the default key sort for the subquery. By using defaultKeySort(false), we prevent Filament from adding the problematic ORDER BY clause. The subquery already orders the results by occurrences in descending order (occurrences desc), so disabling the default sort does not affect the desired outcome.

Consider a scenario where we're fetching the most frequent error types. The initial query might look something like this:

$subquery = DB::table('exceptions')
    ->select('type', DB::raw('count(*) as occurrences'))
    ->groupBy('type')
    ->orderBy('occurrences', 'desc');

Filament's default behavior would attempt to add ORDER BY exceptions.id to this subquery, causing an error because the aggregated result doesn't have an id column. The corrected approach involves disabling this default sort:

$subquery = DB::table('exceptions')
    ->select('type', DB::raw('count(*) as occurrences'))
    ->groupBy('type')
    ->orderBy('occurrences', 'desc')
    ->defaultKeySort(false);

This ensures that the subquery functions as intended, ordering results by the number of occurrences without triggering errors related to missing id columns.

The Takeaway

When working with subqueries in Filament widgets, be mindful of default sorting behaviors. Use defaultKeySort(false) to prevent errors when the subquery lacks a default key column. This ensures your widgets display data correctly and efficiently.