SQL 查询返回适用于包含在类别中的产品的过滤器和适用于剩余产品的过滤器，一旦 1+ 个过滤器活动

本文介绍了SQL 查询返回适用于包含在类别中的产品的过滤器和适用于剩余产品的过滤器，一旦 1+ 个过滤器活动的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在显示产品过滤器列表.仅当产品过滤器适用于所选类别中包含的产品时才会显示.

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.