数据库 |高级分面搜索

Question

我有表:

CREATE TABLE IF NOT EXISTS `category` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `category` (`id`, `name`) VALUES
(1, 'Computers'),
(2, 'Bikes');

CREATE TABLE IF NOT EXISTS `fields` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `field_name` varchar(255) DEFAULT NULL,
  `cid` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

INSERT INTO `fields` (`id`, `field_name`, `cid`) VALUES
(1, 'Processor', '1'),
(2, 'Display', '1'),
(3, 'Brand', '2');

CREATE TABLE IF NOT EXISTS `fields_values` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `field_id` int(11) DEFAULT NULL,
  `field_value` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

INSERT INTO `fields_values` (`id`, `field_id`, `field_value`) VALUES
(1, 1, 'Intel Pentium 3'),
(2, 2, '27 inch'),
(3, 3, 'BMX'),
(4, 1, 'AMD Radeon'),
(5, 1, 'Intel Atom'),
(6, 2, '22 inch');

CREATE TABLE IF NOT EXISTS `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `cid` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;

INSERT INTO `products` (`id`, `name`, `cid`) VALUES
(1, 'Computer1', 1),
(2, 'Bike1.BMX', 2),
(3, 'Bike3', 2),
(4, 'Intel Atom', 1),
(5, 'Computer Radeon', 1);

CREATE TABLE IF NOT EXISTS `products_to_fields_values` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) DEFAULT NULL,
  `field_value_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

INSERT INTO `products_to_fields_values` (`id`, `product_id`, `field_value_id`) VALUES
(1, 1, 1),
(2, 2, 3),
(3, 1, 2),
(4, 4, 5);

我的请求是这样的:

SELECT ft.id field_id, ft.field_name, fvt.field_value, fvt.id field_value_id, COUNT( DISTINCT pid ) count
FROM FIELDS ft
JOIN fields_values fvt ON ( ft.id = fvt.field_id ) 
JOIN products_to_fields_values pfv ON ( pfv.field_value_id = fvt.id ) 
JOIN products pt ON ( pt.id = pfv.product_id ) 
LEFT JOIN (

SELECT ft.id field_id, ft.field_name, fvt.field_value, fvt.id field_value_id, pt.name, pt.id pid
FROM FIELDS ft
JOIN fields_values fvt ON ( ft.id = fvt.field_id ) 
JOIN products_to_fields_values pfv ON ( pfv.field_value_id = fvt.id ) 
JOIN products pt ON ( pt.id = pfv.product_id ) 
GROUP BY pt.id
)LJ ON pfv.product_id = LJ.pid
WHERE FIND_IN_SET( 1, pt.cid ) 
GROUP BY ft.field_name, fvt.field_value
LIMIT 0 , 30

此请求将返回(我正在尝试构建分面过滤器):

field_id field_name field_value field_value_id count
2        Display    27 inch     2              1
1        Processor  Intel Atom  5              1
1        Processor  Intel Pentium 3            1

但我在此表中还有其他值:fields_values，例如:AMD Radeon 和 22 英寸。我在请求中的错误在哪里？谢谢!

编辑:

我期望得到结果:

field_id   field_name   field_value   field_value_id   count
2          Display      22 inch       6                0
2          Display      27 inch       2                1
1          Processor    AMD Radeon    4                0
1          Processor    Intel Atom    5                1
1          Processor    IntelPentium3 1                1

其中 count 是产品计数。

Answer 1

这是一个工作 SQL，但我不确定您是否正确构建了结构。

SELECT
  ft.id field_id, ft.field_name, fvt.field_value, fvt.id as field_value_id, COUNT( DISTINCT pid ) count
FROM products AS pt
JOIN products_to_fields_values AS pfv ON pfv.product_id = pt.id
JOIN fields_values AS fvt ON fvt.field_id = pfv.field_value_id
JOIN fields AS ft on ft.id = fvt.field_id
LEFT JOIN (

SELECT ft.id field_id, ft.field_name, fvt.field_value, fvt.id field_value_id, pt.name, pt.id pid
FROM fields ft
JOIN fields_values fvt ON ( ft.id = fvt.field_id ) 
JOIN products_to_fields_values pfv ON ( pfv.field_value_id = fvt.id ) 
JOIN products pt ON ( pt.id = pfv.product_id ) 
GROUP BY pt.id
)LJ ON pfv.product_id = LJ.pid

WHERE FIND_IN_SET( 1, pt.cid ) 
GROUP BY ft.field_name, fvt.field_value
LIMIT 0 , 30

简而言之 - 你在这一行中有错误:

JOIN products_to_fields_values pfv ON ( pfv.field_value_id = fvt.id )

右一:

JOIN products_to_fields_values pfv ON ( pfv.field_value_id = fvt.field_id )