Debugging Missing Records in Laravel Reports: Export the SQL

When users report “missing records” in generated reports, resist the urge to dive into application logic first. Instead, export the exact SQL query your Laravel app is executing and inspect it directly. Nine times out of ten, the issue is in the query, not your code.

The Problem

A user reports that your cancellation report shows only 6 out of 8 expected records. You’ve checked the database manually—all 8 records exist with correct timestamps and status codes. But the report consistently omits 2 specific records. What’s going wrong?

The Solution: Export and Inspect Raw SQL

Don’t guess. Export the generated SQL and run it yourself:

// In your report controller
public function generateReport(Request $request)
{
    $query = $this->buildCancellationReportQuery($request->filters);
    
    // Export for debugging
    \Storage::put('temp/debug-query.sql', $query->toSql());
    \Storage::put('temp/debug-bindings.json', json_encode($query->getBindings()));
    
    $results = $query->get();
    
    return Excel::download(new CancellationReportExport($results), 'report.csv');
}

Then examine the SQL file. Replace placeholders with actual bindings and run it directly in your database client.

What to Look For

• JOIN mismatches: Are all necessary tables joined? Check LEFT vs INNER JOINs.
• WHERE clause conflicts: Multiple ANDs can exclude records unintentionally.
• UNION logic errors: If your query combines multiple subqueries, each needs identical filtering.
• Date/time timezone issues: Server timezone != user timezone can shift filter boundaries.
• Soft delete confusion: Are you accidentally filtering out records with deleted_at IS NULL?

Real Example

In one case, a report combined 3 UNION subqueries to gather records from different sources. The main query filtered by supplier_id = 13088, but only 2 of the 3 UNION branches had this filter. The missing records came from the third branch—which returned ALL suppliers’ data, then got filtered out at the GROUP BY stage.

The fix: add WHERE supplier_id = 13088 to every UNION branch.

Pro Tips

• Use DB::enableQueryLog() + DB::getQueryLog() for quick debugging in development
• Laravel Telescope automatically captures all queries—invaluable for production debugging
• For complex reports, consider writing raw SQL first, then translating to Query Builder once it works

This approach saves hours of tracing through service layers, repositories, and scopes. When records are missing, go straight to the source: the SQL.