Sorting data with Eloquent

Question

I'm new to laravel (switched from CI) and eloquent ORM is still a bit of a mystery at some point!

Here is my problem :

I'd like to sort data from my db using Eloquent.

I have a table posts and a table comments (post has many comments and comment belongs to posts)

Every comment has a timestamp (=> created_at field) and I'd like to order things as follow :

(we're on profil page so $user->id is the id of the user (obviously))

I want every post from posts where this user made a comment and order all those post by the created_at field of the comment

I really want to use Eloquent fully, or at least Fluent, and I don't know the right way to do so.

I hope I'm being clear, and thank you for your time!

Answer 1

I just ran into the same problem on a project I am developing. Everywhere I looked I saw the usort() or uasort() as the suggested way to solve this problem. (Collection::sort() is just a wrapper for uasort()) but that did not satisfy me because why would I use PHP to sort when SQL should do it for me with an ORDER BY clause??? I ended up implementing it this way using an getXXXAttribute() method:

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

    public function getCommentsAttribute()
    {
        $comments = $this->comments()->getQuery()->orderBy('created_at', 'desc')->get();
        return $comments;
    }
    ...
}

Answer 2

I had the same problem before but it was a one-to-one relationship. This post helped me. I ended up using a join. My code was like this:

$data = Posts::join('comments', 'comments.post_id', '=', 'posts.id')
        ->order_by('comments.created_at')
        ->get(array('comments.field1 as field1', 'posts.field2 as field2'));

Answer 3

Something like this could help.

$content = Posts::with(array('comments' => function($query) {
               $query->order_by('created_at', 'asc');
            }))
            ->get();

Or if your problem is more complex:

How to sort by a field of the pivot table of a many-to-many relationship in Eloquent ORM

Answer 4

If you have the relationship defined in your user class then you can get all posts via the comments. It returns a Collection which provides the sortBy() and sortByDesc() functions. Both of these functions will accept a callback where you can choose the sort method yourself.

$posts = $user->comments->post->sortByDesc(function($post) {
    return $post->created_at;
});

Answer 5

The issue you are facing is because the Relationship that you've defined is just giving you the the Comments that are associated with the posts and an "Order By" on that result will just sort the comments since the comments are what is coming out, so what you could do is that also define that "Comments" belongs to "Post" in the Comments model and then find which Post the Comment is associated with and then use the usort() function to run a manual comparison an example would be (I'm writing the code in Laravel 3 but you could re-write it for any other version):

So, Assuming that your Comments table has a foreign key called postID which defines the relationship with the Posts table and the Timestamp in the Posts table is the default created_at and the Users table is connected to the Comments table with the foreign key userid,

    $userid = $user->id;
    $comments = Comments::where_userid($userid);
    function commentsort($first, $second){
      if (getCommentPost($first) == getCommentPost($second)) {
        return 0;
      }
     return (getCommentPost($first) < getCommentPost($second)) ? -1 : 1;
    }

    function getCommentPost($comment){
    return Post::where_postid($comment->postid)->first()->created_at;
    }

    usort($comments, "commentsort");

That should help fix this, In reality this isn't included in Laravel because it's a framework and functions like these which perform specific functions aren't generally included in the framework because there is limited scope of use, that being said you could also include this function in the default DB class so as to use this functionality universally in the project.