Still struggling: best Laravel optimization for complex joins?

hey everyone, following up on that slow eloquent queries thread from last week. we're still kinda struggling, even after trying some of the basic stuff you guys suggested. it's a real headache and honestly, it's impacting our user experience.

our main issue now is with these really complex queries, you know, multiple leftJoins and whereHas clauses, sometimes even nested ones. when the dataset gets big, like tens of thousands of records, it just grinds to a halt. it's not just the database query itself taking ages, but also hydrating all those eloquent models afterwards is a killer. we're talking about queries that can take 5-10 seconds, which is just not acceptable for a modern web app.

we've gone through the usual suspects trying to improve things. we're using with() for eager loading related models, specific select() statements to only fetch what we need, and we've put indexes on all our foreign keys and frequently queried columns. we even tried some DB::raw() for really specific aggregates or tricky conditional joins where eloquent was just too clunky. for huge datasets that we process in the background, we're even using chunkById() to iterate through them, but the initial fetch of the primary dataset is still the killer. it feels like we're just moving the bottleneck around.

where we're really stuck is that the profiler, like laravel debugbar, shows the database query itself taking a significant chunk of time (sometimes 70-80% of the request), but then the php memory usage and model hydration just explodes too. it feels like we're hitting a wall beyond simple indexing and eager loading. we've got enough RAM on the server, but it's like eloquent just chokes on the sheer volume of data it needs to turn into objects.

here’s a simplified query snippet that's causing us grief, along with some dummy debugbar output so you can see what i mean:

// Simplified problematic query example
$results = User::query()
    ->select('users.*')
    ->leftJoin('orders', 'users.id', '=', 'orders.user_id')
    ->leftJoin('products', 'orders.product_id', '=', 'products.id')
    ->whereHas('subscriptions', function ($query) {
        $query->where('status', 'active');
    })
    ->where('products.category_id', 5)
    ->with(['profile', 'subscriptions'])
    ->orderBy('users.created_at', 'desc')
    ->get();

// Debugbar output snippet (simplified)
Query Time: 4.87s (SELECT users.* FROM users LEFT JOIN orders... WHERE products.category_id = 5)
Memory Usage: 128 MB
Models Hydrated: 15,000
PHP Time: 2.15s

so, really looking for some next-level Laravel performance tuning tips for these specific complex join scenarios. maybe different query patterns, like using subqueries more effectively, or even denormalizing some data. or ways to deal with large result sets more efficiently in eloquent without hitting these memory and hydration walls. or even if it's better to just drop to raw SQL at some point for these specific reports to bypass eloquent entirely. we're open to anything that can make this faster.

anyone else been down this rabbit hole before?