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

1

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,652
2
15
26

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
Sorry, but this is not the correct answer. the above will only return 1 relationship of the last Post in the result. not 1 comment per Post. why it was marked as Answer? – Amit Shah Sep 22 '22 at 15:52
@AmitShah are you sure that related models exist? If there are no comments, the Collection will simply be empty. Otherwise the first comment will be retrieved. – Aless55 Sep 22 '22 at 18:56
it's not about comment is empty or not, but if it's there. then you try it. it will only retrieve only one comment among all the Posts. not for each Post. I tried finding solution for these everywhere. – Amit Shah Sep 25 '22 at 17:12

score 1 · 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 1 · Answer 3 · answered Sep 22 '22 at 16:42

1

SOLUTION

with trick (no direct sql function I found working with eloquent yet)

apart from hasMany relationship you can create hasOne relationship and provide some condition, ordering.

class Post extends Model
{
    public function first_comment()
    {
        return $this->hasOne(Comment::class)->latest(); 
        // or any orderBy/Where condition according to your filter requirements.
    }
}

$posts = Post::with('first_comment')->get();

answered Sep 22 '22 at 16:42

Amit Shah

7,771
5
39
55

This is not the correct answer, you are not retrieving the first comment, you are retrieving the last comment that was made. As described in the question, the first match is wanted. – Aless55 Sep 22 '22 at 18:58
@Aless55 It is already mentioned that the filter can be modified according to developer's requirement. so there can be `->oldest()` instead. but what important here is how to achieve it using `hasOne()` relationship. – Amit Shah May 08 '23 at 06:17

score -3 · Answer 4 · 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

4 Answers4