Laravel eloquent query slow

the core problem is this one particular complex data retrieval process. it involves multiple hasManyThrough relationships and a bunch of aggregations, and it's just causing severe slowdowns. our reporting features are almost unusable during peak times; we're seeing page load times consistently above 10-15 seconds for some of these reports. it's not ideal for a 'quick fix' service, ironically.

we've tried a few things already, you know, the usual suspects:

• we implemented eager loading with ->with() for all the obvious relationships. that helped a bit, but not enough.
• carefully selected columns using ->select() to try and minimize data transfer.
• made sure all relevant foreign keys and frequently filtered columns have proper database indexes. we double-checked them.
• used laravel debugbar quite a bit and manually ran EXPLAIN on the generated SQL queries. the EXPLAIN output still sometimes shows full table scans or inefficient joins, even with indexes in place, especially when combining multiple whereHas or whereDoesntHave clauses.
• experimented with query caching for a bit, but the data updates too frequently for it to be a viable long-term solution for real-time reports.

the stubborn bottleneck is still this specific set of eloquent query operations involving complex nested conditions and subqueries. it just feels like we're hitting a wall with standard eloquent methods for truly complex analytical queries. here's a simplified example of an EXPLAIN output we often see for one of the problematic queries:

+----+-------------+----------+------------+-------+-----------------------------+-----------------------------+---------+------+---------+----------+------------------------------------------+
| id | select_type | table    | partitions | type  | possible_keys               | key                         | key_len | ref  | rows    | filtered | Extra                                    |
+----+-------------+----------+------------+-------+-----------------------------+-----------------------------+---------+------+---------+----------+------------------------------------------+
|  1 | PRIMARY     | users    | NULL       | ALL   | PRIMARY                     | NULL                        | NULL    | NULL |  100000 |   100.00 | Using where; Using temporary; Using filesort |
|  1  | JOIN        | projects | NULL       | ref   | user_id                     | user_id                     | 4       | func |      10 |   100.00 | Using where                              |
|  1  | JOIN        | reports  | NULL       | ALL   | project_id,created_at       | NULL                        | NULL    | NULL | 1000000 |    10.00 | Using where; Using join buffer (hash join)|
+----+-------------+----------+------------+-------+-----------------------------+-----------------------------+---------+------+---------+----------+------------------------------------------+

see that ALL for users and reports, and the Using temporary; Using filesort? that's what's killing us. we're looking for insights into more advanced eloquent query optimization techniques. are there specific patterns or architectural approaches for handling highly complex, dynamic analytical queries in Laravel that don't force a complete rewrite into raw SQL? i mean, we want to leverage eloquent where possible. any experience with custom query builders, materialized views, or specific Laravel packages designed for performance-critical data retrieval in these kinds of scenarios? what strategies do you guys use when standard eloquent methods just aren't cutting it for complex aggregations and you need serious speed?

thanks in advance!