Laravel Eager Load with conditional statement

Question

good day everyone

I'm new to Laravel and currently building my first simple news app with it

I have a simple query that using laravel's eager loading function But in this eager loading function, I want to fetch certain comments based on some conditions

Example:

use App\News;
...

public function home() {
    $news = News::with([
                    'thumbnail',
                    'source', // link to real news source
                    'comments' => function ($query) {
                        // fetch a comment where likes > 0, otherwise, get latest comment
                        // *get only one*
                    }
                ])->paginate(5);

    return response()->json([
        'news' => $news
    ]);
}

How to do this?

UPDATE

I found a way to order comments by likes count, but I do not know how to order them by 'created_at' (fetch latest comment) if there are no likes in each comment

I need to include 'likes_count' using withCount('likes') And then order them in by descending

public function home() {
    $news = News::with([
        'thumbnail',
        'source',
        'comments' => function ($query) {
            $query->withCount('likes');
            $query->orderBy('likes_count', 'DESC')->first();

            // return the first comment that has likes or most liked comment
        }
    ])->paginate(5);

    ...
}

Now how to put fallback query to order comments by 'created_at' (newest one) if there are no like in comments?

Thanks in advance

Answer 1

Your current approach looks fine for ordering your results by likes count in case of no likes you can still get latest record by adding another order by clause with created_at column

$news = News::with([
    'thumbnail',
    'source',
    'comments' => function ($query) {
        $query->withCount('likes');
        $query->orderBy('likes_count', 'DESC')
              ->orderBy('created_at', 'DESC');
    }
])->paginate(5);

Copied from initial comment

So if all comments have 0 likes then the newest one will appear first in list, same if comments have likes then they are also sorted first with likes count and then with created at.

Once you have ordered collection then while you are looping your news records then pick the first item from your related records