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