When Denormalized Data Creates Filter-Display Mismatches

I was debugging a filter that seemed to work perfectly—until users started reporting missing results. The filter UI said “Show items with USD currency,” but items with USD weren’t appearing.

The problem? The filter was checking one field (batch_items.currency), but the UI was displaying a different field (batches.cached_currencies)—a denormalized summary column that aggregated currencies from all batch items.

Here’s what was happening:

// Filter checked individual batch items
$query->whereHas('batchItems', function ($q) use ($currency) {
    $q->where('currency', $currency);
});

// But the UI displayed the denormalized summary
$batch->cached_currencies; // ["USD", "EUR", "GBP"]

When all batch items were sold out or inactive, the whereHas() check would return nothing—even though cached_currencies still showed “USD” because it hadn’t been refreshed.

The Fix

Match the filter logic to what users actually see. If you’re displaying denormalized data, either:

1. Filter against the same denormalized field:

// Filter matches what's displayed
$query->whereJsonContains('cached_currencies', $currency);

2. Or ensure the denormalized field stays in sync:

// Observer to keep cached data fresh
class BatchObserver
{
    public function saved(Batch $batch)
    {
        $batch->update([
            'cached_currencies' => $batch->batchItems()
                ->distinct('currency')
                ->pluck('currency')
        ]);
    }
}

The Lesson

When users see one thing but your filter checks another, you’ll get confusing bugs. Always verify: Does my filter logic match what’s displayed in the UI?

If you’re caching/denormalizing data for performance, make sure filters query the same cached field—or keep it strictly in sync.