How to order by another table join by eager loading in Laravel

Question

I got product and stocks table;

products

id int
name varchar
created_at timestamp

stocks

id int
name varchar
product_id varchar
created_at timestamp

Product Model

public function validStock() {
    return $this->hasMany('Stock')->where('quantity', '>', 10);
}

If both have created_at, how to order by stocks's created_at, I've tried two methods and it's not work

Product::with('validStock')->orderBy('validStock.created_at', 'DESC');

Product::with(array('validStock' => function($q) {
    $q->orderBy('created_at', 'DESC');
}));

Answer 1

Instead of sorting after retrieving all data (which is impossible to do in an efficient way when paginating results) you can use a join.

(This answer is based on this one.)

Product::with('validStock')
    ->join('stocks', 'stocks.product_id', '=', 'products.id')
    ->select('products.*') // Avoid selecting everything from the stocks table
    ->orderBy('stocks.created_at', 'DESC')
    ->get();

The only thing I don't like about this is that it takes away some of the database abstraction, in that you have to write your table names here.

Note that I haven't tried this with a hasMany relationship in this direction, as you have it in your example (selecting products, and each product has many stocks). I've tried only with the hasMany in the other direction (eg selecting stocks, each of which has exactly one product).

Answer 2

You can not apply an order while querying an eagerly loaded relationship in Laravel. You can order the Collections after the query has been performed.

$products = Product::with(array('validStock'))
    ->get()
    ->each(function ($product)
    {
        $product->validStock = $product->validStock
            ->sortBy(function ($validStock)
            {
                return $validStock->created_at;
            })
            ->reverse();
    });

Answer 3

You should return $q in the closure:

Product::with(array('validStock' => function($q) {
    return $q->orderBy('created_at', 'DESC');
}));