I have 3 tables - price, shop, and address. Price contains the fields (FKs) address_id, and shop_id. Shop can have many addresses via special table ‘shop2address’ with shop_id and address_id fields (FKs). So there is the following relations:
//Price
'shop' => array(self::BELONGS_TO, 'Shop', 'shop_id'),
'address' => array(self::BELONGS_TO, 'Address', 'address_id'),
//Shop
'addresses' => array(self::MANY_MANY, 'Address', 'shop2address(shop_id, address_id)'),
When searching for prices I’d like to create CDbCriteria involving both address in price and all addresses of shop. So I write:
$criteria->with = array('shop', 'address', 'shop.addresses');
This breaks in a strange manner. In the log I see that Yii first generates an SQL-statement for DISTINCT COUNT prices - to get total count. This SQL-statement is correct - that is it contains ‘shop2address’ table. Next Yii generates SQLstatement to retrieve records. This statement does not contain ‘shop2address’ table, and this produces syntax error because I have conditions on the fields from shop.addresses.
How this can be that Yii generates correct SQL for SELECT COUNT, and omit important part of it while builing the paired SQL for data retrieval? How to fix this?
Thanks in advance.