如何在LARAVEL中使用whereHas来选择非空数据

Question

让我简单点，在我的模型中，我有国家城市和。我的目标是，我只想选择并显示城市有商店还应该有datas员工不能是NULL

在下面给定的数组中，mycity有两个商店其中id - 13没有任何数据，id - 19有数据。

[
{
"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
}
]
}
]
}
]

这是我在控制器中编写的代码。我的目标是，我想选择并只显示那些有员工的商店的城市。

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

这些是我的模型，它们每个都有一个hasMany关系

<<p>国家模型/strong>

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

<<p>城市模型/strong>

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

<<p>商店模型/strong>

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

Answer 1

你可以试试

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

Answer 2

我找到了另一个用子句查询的解决方案。

return Country::whereHas('cities',function($cities){
$cities->where('country_id',1);
})->with(['cities'=>function($shops){
$shops->has('shops')->where('cities.country_id',1);
},'cities.shops'=>function($employee){
$employee->withCount('employees');
}])->get();