get Last Record From leftJoin Table in Laravel?

get the latest record from leftJoin table, how to get the last record from letjoin table in laravel, in laravel whereRaw() example, nested join in join example, self join in laravel,

In this article, I will share with you one of the helpful laravel database query solutions with a simple example. we have two following tables to 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, the challenge is to get all the records from articles  table. but, also get the latest/last comment from comments  table. here we want only new comments not all comment will get in the records.

Solutions

Here we have 2 tables ‘articles’ and ‘comments’ where articles is left table and comments is the right table which has an 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.