Order by sum column relationship in Laravel

Question

I have this controller which grabs posts from a post table. Every post in the posts table have the relation "hasMany" with another table likes.

Controller:

public function getDashboard(){
        $posts = Post::orderBy('created_at', 'desc')->get(); 

        return view('dashboard', ['posts' => $posts]);
}

I'd like to replace 'created at' with something like:

$post->likes->sum(like)

Don't know how to write the right syntax though.

EDIT:

Here are the tables.

Posts  
--id   
--body  
Likes 
--id  
--post_id  
--like

The column like can have the value 1 or -1. I'd like to order on the summation of that column for each post. So a post with one dislike(-1) and one like(1) will have the aggregated value of 0, hence will be placed after a post with one like(1).

Answer 1

You can use withCount() for this as:

Post::withCount('likes')->orderBy('likes_count')->get()

withCount() will place a {relation}_count column on your resulting models

Update

Post::withCount(['likes' => function($q) {
    $q->where('like', 1)
}])
->orderBy('likes_count')
->get()

Update2

You can use sortByDesc() to sort your collection as:

$posts = Post::get();

$posts = $posts->sortByDesc(function ($post) {
            return $post->likes->sum('like');
        });

Answer 2

If you want to sum column value from relationship and then sort records.

$users = User::addSelect(['likes' => Post::selectRaw('sum(likes) as total_likes')
     ->whereColumn('user_id', 'useres.id')
     ->groupBy('user_id')
 ])
 ->orderBy('likes', 'DESC')
 ->get()
 ->toArray();

Or use below

$users = User::select("*", 
               \DB::raw('(SELECT SUM(likes) FROM likes_table WHERE likes_table.user_id = users.id) as tolal_likes'))
        ->orderBy('likes', 'DESC')
        ->get()
        ->toArray();