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?

Possible duplicate of [How to create multiple where clause query using Laravel Eloquent?](http://stackoverflow.com/questions/19325312/how-to-create-multiple-where-clause-query-using-laravel-eloquent) — Veerendra, Nov 29 '16 at 07:15
First thing your first and second approach makes the same sense just the pattern to write is different. Second you have not even tried the second where clause in both approach. Please post the code with what you have tried for the second where clause. Please refer the example $users = User::with(array( 'posts' => function($query, $title) { $query->where('title', '=', $title); }, 'posts.tags' => function($query) { $query->where('tag_type', '=', 'admin') } ))->get(); — Veerendra, Nov 29 '16 at 07:30
Typo error, done updated. Also, you example code looks different for what i want to achieve. — Jefsama, Nov 29 '16 at 07:50
Is $date a Carbon or DateTime object? And what is $order? You can order by some column of the table. — Alex Lukinov, Nov 29 '16 at 08:04
Hmm, looks like it's alright. Maybe try to add `'='` in `where` and `orderBy` — Alex Lukinov, Nov 29 '16 at 08:31
This should work 100%. Can you check your MySQL log or the raw SQL to confirm what query is being built? — thefallen, Nov 29 '16 at 09:23

Nishanth Matha · Answer 1 · 2016-12-05T05:42:46.100

7

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

edited Dec 05 '16 at 05:42

answered Dec 05 '16 at 05:36

Nishanth Matha

5,993
2
19
28

score 2 · Answer 2 · answered Dec 05 '16 at 14:40

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.

score 2 · Answer 3 · answered Dec 07 '16 at 01:35

2

As your description, both approaches should work well.

Are there some scopes which use ->orderBy() in your User Model so the orderBy() in with closure is confused.

dd($query->toSql()) in with closure maybe helpful to you.

answered Dec 07 '16 at 01:35

forehalo

108
3

score 1 · Accepted Answer · answered Dec 07 '16 at 05:10

I solve the issue by adding a closure to the where() condition.

$user = User::with([
    'products' => function ($query) use ($date, $order) {
        $query->where(function ($q) use ($date, $order) {
            $q->where('created_at', $date); //This can accept more where() condition
        })->orderBy($order,'DESC');
    }])->get();

laravel 5 query multiple constraint on eager load

4 Answers4