Can I use eager loading with complex queries involving joins and subqueries

php
$posts = Post::with(['comments' => function ($query) {
    $query->select('id', 'post_id', 'body')
          ->whereNotNull('approved_at')
          ->orderBy('created_at', 'desc');
}])
->join('categories', 'posts.category_id', '=', 'categories.id')
->where('categories.name', 'News')
->whereHas('author', function ($query) {
    $query->where('name', 'John Doe');
})
->selectRaw('posts.*, (SELECT COUNT(*) FROM likes WHERE likes.post_id = posts.id) as likes_count')
->orderByDesc('likes_count')
->get();

In this example:

1. We eager load the `comments` relationship, but we also add a constraint to only load approved comments and select specific columns.

2. We join the `posts` table with the `categories` table to only fetch posts from the "News" category.

3. We add a `whereHas` constraint to only fetch posts written by the author "John Doe".

4. We use `selectRaw` to add a subquery to count the number of likes for each post and alias it as `likes_count`.

5. Finally, we order the results by the `likes_count` in descending order.

This demonstrates that you can combine eager loading with complex queries involving joins, subqueries, and constraints to optimize your database queries while still maintaining flexibility in your queries.

Remember to replace the model names, relationship names, and column names with your actual schema.