SQL 查询返回适用于包含在类别中的产品的过滤器和适用于剩余产品的过滤器，一旦 1+ 个过滤器活动 [英] SQL Query to return filters applicable to products contained in category AND filters that apply from remaining products once 1+ filters active
问题描述
我正在显示产品过滤器列表.
仅当产品过滤器适用于所选类别中包含的产品时才会显示.
I'm displaying a list of product filters.
Product filters are only displayed if they are applicable to the products contained within a chosen category.
我想进一步扩展它，因此一旦激活过滤器，就不会输出不适用的过滤器.
I want to expand that further so once a filter has been activated, the non-applicable filters are not outputted.
从下面 products_filters_to_products 中的数据样本来看:
So from the data sample below in products_filters_to_products:
- 一旦选择橙色作为颜色
- 只应提供小尺寸
到目前为止的查询返回适用的过滤器类别、属于这些过滤器类别的过滤器以及它们适用的产品.
Query so far returns applicable filter categories, filters belonging to those filter categories and products that they apply to.
到目前为止的表格和查询如下所示.
Tables and query so far shown below.
表格
products_filters_to_products
-------------------
pftp_pf_id pftp_products_id
3 1
4 1
5 2
product_filters
-------------------
pf_id pf_name pf_to_pfc_id
1 Red 1
2 Blue 1
3 Orange 1
4 Small 2
5 Medium 2
product_filters_categories
-------------------
pfc_id pfc_name
1 Colour
2 Size
products_to_categories
-------------------
products_id categories_id
1 8
2 9
products
-------------------
products_id products_status
1 1
2 1
查询
SELECT pfc.pfc_id
, pfc.pfc_name
, pf.pf_id
, pf.pf_name
, COUNT(pftp.pftp_pf_id) products_in_filter
FROM products_filters_to_products pftp
LEFT
JOIN product_filters pf
ON pftp.pftp_pf_id = pf.pf_id
LEFT
JOIN product_filters_categories pfc
ON pf.pf_to_pfc_id = pfc_id
WHERE pftp_products_id IN ( SELECT ptc.products_id
FROM products_to_categories ptc
LEFT
JOIN products p
ON ptc.products_id = p.products_id
WHERE p.products_status = 1
AND ptc.categories_id = 2479 )
GROUP
BY pfc.pfc_id
, pftp.pftp_pf_id
ORDER
BY pfc.pfc_name ASC
, pf.pf_name ASC
推荐答案
当过滤器类别 1(颜色)已经设置为一个值(3= 橙色).
You want to show all still matching filter values for filter category 2 (Size), when filter category 1 (Color) already has been set to a value (3 = Orange).
为了找到它们，我们会查找与类别/值 1(颜色)/3(橙色)匹配的所有产品.这只是产品 1.产品 1 和过滤器类别 2(尺寸)的唯一过滤器值为值 4(小).
In order to find them, we'd look up all products matching category/value 1(Color)/3(Orange). This is only product 1. And the only filter value for product 1 and filter category 2(Size) is value 4(Small).
让我们让场景更复杂:假设有四个过滤器类别:1、2、3 和 4.对于过滤器类别 2，已选择值 22，为过滤器类别 4 选择值 44.现在，哪些值是可用于其他过滤器类别(1 和 3)?
Let's make the scenario more complex: Let there be four filter categories: 1, 2, 3, and 4. For filter category 2 the value 22 has been chosen and for filter category 4 the value 44. Now, which values are available for the other filter categories (1 and 3)?
首先，我们必须获取与 2/22 和 4/44 都匹配的所有项目 ID.这比仅仅查找一个过滤器要复杂得多.处理这个问题的典型方法是聚合:
First we must get all item IDs that match both 2/22 and 4/44. This is more complex than just looking up one filter. The typical way of dealing with this is an aggregation:
select pftp.pftp_products_id
from products_filters_to_products pftp
join product_filters pf on pf.pf_id = pftp.pftp_pf_id
group by pftp.pftp_products_id
having sum(pf.pf_id = 22) > 0
and sum(pf.pf_id = 44) > 0;
(这种查询在处理键/值表时非常常见，就像这里的情况.)通过找到的产品 ID，我们查找其他过滤器类别的所有值:
(Such queries are very common when dealing with key/value tables as is the case here.) With the product IDs found we look up all their values for the other filter categories:
select pf_to_pfc_id, pf_id, pf_name
from product_filters
where pf_id in
(
select pftp_pf_id
from products_filters_to_products
where pftp_products_id in (<above query>)
)
and pf_to_pfc_id not in
(
select pf_to_pfc_id
from product_filters
and pf_id in (22, 44)
)
order by pf_to_pfc_id, pf_id;
如果您只需要过滤器类别 3 的可用过滤器值，请将 pf_to_pfc_id not in (...) 替换为 pf_to_pfc_id = 3.
If you want only the available filter values for filter category 3, then replace pf_to_pfc_id not in (...) with pf_to_pfc_id = 3.
这篇关于SQL 查询返回适用于包含在类别中的产品的过滤器和适用于剩余产品的过滤器，一旦 1+ 个过滤器活动的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
