separating search conditions

hi, i need help constructing search conditions.

i have a model called Client, which contains two addresses, one invoice address and one delivery address. now i want to search over the country, city and zipcode which can be in either one of these addresses.

i have the relations:




public function relations()

	{

		return array(

			'deliveryaddress' => array(self::BELONGS_TO, 'Address', 'deliveryaddress'),

			'invoiceadress' => array(self::BELONGS_TO, 'Address', 'invoiceadress'),

		);

	}



and to the search function i have added this:




                $criteria->with = array('invoiceadress', 'deliveryaddress');

                $criteria->addSearchCondition('invoiceadress.country', $this->country, true, 'AND');

                $criteria->addSearchCondition('deliveryaddress.country', $this->country, true, 'OR');

                $criteria->addSearchCondition('invoiceadress.city', $this->city, true, 'AND');

                $criteria->addSearchCondition('deliveryaddress.city', $this->city, true, 'OR');

                $criteria->addSearchCondition('invoiceadress.zipcode', $this->zipcode, true, 'AND');

                $criteria->addSearchCondition('deliveryaddress.zipcode', $this->zipcode, true, 'OR');



and now comes my problem. searching any of this attributes separately works without problems, but when i use more of them the results are incorrect.

eg. if i choose London as the city it shows the clients from London, but if i add Germany as the country it still shows clients from London although they’re obviously not from Germany.

how should i change this?

The operators of "AND" and "OR" will not merge the conditions in a way as you are expecting.

They will work like the following:




1 (A)

2 ((A) OR <img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='B)' />

3 (((A) OR <img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='B)' /> AND C)

4 ((((A) OR <img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='B)' /> AND C) OR D)

5 (((((A) OR <img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='B)' /> AND C) OR D) AND E)

6 ((((((A) OR <img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='B)' /> AND C) OR D) AND E) OR F)



You can divide the conditions in groups to make separated criterias, and use CDbCriteria::mergeWith() to merge them.




$criteria->with = array('invoiceadress', 'deliveryaddress');

$criteria->addSearchCondition('invoiceadress.country', $this->country, true, 'AND');

$criteria->addSearchCondition('invoiceadress.city', $this->city, true, 'AND');

$criteria->addSearchCondition('invoiceadress.zipcode', $this->zipcode, true, 'AND');


$criteria2 = new CDbCriteria;

$criteria2->addSearchCondition('deliveryaddress.country', $this->country, true, 'AND');

$criteria2->addSearchCondition('deliveryaddress.city', $this->city, true, 'AND');

$criteria2->addSearchCondition('deliveryaddress.zipcode', $this->zipcode, true, 'AND');


$criteria->mergeWith($criteria2, false); // merge using 'OR'



This will combine the conditions as:




((((A) AND <img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='B)' /> AND C) OR (((D) AND E) AND F))



thank you very much :) now it works as expected.