php - 如果项目有许多(动态)属性，则多个左连接

Question

我有两个表，产品和属性。每个产品可以有很多属性!

当我必须找到具有多个属性的产品时，问题就开始了。

select * from `products` 
left join `pattributes` on `pattributes`.`products_id` = `products`.`id` 
where `products`.`status` = 1 
AND `products`.`sold` = 0 
AND `products`.`deleted` = 0 
AND `products`.`categories_id` = 30 
AND `pattributes`.`attributes_id` = 5 
AND `pattributes`.`values_id` = 10 
AND `pattributes`.`attributes_id` = 4 
AND `pattributes`.`values_id` = 15 
group by `products`.`id` 
order by `products`.`top` desc, 
`products`.`id` desc

因此，如果我正在寻找两个或多个 pattributes，它将返回 0 行。但如果我寻找一种属性，它就可以正常工作。

我不是 mysql 专家，不确定是否必须使用左连接来做到这一点。也许有人知道最好的解决方案？

添加了 SQL Fiddle - 也许会更容易 http://sqlfiddle.com/#!2/b3ada0

Answer 1

对多个 pattributes 使用 IN() ，与多个 values_id 相同

select * from `products` 
left join `pattributes` on `pattributes`.`products_id` = `products`.`id` 
where `products`.`status` = 1 
AND `products`.`sold` = 0 
AND `products`.`deleted` = 0 
AND `products`.`categories_id` = 30 
AND `pattributes`.`attributes_id` IN(5 ,4)// if type is varchar then IN('5' ,'4')
AND `pattributes`.`values_id` IN( 10,15 )
group by `products`.`id` 
order by `products`.`top` desc, 
`products`.`id` desc