php - 动态匹配多个类别的项目

Question

给出下表:

CREATE TABLE catalog_categories (
  cat_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  parent_id INTEGER UNSIGNED DEFAULT NULL,
  title VARCHAR(255) NOT NULL,
  valid TINYINT(1) UNSIGNED NOT NULL DEFAULT 1,

  PRIMARY KEY(cat_id),
  FOREIGN KEY (parent_id)
    REFERENCES catalog_categories(cat_id)
);

CREATE TABLE catalog_item_categories (
  item_id INTEGER UNSIGNED NOT NULL,
  cat_id INTEGER UNSIGNED NOT NULL,
  valid TINYINT(1) UNSIGNED NOT NULL DEFAULT 1,

  FOREIGN KEY (item_id)
    REFERENCES catalog_items(item_id),
  FOREIGN KEY (cat_id)
    REFERENCES catalog_categories(cat_id)
);

以及一个多维数组作为输入，其中 key =>parent_id, value=>cat_ids

["categories"]=>
array(2) {
  [1]=>
  array(2) {
    [0]=>
    string(1) "5"
    [1]=>
    string(1) "6"
  }
  [2]=>
  array(1) {
    [0]=>
    string(2) "12"
  }
}

我试图仅选择与提供的类别匹配的项目。

SELECT    a.item_id
FROM      catalog_items AS a
JOIN      catalog_item_categories AS b ON a.item_id = b.item_id
JOIN      catalog_categories AS c ON b.cat_id = c.cat_id AND c.parent_id = 1 AND c.cat_id IN ('5', '6')
JOIN      catalog_categories AS d ON b.cat_id = d.cat_id AND d.parent_id = 2 AND d.cat_id IN ('12')
WHERE     a.valid = TRUE
          AND b.valid = TRUE
          AND c.valid = TRUE
          AND d.valid = TRUE

这是我的硬编码尝试，但是有没有更好的方法，或者我将如何动态构建此查询？前提是我事先不知道会有多少个parent => child 关系。

Answer 1

考虑循环遍历类别，使用动态 PHP 字符串构建 SQL 查询。当然，如果数组数据源自用户输入，请使用 parameterization而不是串联:

$categories = [
    1 => array("5","6"),
    2 => array("12")
];

$sql = "SELECT a.item_id
        FROM   catalog_items AS a 
        JOIN   catalog_item_categories AS b ON a.item_id = b.item_id";            
$i = 1;
foreach($categories as $k=>$v){        
    $cats = implode(",", $v);      

    $sql = $sql."
        JOIN catalog_categories AS c$i ON b.cat_id = c$i.cat_id AND c$i.parent_id = $k 
             AND c$i.cat_id IN ($cats) AND c$i.valid = TRUE";    
    $i = $i + 1;
}

$sql = $sql.
       "\nWHERE a.valid = TRUE AND b.valid = TRUE";

echo $sql;

输出

SELECT a.item_id
FROM catalog_items AS a 
JOIN catalog_item_categories AS b ON a.item_id = b.item_id
JOIN catalog_categories AS c1 ON b.cat_id = c1.cat_id AND c1.parent_id = 1 
     AND c1.cat_id IN (5,6) AND c1.valid = TRUE
JOIN catalog_categories AS c2 ON b.cat_id = c2.cat_id AND c2.parent_id = 2 
     AND c2.cat_id IN (12) AND c2.valid = TRUE
WHERE a.valid = TRUE AND b.valid = TRUE