Laravel雄辩的嵌套whereHas返回一切

Question

我有这些表的关系:

-categories (has many products)
-products (has many features)
-features

我试图使用它的slug获得一个特殊的类别，并获得类别中的产品，我还想使用(id)通过一些功能过滤产品

我代码:

$category = Category::whereHas('products', function ($query) {
$query->whereHas('features', function ($query2) {
$query2->where('id', 21); // Example id (products where has features with '21' id)
});
})->where('slug', 'category-slug')
->with('products:id,title', 'products.features')->get();

但是代码返回该类别的所有产品(包含或不包含id为21的特性)

解决方案是什么?

Answer 1

您可以尝试点表示法:

$category = Category::whereHas('products.features', function ($query) {
$query->whereKey(21);
})
->where('slug', 'category-slug')
->with('products:id,title', 'products.features')->get();

Answer 2

我认为最好使用存储产品特性的数据透视表。

+------------------+
| categories       |
| features         |
| feature_product  |
| products         |
+------------------+

请注意，数据透视表名称应该在Laravel约定之前按字母顺序排列。

feature_product:

+------------+---------+------+-----+---------+----------------+
| Field      | Type    | Null | Key | Default | Extra          |
+------------+---------+------+-----+---------+----------------+
| id         | int(11) | NO   | PRI | NULL    | auto_increment |
| product_id | int(11) | YES  |     | NULL    |                |
| feature_id | int(11) | YES  |     | NULL    |                |
+------------+---------+------+-----+---------+----------------+

现在添加关系到您的产品模型:

public function features() {
return $this->belongsToMany(Feature::class);
}

查询:

$products = Product::select()
->join('feature_product', function ($join) {
$join->on('feature_product.product_id', 'products.id')
->where('feature_id', 21);
})
->join('categories', function ($join) {
$join->on('products.category_id', 'categories.id')
->where('slug', 'slug-name');
}) 
->get();

现在获取每个产品的功能:

foreach ($products as $product) {
$product->features;
}