mysql - 插入标准？或者甚至可以创建这样的查询吗？

Question

我有 3 个表:产品、product_parameter、product_parameter_item。产品和产品参数没有外部引用，product_parameter_item具有product_id和product_parameter_id。例如我有 4 个产品:

1
2
3
4

产品参数:

1 - Model
2 - Color

产品参数项:

id - product_id - product_paramter_id - value:
1 - 1 - 1 - Toyota
2 - 1 - 2 - white
3 - 2 - 1 - Toyota
4 - 2 - 2 - black
5 - 3 - 1 - Citroen
6 - 3 - 2 - white
7 - 4 - 1 - Citroen
8 - 4 - 1 - black

如何获得型号=丰田、颜色=黑色的所有产品？

$c->addJoin(ProductPeer::ID, ProductParameterItemPeer::PRODUCT_ID);
$c->addJoin(ProductParameterItemPeer::PRODUCT_PARAMETER_ID, ProductParameterPeer::ID);
foreach ($product_params as $i => $param){
   $c1 = $c->getNewCriterion(ProductParameterPeer::ID, $param->getId());
   $c2 = $c->getNewCriterion(ProductParameterItemPeer::VALUE, $value);
   $c1->addAnd($c2);
   $c->addAnd($c1);
}

不起作用

$c->addJoin(ProductPeer::ID, ProductParameterItemPeer::PRODUCT_ID);
foreach ($product_params as $i => $param){
   $c1 = $c->getNewCriterion(ProductParameterItemPeer::ID, $param->getId());
   $c2 = $c->getNewCriterion(ProductParameterItemPeer::VALUE, $value);
   $c1->addAnd($c2);
   $c->addAnd($c1);
}

也不起作用

$c->addJoin(ProductParameterItemPeer::PRODUCT_PARAMETER_ID, ProductParameterPeer::ID);
foreach ($product_params as $i => $param){
   $c->add(ProductParameterPeer::ID, $param->getId());
   $c->add(ProductParameterItemPeer::VALUE, $value);
}

Answer 1

我创建了这个函数来获取产品的 id 并将它们添加到 Criteria 中。没有找到其他方法:(

$product_params = ProductParameterPeer::getParamsForFilter();
$ids = false;
if (count($product_params)){
  $sql = '';
  $clause = '';
  $clauses = array();
  $make_query = false;
  foreach ($product_params as $i => $param){
    if ($values[$param->getAlias()]){
      $is_last = end($product_params) == $param;
      $sql .= '(SELECT product_id FROM  `product_parameter_item` WHERE `value` = "'.$values[$param->getAlias()].'" AND  `product_parameter_id` = '.$param->getId().' ) t'.$i.($is_last?' ':', ');
      $clauses[] = 't'.$i.'.product_id';
      $make_query = 'SELECT t'.$i.'.product_id FROM ';
    }
  }
  if (count($clauses) == 1)      $clause = 'WHERE '.$clauses[0];
  else if (count($clauses) == 2) $clause = 'WHERE '.$clauses[0].'='.$clauses[1];
  else if (count($clauses) > 2){
    $clause = 'WHERE ';
    for ($i = 1; $i < count($clauses); $i++){
      $clause .= '('.$clauses[0].'='.$clauses[$i].')'.($i == count($clauses)-1 ? '' : ' AND ');
    }
  }
  if ($make_query){
    $query = $make_query.$sql.$clause;
    $con = Propel::getConnection();
    $statement = $con->prepare($query);
    $statement->execute();
    $ids = array();
    while($stmt = $statement->fetch(PDO::FETCH_ASSOC))  {
      $ids[] = (int)$stmt['product_id'];
    }
  }
}
return $ids;