laravel 5 query multiple constraint on eager load

Question

In my controller I have following code:

//Example Data;
$date = Carbon::now();
$order = 'name'; // it can be by name, id or created_at;

// Approach i try for getting data of user with eager loaded products

//1st approach
$user = User::with([
    'products' => function ($query) use ($date, $order) {
        $query->where('created_at', $date)->orderBy($order, 'desc');
    },
])->get();

//2nd approach
$user = User::with([
    'products' => function ($query) use ($date, $order) {
        $query->where('created_at', $date);
        $query->orderBy($order, 'desc');
    },
])->get();

On both approach, only the 1st condition of the query is being read.

I want to make 1 where() clause and 1 orderBy to be filtered in the eager loaded data.

Is there something i miss out doing? Do I code it wrong?

Answer 1

Try using nested eager loading. Some thing like:

$user = User::with([
    'products' => function ($query) use ($date) {
            $query->where('created_at', $date);
        },'products.order' =>function ($query) use ($order) {
            $query->orderBy($order, 'desc');
        } 
    ])->get();

Answer 2

The thing is that your orderBy is being made at the "subquery". When laravel does the join (that's what an eager loading is), the order_by will not be relevant to you.

I think an useful solution would be using the orderby clause outside the query, using the alias provided by laravel.

$user = User::with([ 'products' => function ($query) use ($date, $order) { $query->where('created_at', $date)->orderBy($order, 'desc'); }, ])->orderBy("$laravel_join_generated_alias.$order", 'desc')->get(); where the $laravel_join_generated_alias you could use the debugbar to check how this string will work.