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.

Fillister

What's the version of your laravel? – Louvre

Don't return inside your function because that will stop your query. – Louvre

I know this code is wrong. You can't pass orderBya function because it has to convert it to an database query. But it explains the problem I have. – Fillister

So you want to sort your products based on the count of status that is equal to two am I right? – Louvre

@Louvre that's right. – Fillister

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);

Fillister

Nice one. I have generalized this into a scope, so you could do "$products->includeCount('orders')->orderBy('orders', 'desc')". Have a look gist.github.com/boukeversteegh/50c138578c9ca4b9282491b274db5d16 – Bolling

have other solution whitout the ->join() that uses with()? – Mixer

Answer 2

Try this if it works:

    $categories = Prodcuts::with(array('orders' => function($query) {
       $query->select(DB::raw('SELECT * count(status) WHERE status = 2 as count'))
        $query->orderBy('MAX(count)')
     }))->paginate(15);

    return View::make('products.index', compact('categories'));

Note: This is not tested.

Louvre

` Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * count(status) WHERE status = 2 as count from orders where orders.p' at line 1 (SQL: select SELECT * count(status) WHERE status = 2 as count from orders` where orders.product_id in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15) order by MAX(count) asc)` – Fillister

Answer 3

From 5.2 on wards you can use the withCount for counting relationship result.

In this case the

$products = $products->withCount(['orders' => function ($query) {
    $query->where('status', 2);
}]);

Reference : https://laravel.com/docs/5.2/eloquent-relationships

Bremser