如何将产品计数到多个过滤器 [英] How to count products to multiple filters

查看：35
本文介绍了如何将产品计数到多个过滤器的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

更新

我喜欢制作这样的产品过滤器模块 Online Shop ，当您点击其中一个过滤器以计算其他过滤器中的当前产品

例如，如果这是前端并且它们是复选框并且它们是未选中的 atm

大小 (group_id)10m (option_id: 52) (21 个产品)20m (option_id: 51) (1 个产品)颜色(group_id)绿色 (option_id: 49) (22 个产品)黑色 (option_id: 38) (1 个产品)

如果用户选择 10m (option_id: 52) 过滤器计数器应该变成这样

大小 (group_id)10m (option_id: 52) (21 个产品)20m (option_id: 51) (1)颜色(group_id)绿色 (option_id: 49) (2)黑色 (option_id: 38) (0)

我做了一张表，只是为了计算我保留 group_id、option_id、product_id、category_id、manufacturer_id 的位置

我查询要过滤的产品数量，但问题是我无法返回同一类别中的其他过滤器计数器(option_id:38)和同一类别中的所有制造商的制造商计数器，有什么想法吗?

SELECT sd.*, COUNT(sd.product_id) FROM filter_counter sd WHERE sd.product_id IN (SELECT c.product_id FROM filter_counter c WHERE c.option_id IN (52) AND c.category_id = 127) AND sd.category_id = 127 GROUP BY sd.option_id

<预><代码>创建表`filter_counter`(`id` int(11) 非空，`group_id` int(11) 非空，`option_id` int(11) 非空，`product_id` int(11) 非空，`category_id` int(11) 非空，`manufacturer_id` int(11) 非空) 引擎=MyISAM 默认字符集=utf8;插入`filter_counter`(`id`、`group_id`、`option_id`、`product_id`、`category_id`、`manufacturer_id`)值(1, 33, 52, 5124, 65, 36),(2, 33, 52, 5124, 127, 36),(3, 33, 52, 5125, 65, 36),(4, 33, 52, 5125, 127, 36),(5, 33, 52, 5138, 65, 36),(6, 33, 52, 5138, 127, 36),(7, 33, 52, 5141, 65, 36),(8, 33, 52, 5141, 127, 36),(9, 33, 52, 5146, 65, 36),(10, 33, 52, 5146, 127, 36),(11, 33, 52, 5147, 65, 36),(12, 33, 52, 5147, 127, 36),(13, 33, 52, 5148, 65, 36),(14, 33, 52, 5148, 127, 36),(15, 33, 52, 5149, 65, 36),(16, 33, 52, 5149, 127, 36),(17, 33, 52, 5150, 65, 36),(18, 33, 52, 5150, 127, 36),(19, 33, 52, 5151, 65, 36),(20, 33, 52, 5151, 127, 36),(21, 33, 52, 5152, 65, 36),(22, 33, 52, 5152, 127, 36),(23, 33, 52, 5153, 65, 36),(24, 33, 52, 5153, 127, 36),(25, 33, 52, 5154, 65, 36),(26, 33, 52, 5154, 127, 36),(27, 33, 52, 5155, 65, 36),(28, 33, 52, 5155, 127, 36),(29, 33, 52, 5156, 65, 36),(30, 33, 52, 5156, 127, 36),(31, 33, 52, 5157, 65, 36),(32, 33, 52, 5157, 127, 36),(33, 33, 52, 7042, 65, 38),(34, 33, 52, 7042, 127, 38),(35, 33, 52, 7048, 65, 38),(36, 33, 52, 7048, 127, 38),(37, 33, 52, 7124, 65, 0),(38, 33, 52, 7124, 127, 0),(39, 32, 49, 7185, 65, 0),(40, 32, 49, 7185, 127, 0),(41, 32, 49, 7517, 65, 39),(42, 32, 49, 7517, 127, 39),(43, 32, 49, 7518, 65, 39),(44, 32, 49, 7518, 127, 39),(45, 32, 49, 7538, 65, 39),(46, 32, 49, 7538, 127, 39),(47, 32, 49, 7657, 65, 39),(48, 32, 49, 7657, 127, 39),(49, 32, 49, 7658, 65, 39),(50, 32, 49, 7658, 127, 39),(51, 32, 49, 7797, 65, 21),(52, 32, 49, 7797, 127, 21),(53, 32, 49, 7798, 65, 21),(54, 32, 49, 7798, 127, 21),(55, 32, 49, 7799, 65, 21),(56, 32, 49, 7799, 127, 21),(57, 32, 49, 7800, 65, 21),(58, 32, 49, 7800, 127, 21),(59, 32, 49, 7801, 65, 21),(60, 32, 49, 7801, 127, 21),(61, 32, 49, 7802, 65, 21),(62, 32, 49, 7802, 127, 21),(63, 32, 49, 7803, 65, 21),(64, 32, 49, 7803, 127, 21),(65, 32, 49, 7804, 65, 21),(66, 32, 49, 7804, 127, 21),(67, 32, 49, 7805, 65, 21),(68, 32, 49, 7805, 127, 21),(69, 32, 49, 7806, 65, 21),(70, 32, 49, 7806, 127, 21),(71, 32, 49, 7807, 65, 21),(72, 32, 49, 7807, 127, 21),(73, 32, 49, 7808, 65, 21),(74, 32, 49, 7808, 127, 21),(75, 32, 49, 7809, 65, 21),(76, 32, 49, 7809, 127, 21),(77, 32, 49, 7810, 65, 21),(78, 32, 49, 7810, 127, 21),(79, 29, 38, 7811, 65, 21),(80, 29, 38, 7811, 127, 21),(81, 32, 49, 8020, 65, 21),(82, 32, 49, 8020, 127, 21),(83, 33, 52, 8020, 65, 21),(84, 33, 52, 8020, 127, 21),(85, 32, 49, 8021, 65, 21),(86, 32, 49, 8021, 127, 21),(87, 33, 51, 8021, 65, 21),(88, 33, 51, 8021, 127, 21),(89, 33, 52, 8021, 65, 21),(90、33、52、8021、127、21)；

我的数据库和查询示例

解决方案

例如，如果这是前端并且它们是复选框并且它们是未选中的 atm

大小 (group_id)10m (option_id: 52) (21 个产品)20m (option_id: 51) (1 个产品)颜色(group_id)绿色 (option_id: 49) (22 个产品)黑色 (option_id: 38) (1 个产品)

SELECT COUNT(DISTINCT CASE WHEN option_id = 52 THEN product_id END) p52,计数(DISTINCT CASE WHEN option_id = 51 THEN product_id END)p51，COUNT(DISTINCT CASE WHEN option_id = 49 THEN product_id END) p49,计数(DISTINCT CASE WHEN option_id = 38 THEN product_id END)p38从过滤器计数器；

<块引用>

如果用户选择 10m (option_id: 52) 过滤器计数器应该变成这样

大小 (group_id)10m (option_id: 52) (21 个产品)20m (option_id: 51) (1)颜色(group_id)绿色 (option_id: 49) (2)黑色 (option_id: 38) (0)

SELECT COUNT(DISTINCT CASE WHEN option_id = 52 THEN product_id END) p52,计数(DISTINCT CASE WHEN option_id = 51 THEN product_id END)p51，COUNT(DISTINCT CASE WHEN option_id = 49 THEN product_id END) p49,计数(DISTINCT CASE WHEN option_id = 38 THEN product_id END)p38FROM filter_counterJOIN ( SELECT DISTINCT product_idFROM filter_counterWHERE option_id IN (52) ) filter1 USING (product_id);

小提琴

如果用户检查同一组中的其他过滤器，则将下一个值添加到条件中.例如，如果他检查与 option_id = 52 在同一组中的 option_id = 51 然后过滤子查询变为

JOIN ( SELECT DISTINCT product_idFROM filter_counterWHERE option_id IN (52, 51) ) filter1 USING (product_id)

如果用户检查另一个组中的附加过滤器，则添加附加的 filterX 子查询.例如，如果他检查 option_id = 49 然后添加到查询的下一个子查询:

JOIN ( SELECT DISTINCT product_idFROM filter_counterWHERE option_id IN (49) ) filter2 USING (product_id)

<块引用>

有没有办法让输出变成每行一个?

SELECT options.option_id,COUNT(DISTINCT CASE WHEN filter_counter.option_id = options.option_idTHEN product_idEND) option_countFROM filter_counterCROSS JOIN ( SELECT DISTINCT option_idFROM filter_counter ) 选项JOIN ( SELECT DISTINCT product_idFROM filter_counterWHERE option_id IN (52) ) filter1 USING (product_id)GROUP BY options.option_id;

fiddle

UPDATED

I like to make product filter module like this one Online Shop , when you click one of the filters to count current products in other filters

For example if this is frontend and their are checkboxes and their are UNchecked atm

Size (group_id)
  10m (option_id: 52) (21 products)
  20m (option_id: 51) (1 product)

Color (group_id)
   Green (option_id: 49) (22 products)
   Black (option_id: 38) (1 product)

If a user select 10m (option_id: 52) the filter counter should become like this

Size (group_id)
  10m (option_id: 52) (21 products)
  20m (option_id: 51) (1)

Color (group_id)
   Green (option_id: 49) (2)
   Black (option_id: 38) (0)

I made one table only for counting where i keep group_id,option_id,product_id,category_id,manufacturer_id

I make query that count products to filter but problem is that i cant return other filter counter (option_id: 38) in same category and manufacturer counter for all manufacturer in same category , any ideas how to make it ?

SELECT sd.*, COUNT(sd.product_id) FROM filter_counter sd WHERE sd.product_id IN (SELECT c.product_id FROM filter_counter c WHERE c.option_id IN (52) AND c.category_id = 127) AND sd.category_id = 127 GROUP BY sd.option_id



CREATE TABLE `filter_counter` (
  `id` int(11) NOT NULL,
  `group_id` int(11) NOT NULL,
  `option_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  `manufacturer_id` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `filter_counter` (`id`, `group_id`, `option_id`, `product_id`, `category_id`, `manufacturer_id`) VALUES
(1, 33, 52, 5124, 65, 36),
(2, 33, 52, 5124, 127, 36),
(3, 33, 52, 5125, 65, 36),
(4, 33, 52, 5125, 127, 36),
(5, 33, 52, 5138, 65, 36),
(6, 33, 52, 5138, 127, 36),
(7, 33, 52, 5141, 65, 36),
(8, 33, 52, 5141, 127, 36),
(9, 33, 52, 5146, 65, 36),
(10, 33, 52, 5146, 127, 36),
(11, 33, 52, 5147, 65, 36),
(12, 33, 52, 5147, 127, 36),
(13, 33, 52, 5148, 65, 36),
(14, 33, 52, 5148, 127, 36),
(15, 33, 52, 5149, 65, 36),
(16, 33, 52, 5149, 127, 36),
(17, 33, 52, 5150, 65, 36),
(18, 33, 52, 5150, 127, 36),
(19, 33, 52, 5151, 65, 36),
(20, 33, 52, 5151, 127, 36),
(21, 33, 52, 5152, 65, 36),
(22, 33, 52, 5152, 127, 36),
(23, 33, 52, 5153, 65, 36),
(24, 33, 52, 5153, 127, 36),
(25, 33, 52, 5154, 65, 36),
(26, 33, 52, 5154, 127, 36),
(27, 33, 52, 5155, 65, 36),
(28, 33, 52, 5155, 127, 36),
(29, 33, 52, 5156, 65, 36),
(30, 33, 52, 5156, 127, 36),
(31, 33, 52, 5157, 65, 36),
(32, 33, 52, 5157, 127, 36),
(33, 33, 52, 7042, 65, 38),
(34, 33, 52, 7042, 127, 38),
(35, 33, 52, 7048, 65, 38),
(36, 33, 52, 7048, 127, 38),
(37, 33, 52, 7124, 65, 0),
(38, 33, 52, 7124, 127, 0),
(39, 32, 49, 7185, 65, 0),
(40, 32, 49, 7185, 127, 0),
(41, 32, 49, 7517, 65, 39),
(42, 32, 49, 7517, 127, 39),
(43, 32, 49, 7518, 65, 39),
(44, 32, 49, 7518, 127, 39),
(45, 32, 49, 7538, 65, 39),
(46, 32, 49, 7538, 127, 39),
(47, 32, 49, 7657, 65, 39),
(48, 32, 49, 7657, 127, 39),
(49, 32, 49, 7658, 65, 39),
(50, 32, 49, 7658, 127, 39),
(51, 32, 49, 7797, 65, 21),
(52, 32, 49, 7797, 127, 21),
(53, 32, 49, 7798, 65, 21),
(54, 32, 49, 7798, 127, 21),
(55, 32, 49, 7799, 65, 21),
(56, 32, 49, 7799, 127, 21),
(57, 32, 49, 7800, 65, 21),
(58, 32, 49, 7800, 127, 21),
(59, 32, 49, 7801, 65, 21),
(60, 32, 49, 7801, 127, 21),
(61, 32, 49, 7802, 65, 21),
(62, 32, 49, 7802, 127, 21),
(63, 32, 49, 7803, 65, 21),
(64, 32, 49, 7803, 127, 21),
(65, 32, 49, 7804, 65, 21),
(66, 32, 49, 7804, 127, 21),
(67, 32, 49, 7805, 65, 21),
(68, 32, 49, 7805, 127, 21),
(69, 32, 49, 7806, 65, 21),
(70, 32, 49, 7806, 127, 21),
(71, 32, 49, 7807, 65, 21),
(72, 32, 49, 7807, 127, 21),
(73, 32, 49, 7808, 65, 21),
(74, 32, 49, 7808, 127, 21),
(75, 32, 49, 7809, 65, 21),
(76, 32, 49, 7809, 127, 21),
(77, 32, 49, 7810, 65, 21),
(78, 32, 49, 7810, 127, 21),
(79, 29, 38, 7811, 65, 21),
(80, 29, 38, 7811, 127, 21),
(81, 32, 49, 8020, 65, 21),
(82, 32, 49, 8020, 127, 21),
(83, 33, 52, 8020, 65, 21),
(84, 33, 52, 8020, 127, 21),
(85, 32, 49, 8021, 65, 21),
(86, 32, 49, 8021, 127, 21),
(87, 33, 51, 8021, 65, 21),
(88, 33, 51, 8021, 127, 21),
(89, 33, 52, 8021, 65, 21),
(90, 33, 52, 8021, 127, 21);

My database and query example

解决方案

For example if this is frontend and their are checkboxes and their are UNchecked atm

Size (group_id)
 10m (option_id: 52) (21 products)
 20m (option_id: 51) (1 product)

Color (group_id)
  Green (option_id: 49) (22 products)
  Black (option_id: 38) (1 product)


SELECT COUNT(DISTINCT CASE WHEN option_id = 52 THEN product_id END) p52,
       COUNT(DISTINCT CASE WHEN option_id = 51 THEN product_id END) p51,
       COUNT(DISTINCT CASE WHEN option_id = 49 THEN product_id END) p49,
       COUNT(DISTINCT CASE WHEN option_id = 38 THEN product_id END) p38
FROM filter_counter;

If a user select 10m (option_id: 52) the filter counter should become like this

Size (group_id)
 10m (option_id: 52) (21 products)
 20m (option_id: 51) (1)

Color (group_id)
  Green (option_id: 49) (2)
  Black (option_id: 38) (0)


SELECT COUNT(DISTINCT CASE WHEN option_id = 52 THEN product_id END) p52,
       COUNT(DISTINCT CASE WHEN option_id = 51 THEN product_id END) p51,
       COUNT(DISTINCT CASE WHEN option_id = 49 THEN product_id END) p49,
       COUNT(DISTINCT CASE WHEN option_id = 38 THEN product_id END) p38
FROM filter_counter
JOIN ( SELECT DISTINCT product_id
       FROM filter_counter
       WHERE option_id IN (52) ) filter1 USING (product_id);

fiddle

If user checks additional filter in the same group then the next value is added into the condition. For example, if he checks option_id = 51 which is in the same group with option_id = 52 then filtering subquery become

JOIN ( SELECT DISTINCT product_id
       FROM filter_counter
       WHERE option_id IN (52, 51) ) filter1 USING (product_id)

If user checks additional filter in another group then additional filterX subquery added. For example, if he checks option_id = 49 then the next subquery added to the query:

JOIN ( SELECT DISTINCT product_id
       FROM filter_counter
       WHERE option_id IN (49) ) filter2 USING (product_id)

is there any way output to become one per line ?

SELECT options.option_id,
       COUNT(DISTINCT CASE WHEN filter_counter.option_id = options.option_id 
                           THEN product_id 
                           END) option_count
FROM filter_counter
CROSS JOIN ( SELECT DISTINCT option_id
             FROM filter_counter ) options
JOIN ( SELECT DISTINCT product_id
       FROM filter_counter
       WHERE option_id IN (52) ) filter1 USING (product_id)
GROUP BY options.option_id;

fiddle

这篇关于如何将产品计数到多个过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆