php - Laravel whereHas manyToMany 完全匹配

Question

我有一个 Model 与 manyToMany 关系:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Product extends Model
{
    public function categories()
    {
        return $this->belongsToMany(Category::class, 'product_category', 'product_id', 'category_id');
    }
}

所以 1 个 Product 可以有多个 Categories。

我正在尝试查询(获取)所有与类别完全匹配的产品:

例子:

$categories = ['category1', 'category2'];

Product::whereHas('categories', function($q) use ($categories){

    foreach ($categories as $categoryName) {
       $q->where('name', $categoryName);
    }
    //or $q->whereIn('name', $categories);

})->get();

还试过:

$categories = ['category1', 'category2'];

Product::whereHas('categories', function($q) use ($categories){

    $q->whereIn('name', $categories);

}, '=', count($categories))->get();

假设我的 Product 只附加了 category1，查询应该返回这个产品。

如果 Product 有两个 categories，但我的数组只包含 category1，那么这个 Product 应该被忽略.

所以我试图实现:只获取具有特定类别的产品。它可以用 Eloquent 或 DB builder 实现吗？

伪代码

$allow = for each Product->categories{
    category Exist in $categories
}

Answer 1

根据您的要求，您的 MySQL 查询如下:

• 使用LEFT JOIN 获取产品的映射类别 总数。
• 对产品使用 GROUP BY 以比较其总映射类别与基于用户输入的匹配类别。
• 可以使用HAVING 子句进行上述比较，其中total mapped categories 应该等于用户提供的类别数。相同的基于用户输入的匹配类别也应该与用户提供的类别的确切计数相匹配。

SELECT p.id
FROM products p
LEFT JOIN product_category pc ON p.id = pc.product_id 
LEFT JOIN categories c ON c.id = pc.category_id AND c.name IN ('category1', 'category2')
GROUP BY p.id
HAVING COUNT(0) = 2   -- To match that product should have only two categories
    AND SUM(IF(c.name IN ('category1', 'category2'), 1, 0)) = 2; -- To match that product have only those two categories which user has provided.

同样可以通过查询构建器以下列方式实现:

$arrCategory    =   ['category1', 'category2'];
$strCategory    =   "'" . implode("','", $arrCategory) . "'";
$intLength      =   count($arrCategory);

$products       =   Product::leftJOin('product_category', 'products.id', '=', 'product_category.product_id')
                        ->leftJoin('categories', function ($join) use ($arrCategory) {
                            $join->on('categories.id', '=', 'product_category.category_id')
                                ->whereIn('categories.name', $arrCategory);
                        })
                        ->groupBy('products.id')
                        ->havingRaw("COUNT(0) = $intLength AND SUM(IF(categories.name IN ($strCategory), 1, 0)) = $intLength")
                        ->select(['products.id'])
                        ->get();
dd($products);

注意:如果您在 MySQL 中启用了 only_full_group_by 模式，则在您选择的 group by 和 select 子句中包含 products 表的列想要获取。