Laravel: Select first record in a One-to-Many relation

Question

Is it possible to use eloquent to retrieve just the first match in a one to many relationship?

What do I mean, well, let me explain.

Most of us are familiar with the common one to many relationship of (or between) posts and comments, as in:

A post has can have zero or more comments
A comment belongs to one post

Which is represented in laravel as:

class Post extends Model
{
    public function comments()
    {
        return $this->hasMany(Comment::class);
    }
}

class Comment extends Model
{
    public function post()
    {
        return $this->belongsTo(Post::class);
    }
}

So I wanted to find out if it is possible to retrieve post(s) with just the first comment in the result and not all comments that belong to the post(s).

$posts = Post::with('comments.0')->get(); 
$post = Post::with('comments.0')->first();

With comments.0, as my way of saying, get just the first comment belonging to the each or that post.

I have looked at this question and I was wondering how it could be done using eloquent?

Thank you.

Aless55 · Accepted Answer · 2020-07-30T15:18:31.640

2

Problem:

To get 1 Comment per Post you will have to limit the subquery. I would do it like that:

Solution:

Post::with(['comments' => function ($query){
    return $query->first();
}])->get();

With that, we are returing the first() comment and get() all posts do do so for.

edited Jul 30 '20 at 15:18

answered Jul 30 '20 at 15:11

Aless55

2,273
1
13
25

1

This seems to be working but for some strange reason, it runs one extra query that in my opinion is not needed. Please check it out with [laravel debugbar](https://github.com/barryvdh/laravel-debugbar) – kellymandem Jul 30 '20 at 15:37
2

I am not sure if it is possible to use eloquent and have less queries @kellymandem – Aless55 Jul 30 '20 at 16:03
@Aless55 Yes, If we use eloquent, It'll run extra queries so row query is best in this case. – Niyanta Bhayani May 11 '22 at 13:44

score 0 · Answer 2 · answered May 11 '22 at 13:43

$post = Post::leftJoin('comments as c','c.post_id','=','posts.id')->select('posts.id','posts.title','c.id as comment_id','c.description')->groupBy('posts.id')->get();

This query return only one comment if exist otherwise return null data which is selected from comments table. I added some dummy fields in select you can add which required.

score -2 · Answer 3 · answered Jul 30 '20 at 18:43

If you need to use one of the comments, you could simply access it like this

$posts = Post::with('comments')->get();
foreach($posts as $post){
   // To get the first comment out of the $post->comment collection
   $post->comments->first();
}

Where first is a collection method. https://laravel.com/docs/7.x/collections#method-first

The number of queries would be two for this case when you eager load all the comments. And even if there's a way to eager load one comment in Eloquent, then too there would be minimum of two queries required.

you can use the with([comments => function($q) { return $q->first(); }])->get(); this will be much better and will lower the preformance — Moubarak Hayal, May 11 '22 at 13:59

Laravel: Select first record in a One-to-Many relation

3 Answers3