WHERE Clause in nested json data Laravel 5

Question

As a follow up question in my last post, I just can't figure out how to use WHERE clause when you have a nested json data. Please see my last post as reference to this.

So, I have this kind of data generated by using relationship in my models:

[
  {
     "id":1,
     "name":"South Luzon",
     "branches":[
        {
           "id":1,
           "machinenum":108,
           "name":"Alaminos",
           "region_id":1,
           "user":{
              "id":52,
              "name":"",
              "email":"[e-mail]",
              "role":0,
              "machinenum":108,
              "created_at":"2016-07-11 05:58:04",
              "updated_at":"2016-07-14 09:49:00",
              "pendings":[
                 {
                    "id":10,
                    "user_id":52,
                    "region":"South Luzon",
                    "branch":"Alaminos",
                    "docdate":"2016-07-14 00:00:00",
                    "ls_or":12345,
                    "por":1,
                    "ls_ci":12345,
                    "ci":2,
                    "ls_ch":12345,
                    "ch":2,
                    "dep":5,
                    "cla":0,
                    "grpo":3,
                    "si":25,
                    "so":62,
                    "sts":2,
                    "disb":3,
                    "arcm":5,
                    "apcm":65,
                    "pint":2,
                    "rc_cash":1,
                    "reason":"Test Reason Alaminos",
                    "created_at":"2016-07-14 09:48:55",
                    "updated_at":"2016-07-14 09:48:55"
                 }
              ]
           }
        }

What I'm trying to do is loop all regions with branches and get the pending of each branches. I'm successfully done the looping with my last SO question. Now, I just want to filter the date when the pending was created.

I'm trying to do this:

$regions = Region::with(array('branches->user->pendings' => function($query) {
    $query->where('created_at', '=', '2016-07-14 09:48:55');
}))->get();

But I'm getting this error:

BadMethodCallException in Builder.php line 2345:
Call to undefined method Illuminate\Database\Query\Builder::branches->user->pendings()

I've also done some research but end up asking here. Thanks in advance and please don't forget to see my last SO question as your reference.

Answer 1

$regions = App\Region::with(['pendings' => function ($query) {
    $query->where('created_at', '=', '2016-07-14 09:48:55');

}])->get();

Answer 2

This code seems to work:

$regions = Region::with(['branches.user.pendings' => function($query){
    $query->where('created_at', 'like', '%2016-07-12%');
}])->get();

Please confirm if it is a best practice or there's a better way to do it.

Answer 3

Cant you do this on the relationships first before you output it in json? You can build this query there so the json output will be clean. Your previous post has an answer that may lead you to the solution.