php - 数据库 |分面搜索已更改

Question

也许你会帮助我处理我的 sql 查询。我的问题基于另一个问题:Mysql | Faceted search一切都是一样的，我需要相同的结果，但表格有点不同。我无法构建我的查询。请看看这个sql fiddle :

我的表结构:

CREATE TABLE products
    (`id` int, `description` varchar(9), `user_id` int);

INSERT INTO products
    (`id`, `description`, `user_id`)
VALUES
    (1, 'my car', 3),
    (2, 'dream car', 3),
    (3, 'New car', 3),
    (4, 'Old car', 4);

CREATE TABLE fields
    (`id` int, `field_name` varchar(14)); /*meta_name*/

INSERT INTO fields
    (`id`, `field_name`)
VALUES
    (1, 'Make'),
    (2, 'Model'),
    (3, 'Color'),
    (4, 'Car Type'),
    (5, 'Interior Color');

CREATE TABLE fields_values
    (`id` int, `field_id` int, `field_value` varchar(7)); /*meta_value*/

INSERT INTO fields_values
    (`id`, `field_id`, `field_value`)
VALUES
    (1, 1, 'BMW'),
    (2, 2, '3Series'),
    (3, 3, 'White'),
    (4, 4, 'Coupe'),
    (5, 5, 'Black'),
    (6, 1, 'BMW'),
    (7, 2, '2Series'),
    (8, 3, 'Black'),
    (9, 4, 'Coupe'),
    (10, 5, 'Grey'),
    (11, 1, 'Honda'),
    (12, 2, 'Civic'),
    (13, 3, 'Red'),
    (14, 4, 'Sedan'),
    (15, 5, 'Black');

CREATE TABLE products2fields_values
    (`id` int, `product_id` int, `field_value_id` int);
INSERT INTO products2fields_values
    (`id`, `product_id`, `field_value_id`)
VALUES
    (1, 1, 1),
    (2, 1, 2),
    (3, 1, 3),
    (4, 1, 4),
    (5, 1, 5),
    (6, 2, 1),
    (7, 2, 2),
    (8, 2, 3),
    (9, 2, 4),
    (10, 2, 5),
    (11, 3, 1),
    (12, 3, 2),
    (13, 3, 3),
    (14, 3, 4),
    (15, 3, 5); 

还有我的错误查询:

SELECT field_name, field_value, COUNT(DISTINCT pid) count
FROM fields ft 
JOIN fields_values fvt
    ON fvt.field_id = ft.id

JOIN products2fields_values p2fv
    ON p2fv.field_value_id = fvt.id

LEFT JOIN (
    SELECT p.id pid
    FROM products p   
    JOIN products2fields_values p2fv
        ON p2fv.product_id = p.id
    JOIN fields_values fvt
        ON fvt.id = p2fv.field_value_id
    JOIN fields ft
        ON ft.id = fvt.field_id
    GROUP BY p.id
    HAVING MAX(ft.id = 1 AND p2fv.field_value_id = 1) = 1
       AND MAX(ft.id = 4 AND p2fv.field_value_id = 4) = 1
)

LJ ON p2fv.product_id = LJ.pid
GROUP BY field_name, field_value;

我正在尝试获得结果:

|      field_name| field_value| count |
|----------------|------------|-------|
|       Car Type |      Coupe |     2 |
|       Car Type |      Sedan |     0 |
|          Color |      Black |     1 |
|          Color |        Red |     0 |
|          Color |      White |     1 |
| Interior Color |      Black |     2 |
| Interior Color |       Grey |     1 |
|           Make |        BMW |     2 |
|           Make |      Honda |     0 |
|          Model |    2Series |     0 |
|          Model |    3Series |     1 |
|          Model |      Civic |     0 |

Answer 1

据我了解你的问题，这应该是你想要的查询:

SELECT field_name, field_value, COUNT(val.id) as count
FROM fields ft 
JOIN fields_values fvt
ON fvt.field_id = ft.id
LEFT JOIN products2fields_values val
ON val.field_value_id = fvt.id 
GROUP BY  field_name, field_value;

我不知道你为什么要加入你的产品表，因为它似乎对你的结果没有必要。而且我不明白你为什么实现奇怪的 HAVING 子句。

请看一下我的查询结果。