MYSQL过滤器产品通过属性

Question

如何选择具有由用户指定的所有属性的产品

例如：指定用户属性来搜索产品：41,2,4,6

MYSQL结构是：

表：报价

`id` int(11) NOT NULL auto_increment, 

`title_pl` varchar(100) character set utf8 collate utf8_polish_ci NOT NULL, 

表：offer_att

`id` int(11) NOT NULL auto_increment, 
`offer_id` int(11) NOT NULL, 
`att_id` int(11) NOT NULL, 

表ATT

`id` int(11) NOT NULL auto_increment, 

`title_pl` varchar(255) character set utf8 collate utf8_polish_ci NOT NULL, 

Answer 1

这是你需要什么？

SELECT * 
FROM offer 
WHERE id IN 
    (SELECT offer_id 
    FROM offer_att oa, 
      att a 
    WHERE a.title_pl IN (41, 
          2, 
          4, 
          6) 
     AND a.id = oa.att_id); 

P.S. Here是一个示例小提琴。

Answer 2

如果你想获取仅具有所有4个属性（41,2,4,6）报价在一起，然后如果你想获取该至少有一个报价，你应该使用一个查询

SELECT 
    o.id 
    o.title_pl 
FROM 
    offer o INNER JOIN offer_att oa ON o.id = oa.offer_id AND oa.att_id IN (41,2,4,6) 
GROUP BY o.id 
HAVING COUNT(DISTINCT oa.att_id) = 4 

的属性（41或2或4或6）用这一个：

SELECT 
    o.id 
    o.title_pl 
FROM 
    offer o INNER JOIN offer_att oa ON o.id = oa.offer_id AND oa.att_id IN (41,2,4,6) 

PHP代码来生成对您的SQL查询参数：

<?php 
$attributes = array(41, 2, 4, 6); 
$count = count($attributes); 
$list = implode(',', $attributes); 
$query = ' 
    SELECT 
     o.id 
     o.title_pl 
    FROM 
     offer o INNER JOIN offer_att oa ON o.id = oa.offer_id AND oa.att_id IN (' . $list . ') 
'; 
$db->query($query); 
?>