How to select not null datas using whereHas in LARAVEL

Question

Let me make it simple, In my models, I have countries and cities. My goal is, I want to select and display only the cities that has shops and it should also have datas and the employees should not be NULL

In the below given array, my city has two shops where id - 13 that doesn't have any datas and id - 19 which is having data.

[
    {
    "id": 1,
    "created_at": "2022-06-02T06:07:31.000000Z",
    "updated_at": "2022-06-02T06:07:31.000000Z",
    "name": "Niue",
    "cities": [
                {
                "id": 13,
                "created_at": "2022-06-02T06:07:44.000000Z",
                "updated_at": "2022-06-02T06:07:44.000000Z",
                "country_id": 1,
                "name": "North Michealbury",
                "shops": [] //empty
                },

                {
                "id": 19,
                "created_at": "2022-06-02T06:07:44.000000Z",
                "updated_at": "2022-06-02T06:07:44.000000Z",
                "country_id": 1,
                "name": "Millsmouth",
                "shops": [
                            {
                              "id": 1,
                              "created_at": "2022-06-02T06:14:37.000000Z",
                              "updated_at": "2022-06-02T06:14:37.000000Z",
                              "city_id": 19,
                              "name": "Roberts-Okuneva",
                              "employees_count": 146
                            }
                        ]
                }
            ]
    }
]

This is the code that, I have written in my controller. The goal is, I want to select and show only those cities that has shops with employees.

   return Country::with(['cities','cities.shops'=>function($employee){
        $employee->withCount('employees')->where('city_id',19);
    }])->whereHas('cities',function($city){
        $city->where('cities.id',19);
    })->get();

These are my models, each of them has a hasMany relationship

Country model

 public function Cities(){
        return $this->hasMany(City::class);
    }

City model

public function shops(){
    return $this->hasMany(Shop::class);
}

Shop model

   public function employees(){
        return $this->belongsToMany(User::class,'shop_employees','shop_id','employee_id');
    }

Answer 1

You can try

//Get all cities which have shops having at least one employee
return Country::with([
    'cities',
    'cities.shops' => function($query) {
        $query->withCount('employees');
    }
])
->whereHas('cities', function($query) {
    $query->whereHas('shops', function($query) {
        $query->has('employees');
    });
})
->get();