MYSQL JOIN 创建过滤器

Question

我尝试通过大量搜索 stackoverflow 来找到解决方案，但未能成功。如果我发布的内容可能重复且已得到答复，我很抱歉。

我有 3 张 table :

1) 产品(product_id(PRI)、category_id(FOREIGN)、product_en)

2) 特征(characteristic_id (PRI)、category_id(FOR)、characteristic_en)和

3) Product_chacacteristics(拼写错误)，包括 (product_chacacteristic_id (PRI)、characteristic_id (FOR)、product_id (FOR)、value_en)

这个想法是让产品和特征都按类别分类，并在给出值的第三个表中组合起来。 Fon实例:

产品 A a 有 2 个特性，功耗和光色，值分别为 10 瓦和白色(每个特性仅允许一个值)。

我创建了一个小型系统来显示产品和过滤器，它结合了特征和值并减少了结果。当应用 2 个过滤器时，我当前的查询如下所示:

SELECT products.* 
FROM products 
LEFT JOIN product_chacacteristics 
    ON products.product_id = product_chacacteristics.product_id 
WHERE products.active = 1 
    AND products.category_id = 13 
    AND products.sub_category_id = 21 
    AND ((product_chacacteristics.characteristic_id = '7' 
    AND product_chacacteristics.value_en = '10 Watt') 
    AND (product_chacacteristics.characteristic_id = '8' 
    AND product_chacacteristics.value_en = 'White')) 
GROUP BY products.product_id 
ORDER BY products.product_order

并返回 0 行(应返回 2)...如果应用一个过滤器，则可以正常工作...即

SELECT products.* 
FROM products 
LEFT JOIN product_chacacteristics 
    ON products.product_id = product_chacacteristics.product_id 
WHERE products.active = 1  
    AND products.category_id = 13 
    AND products.sub_category_id = 21 
    AND ((product_chacacteristics.characteristic_id = '8' 
    AND product_chacacteristics.value_en = 'White')) 
GROUP BY products.product_id 
ORDER BY products.product_order

如果过滤器之间使用 OR

SELECT products.* 
FROM products 
JOIN product_chacacteristics 
    ON products.product_id = product_chacacteristics.product_id
WHERE products.active = 1 
    AND products.category_id = 13 
    AND products.sub_category_id = 21 
    AND ((product_chacacteristics.characteristic_id = '7' 
    AND product_chacacteristics.value_en = '10 Watt') 
    **OR** (product_chacacteristics.characteristic_id = '8' 
    AND product_chacacteristics.value_en = 'White')) 
GROUP BY products.product_id 
ORDER BY products.product_order

它返回 5 行，似乎过滤仅通过第一个过滤器完成，而不是组合

但是，查询中可能有超过 3 或 4 个过滤器，这些过滤器包含在我创建的一个小函数中。

如果有人可以帮助我完成上面的查询(2 个过滤器)，以便我了解如何继续......

提前谢谢

Answer 1

不幸的是，每个特征都需要一个单独的JOIN。

SELECT products.* FROM products

    <join for characteristic 7 and value X>

    <join for characteristic 8 and value Y>

WHERE 
-- Condition for all products
( products.active = 1 AND products.category_id = 13 AND products.sub_category_id = 21 )
AND
(
       -- Here the conditions on the values
)
ORDER BY products.product_order

每个 JOIN 的形式为

    LEFT JOIN product_chacacteristics AS f1
    ON (products.product_id = f1.product_id 
        AND f1.characteristic_id = ? AND f1.value_en = ?)


    LEFT JOIN product_chacacteristics AS f2
    ON (products.product_id = f2.product_id 
        AND f2.characteristic_id = ? AND f2.value_en = ?)


    ...
    WHERE
    ...

        (f1.value_en = ?) OR (f2.value_en = ?)

因此您可以使用以下参数来描述每个条件:

- progressive number (goes into "f1, f2, ..." alias)
- characteristic id (e.g. 7)
- its value

然后你还有另一个参数，表明你使用的是 OR 还是 AND。我建议不要使用复杂的表达式 - 只需使用所有 OR 或所有 AND。

每次调用“filter”函数时，您应该返回四个单独的对象:

- the SQL for the JOIN clause
- the two values for the JOIN clause binding
- the SQL for the WHERE clause
- the value (equal to the second one above) for the WHERE binding.

伪代码(PHP)类似于:

$bigJoin = '';
$values1 = array();
$bigWhere = "( products.active = ? AND products.category_id = ? AND products.sub_category_id = ? )";
$values2 = array( 1, 13, 21 );

foreach ($filters as $i => $filter) {
    list($join, $joinvals, $where, $wherevals) = addFilter($i, $filter[$i]);
    $bigJoin .= $join;
    $values1 = array_merge($values1, $joinvals);
    $bigWhere .= " {$AND_or_OR} ( {$where} )";
    $values2 = array_merge($values2, $wherevals);
}

$SQL    = "SELECT products.* FROM products "
        . $bigJoin
        . " WHERE "
        . $bigWhere;
$values = array_merge($values1, $values2);

// prepare statement, bind $values, and execute.