mysql - Laravel 计数类别加入

Question

我最终需要从中查询三个表，并且我正在寻找最有效的方法。

这些表是:产品、产品颜色和类别。

关系为 products.category_id =>categories.id 和product_colors.product_id => products.id(按product_colors.color_id 过滤)

首先，我有一个产品表，该产品与颜色表具有一对多关系，因此我需要根据颜色对其进行过滤。其次，我需要按类别对其进行过滤，并返回其适合的每个类别中的产品数量。

我有以下查询，它最终会计算我想要的每个类别中的产品，但它不会根据第一个颜色过滤器进行过滤。

SELECT categories.name, SUM(IF(category_id IS NULL, 0, 1)) AS categories         
FROM products     JOIN categories ON (categories.id = products.category_id) 
WHERE(products.gender = 1)     GROUP BY categories.id;

我不太熟悉在查询或连接方面什么是有效的或无效的，但我想的可能是获取与正在过滤的颜色相匹配的产品 ID 列表，然后以某种方式进行连接这些 ID。

编辑:在下面的回答中，我提出了一个解决方案，但它使用的是 DB:raw - 有什么办法解决这个问题吗？

Answer 1

我设法提出了以下查询，它将有效地返回我需要的内容，并且还可以选择按性别排序。

mysql> SELECT categories.name, SUM(IF(category_id IS NULL, 0, 1)) AS 
categories FROM products LEFT JOIN product_colors ON 
(product_colors.product_id = products.id) LEFT OUTER JOIN categories 
ON (categories.id = products.category_id) WHERE(products.gender = 1 AND 
product_colors.color_id = 1) GROUP BY categories.id;


+--------------+------------+
| name         | categories |
+--------------+------------+
| トップス     |         35 |
| アウター     |          1 |
| ボトムス     |          7 |
| シューズ     |         21 |
| 帽子         |          2 |
+--------------+------------+
5 rows in set (0.00 sec)

编辑:我用 Eloquent 重写了它。

$categories = Category::select('categories.name',DB::raw('SUM(IF(category_id     IS NULL, 0, 1)) AS categories'))
        ->join('products','categories.id','=','products.category_id')
        ->join('product_colors','product_colors.product_id','=','products.id','left outer')
        ->where('products.gender','=',1)
        ->where('product_colors.color_id','=',1)
        ->groupBy('categories.id')
        ->get();

总的来说，我觉得这个解决方案还不错，但我不喜欢它使用 DB:raw。如果有人有替代方案，我将不胜感激。