Laravel OrderBy Relationship count with pagination

Question

I have many projects and each has many orders, some completed, some not.

I want to order them by the amount of completed orders like this:

$products = $products->orderBy(function($product) {
    return $product->orders->where('status', 2)->count();
})->paginate(15);

I know the order call doesn't work like this but this is the best was show the problem. SortBy doesn't work because I want to use pagination.

Answer 1

Finally found a good solution for the problem:

$products = $products->join('orders', function ($join) {
                        $join->on('orders.product_id', '=', 'products.id')
                            ->where('orders.status', '=', 2);
                    })
                    ->groupBy('products.id')
                    ->orderBy('count', $order)
                    ->select((['products.*', DB::raw('COUNT(orders.product_id) as count')]))->paginate(50);