How to get Last Record From leftJoin Table in Laravel7?

In this article i will share with you one of the helpful laravel database query solution with simple example. we have two following tables for solve this laravel query problem.

articles table structure

| id | title | slug | body | tags_id | categories_id |
|----|-------|------|------|---------|---------------|
|    |       |      |      |         |               |
|    |       |      |      |         |               |
|    |       |      |      |         |               |

comments table structure

| id | article_id | comment |
|----|------------|---------|
|    |            |         |
|    |            |         |

Challenge :

Here, chalange is get all the records from articles table. but, also get the latest/last comment from comments table. here we want only new comments not the all comment will get in the records.

Solutions

Here we have 2 tables 'articles' and 'comments' where articles is left table and comments is right table which has article's comments.

We wanted to left join articles with comments but the join should be with the latest record from comments table.

$query = Article::select('articles.*', 'comments.comment as article_comment')
->leftJoin('comments', function($query) {
    $query->on('comments.article_id','=','articles.id')
        ->whereRaw('comments.id IN (select MAX(a2.id) from comments as a2 join articles as u2 on u2.id = a2.article_id group by u2.id)');
})
->get();

I hope this laravel query solution more helpful to you.