Ordering Related Models with Laravel/Eloquent

Question

Is it possible to use an orderBy for an object's related models? That is, let's say I have a Blog Post model with a hasMany("Comments"); I can fetch a collection with

$posts = BlogPost::all();

And then run through each post, and display the comment's last edited date for each one

foreach($posts as $post)
{
    foreach($post->comments as $comment)
    {
        echo $comment->edited_date,"\n";
    }
}

Is there a way for me to set the order the comments are returned in?

score 19 · Answer 1 · answered Sep 30 '14 at 22:04

19

This is the correct way:

BlogPost::with(['comments' => function ($q) {
  $q->orderBy('whatever');
}])->get();

answered Sep 30 '14 at 22:04

Jarek Tkaczyk

78,987
25
159
157

1

This is indeed the correct way! If you need the flexibility of setting order per-query, this is the only solution that doesn't make new queries for every item. This should definitely be the accepted answer. – Nathan Hazzard Jun 12 '18 at 13:18

score 3 · Accepted Answer · edited May 23 '17 at 12:09

The returned object from the relationship is an Eloquent instance that supports the functions of the query builder, so you can call query builder methods on it.

foreach ($posts as $post) {
    foreach ($post->comments()->orderBy('edited_date')->get() as $comment) {
        echo $comment->edited_date,"\n";
    }
}

Also, keep in mind when you foreach() all posts like this, that Laravel has to run a query to select the comments for the posts in each iteration, so eager loading the comments like you see in Jarek Tkaczyk's answer is recommended.

You can also create an independent function for the ordered comments like you see in this question.

public function comments() {
    return $this->hasMany('Comment')->orderBy('comments.edited_date');
}

And then you can loop them like you did in your original code.

Ah, I was missing `get` -- also, shouldn't that be a call to the `comments` method? If I try it with the magic property I get `Call to undefined method Illuminate\Database\Eloquent\Collection::orderBy()` — Alana Storm, Sep 30 '14 at 21:22

MartinG · Answer 3 · 2021-03-08T21:55:48.033

1

Create a JOIN and select just the column you want to order on:

$post = BlogPost::join('comments', function($j) {
        $j->on('posts.id', '=', 'comments.post_id');
        $j->select('comment_date_or_other_col');
     })
     ->orderBy('comment_date_or_other_col', 'DESC')->first();

edited Mar 08 '21 at 21:55

answered Mar 08 '21 at 17:21

MartinG

153
1
11

score -3 · Answer 4 · answered Sep 30 '14 at 21:12

-3

Yes:

$posts = BlogPost::with('comments')
           ->orderBy('comments_table_name.column_name')
           ->get();

And you can also set that in your relation:

public comments()
{
    $this->hasMany("Comments")->orderBy('comments.column_name');
}

answered Sep 30 '14 at 21:12

Antonio Carlos Ribeiro

86,191
22
213
204

+1 for `with` -- I hadn't seen that pne yet. However, the `orderBy` seems to be applying itself to to the first query (select * from blog_posts), and not the second. Is there a different syntax for applying the order by and/or do you relations need to be setup in a certain way? (referring to the BlogPost::with code here, not directly adding things to the relation) – Alana Storm Sep 30 '14 at 21:48
4

This is wrong, you can't reference related table like this, for it's not used in the main query (on which you apply orderBy) – Jarek Tkaczyk Sep 30 '14 at 22:05

Ordering Related Models with Laravel/Eloquent

4 Answers4

Linked