How to avoid join with subqueries if they are needed?

Question

SQL

Simple Laravel example:

Model::landingSelectTableColumns() // select filter, laravel scope
                ->where(...)
                ->where(...)
                ->where(...)
                ->where(...)
                ->where(...)
               ->where(...) 
                ->join(...)
                ->join(...)
                ->join(...)
                ->join(..., function ($join) use (...) {
                    $join->on(...)
                        ->where(...);
                })
                ->orderByDesc(...)
                ->orderByDesc(...)
                ->orderByDesc(...)
                ->orderByDesc(...)
                ->orderByDesc(...)
                ->limit(...)
                ->get();

That's in general. There are many models and dependencies, Laravel's simple Eager Loading no longer helps, in general, there are even more complex queries .... yes, in which, in addition to subqueries, there are also subqueries in Select (), more variables that affect sorting and sample. In general, while there is little data, the performance is OK, but if there is more data, I would like to know how to remake such a query more correctly?
For example, there is a User model, it has 25 fields with 50+ fields UserDetails. Where only the basic data of the User is needed, we take the 1st table, if additional fields are needed, we do the same join. The same goes for others as well.
The query above does a join on UserDetails, UserRoles, Posts, PostTags, PostCategories to the User model. So we display everything we need on the main page, sorting by fields from tables, there are also a lot of where. For example where deleted_at = null, where posts.is_published = true, order by posts.published_at etc.
Is it really like this in big projects? How could all this be simplified and optimized and made easier to maintain and understand?

Answer 1

In my opinion, large projects do not use porn orms, but work directly with the sql query language