Laravel, eloquent `sibling` (next, previous element) in hasMany relation by given key

Question

Let's say I have a model Post which hasMany Comments.

I also have a certain comment found as:

$comment = Comment::find(12);

Next I want to find a next and previous comments based on updated_at column.

For example:

Posts:

╔════╦══════════════╦══════╗
║ ID ║       Name   ║ Smtn ║
╠════╬══════════════╬══════╣
║  1 ║ First Post   ║ 5636 ║
║  2 ║ Second Post  ║  148 ║
╚════╩══════════════╩══════╝

Comments:

╔════╦══════════════╦═════════╦════════════╗
║ ID ║   Comment    ║ post_id ║ updated_at ║
╠════╬══════════════╬═════════╣════════════╣
║  1 ║ First Comm   ║    1    ║  123       ║
║  2 ║ Second Post  ║    2    ║  124       ║
║  3 ║ Third Post   ║    1    ║  126       ║
║  4 ║ Fourth Post  ║    2    ║  125       ║
║  5 ║ Fifth Post   ║    1    ║  128       ║
║  6 ║ Sixsth Post  ║    1    ║  127       ║
╚════╩══════════════╩═════════╩════════════╝

and my comment is the one with id 3:

$comment = Comment::find(3);

How do I define previous() and next() to fetch the comments by updated_at column and get comments that are previous and next? In this case:

Previous:

║  1 ║ First Comm   ║    1    ║  123       ║

Next:

║  6 ║ Sixsth Post  ║    1    ║  127       ║

The updated_at is a DATETIME (with timezone) field in a database. I was lazy to write the exact code.

Answer 1

First Question

Doing as seen here in the answers will get you the correct result but that is not recommended.

The code will get all comments related with the post, apply order and where through PHP array manipulation and then give you the filtered comments. In fact after ->comments everything is PHP.

This will be very bad, if comments start numbering in hundreds to thousands or more depending on your hardware.

To counter this you should do it like;

$subjectComment = Comment::whereId($commentId)->with('post')->first();

$post = $subjectComment->post; 

$previousComment = $post->comments()
    ->where('updated_at', '<=', $subjectComment->updated_at)
    ->where($subjectComment->getKeyName(), '!=', $subjectComment->getKey())
    ->latest('updated_at')
    ->first();

$nextComment = $post->comments()
    ->where('updated_at', '>', $subjectComment->updated_at)
    ->oldest('updated_at')
    ->first();

Notice the difference? No? here is a brief explanation;

Using relations as properties of models like $post->comments

This syntax has certain advantages in some scenarios and disadvantages in others. This is what Eloquent is doing behind the scenes;

• Check if the relation is already loaded
• If not, load it. That means it loads all the associated models
• This is great, if you have logic which may or may not load this relation depending on the data you are processing.
• Though its absolutely horrendous, if you only need a fraction of the models that the query will return.

Using relations as functions of models like $post->comments()

This syntax returns a query instead of models which you can further customize to suit your needs. The query is similar to;

Comment::whereHas('post', function ($q) use ($postId) {
    $instance = new Post;
    $q->where($instance->getQualifiedKeyName(), $postId);
});

Second Question

There is no difference in either of the statements. Both will accomplish the same thing in the same amount of time. The only difference would be readability.

Answer 2

This should be cleaner than what you're doing now. Just select the 1 related comment that has the first updated_at that's either lower or higher than $comment

$previous = $post->comments->orderBy('updated_at', 'DESC')->take(1)->where('updated_at', '<', $comment->updated_at)->first();

$next = $post->comments->orderBy('updated_at', 'ASC')->where('updated_at', '>', $comment->updated_at)->first();

Answer 3

I'm not that happy how this looks but I don't see a better way to do it and it makes sense for me. If you have any ideas please respond.

There might be typos as my code is a bit more complex and my models have different names. Please report if you see any errors.

    //get current comment with id 12
    $comment = Comment::->find(12);
    //get parent post
    $post = $comment->post;

    //get all comments of this post
    $comments = $post->comments->orderBy('updated_at',ASC)->get();
    //some helper variables because I don't know the better way
    $prev = null; // the previous comment
    $next  = null; // the next comment
    $counter=0; //helper counter
    //iterate through all comments
    foreach ($comments as $commentIterated) {
        //if this is my comment increase the counter
        if($commentIterated->id == $comment->id) {
            $counter++;
        } elseif ($counter == 1) { //if counter is increased only once increase it again and save current comment as the next one (since in previous iteration we concluded that that was our comment and hence increased the counter first time)
            $next = $commentIterated;
            $counter++;
        } elseif ($counter == 0){ //if counter is not increased current comment is previous one, but this will be rewritten in next iteration if we don't find our comment again
            $prev = $commentIterated;
        }
        $comments->shift(); // Laravel helper method to remove the current element () from the collection so we are left with rest of collection and current instance is again first object in collection which is the next object in this iteration
    }

    return view('my_view', compact('comment','prev','next'));

Quick question for the experts:

Would replacing this

    //get parent post
    $post = $comment->post;

    //get all comments of this post
    $comments = $post->comments->orderBy('updated_at',ASC)->get();

with this

    //get all comments of this post
    $comments = $comment->post->comments->orderBy('updated_at',ASC)->get();

make things faster? I'm not really sure how this method linking works in background. Is there a simple way to benchmark these queries in Laravel/PHP?