Yii mergeWith（）在多对多关系中

Question

please help solve one trivial task for writing results:

tables:

flats :: id, dateadd
flatparams :: id, title
flatsoptions :: id, flatid, paramid, value

SQL DOWNLOAD HERE :: http://yadi.sk/d/034so9jIDgfoz CONTROLLER CODE DOWNLOAD HERE :: http://yadi.sk/d/CtGAGZzyDghp7

I need to find flats that are in a region, city, street, apartment - for example:

russia, moscow, papanina, 8

My code looks for all the apartments, which have at least one of these parameters (if OR), and if the AND, then no result because trying to find a field in one table flatsoptions immediately all kinds of parameters. i would like to understand how to look flat on several parameters simultaneously. Thank you!

UPDATED :: 02-12-2013 22-00

$criteria = new CDbCriteria();
$criteria->together = true;

$i = 0;
foreach ($arr as $key => $item) {
    if (in_array($key, $pos)) {
        $criteria->join = 'LEFT JOIN flatsoptions AS flatsoptions'.$i.' ON flatsoptions'.$i.'.flatid = t.id';
        $criteria->addColumnCondition(
            array(
                'flatsoptions'.$i.'.paramid' => $params[$key]['id'],
                'flatsoptions'.$i.'.value' => $item
            ),
            'AND',
            'OR'
        );
        $i++;
    }
}
$flats = Flats::model()->findAll($criteria);

And this code is not working - they not find the alias 'flatsoptions0' in 'where clause';

Updated :: SOLUTION

count($keys) = count($values); This is a Mysql solution::

    $criteria = new CDbCriteria();
    $criteria->select = array('flatid');
    $criteria->addInCondition('paramid', $keys);
    $criteria->addInCondition('value', $values);
    $criteria->group = 'flatid';
    $criteria->having = 'count(*) = '.count($values);

    $flats = Flatsoptions::model()->findAll($criteria);

Answer 1

In AND case you can join flatoptions N times (how many params passed), and use the code similar to:

$criteria->addColumnCondition(
    array(
        'flatsoptions1.paramid' => $params[$key1]['id'],
        'flatsoptions1.value' => $item1
    ),
    'AND',
    'AND'
);

$criteria->addColumnCondition(
    array(
        'flatsoptions2.paramid' => $params[$key2]['id'],
        'flatsoptions2.value' => $item2
    ),
    'AND',
    'AND'
);

...

$criteria->addColumnCondition(
    array(
        'flatsoptionsN.paramid' => $params[$keyN]['id'],
        'flatsoptionsN.value' => $itemN
    ),
    'AND',
    'AND'
);

This is just an example. You need first define $criteria->with dynamically() with different aliases for the same relation and then put each addColumnCondition inside the loop.

LINKS:

CDbCriteria->with()

CActiveRecord->relations()