Propel criteria? or is it even possible to create such query?

Question

I have 3 tables: product, product_parameter, product_parameter_item. Product and product parameter have no foreign references, product_parameter_item has product_id and product_parameter_id. For example i have 4 products:

1
2
3
4

Product_parametetrs:

1 - Model
2 - Color

Product_parameter_item:

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

How can I get all products with Model = Toyota, Color = 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);
}

Doesnt work

$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);
}

Doesnt work either

$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

I have made this function to get ids of products and add them to Criteria. Didnt find another way :(

$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;