Dealing with Eloquent N+1 Problem

Hey everyone,

I'm running a small SaaS and we've recently noticed some significant performance bottlenecks, particularly on pages that display lists of related data. We're using Laravel, and I suspect we're hitting a classic N+1 problem with Eloquent.

The Scenario:

• We have a Projects model, and each project has many Tasks.
• On the project dashboard, we list all projects and want to display a count of uncompleted tasks for each project.
• Initially, I was just looping through projects and calling $project->tasks()->where('completed', false)->count() inside the loop. As expected, this caused hundreds of queries for just a few dozen projects.

What I've Tried So Far:

• Eager Loading with with(): My first thought was to eager load. I tried Project::with('tasks')->get(), but then accessing the count ($project->tasks->where('completed', false)->count()) still hydrates all tasks, which isn't ideal for just a count.
• Custom Selects & Aggregates: I attempted to use withCount() like Project::withCount(['tasks' => function ($query) { $query->where('completed', false); }])->get(). This correctly adds a tasks_count attribute, but for more complex aggregations or conditional counts, it gets tricky, and I'm not sure if it's always the most performant way when you also need other task-related data elsewhere on the page.
• Subqueries: I've looked into using subqueries in the select clause, but I'm struggling to get the syntax right for conditional counts without making the query overly complex or unreadable.

The Core Question:

What are the most effective and idiomatic ways to resolve the N+1 problem in Laravel Eloquent, especially when dealing with conditional counts or aggregations across relationships, without fetching entire related collections? Are there any advanced patterns or tools beyond withCount() that you use for complex Eloquent query issues or large datasets?