Jointype = 'inner Join' Not Working

Yii 1.1 General Discussion
1

Hi All - I’m facing an issue in fetching data using relationship. Below is the detail:

  1. I’m having two tables. First is “item_master” and Second is “inventory” table. Since both are having MANY_MANY relationship, so we are having another table named “inventory_items”. “inventory_items” table is having only two fields: item_id, inventory_id.

  2. Below is the relationship detail in ItemMaster Model:

‘inventories’ => array(self::MANY_MANY, ‘Inventories’, ‘inventory_items(item_id, inventory_id)’),

‘inventoryItems’=>array(self::HAS_MANY,‘InventoryItems’,‘item_id’),

  1. I need to fetch Items of a particular Inventory only. For this, I’m implementing below filter criteria:

$criteria=new CDbCriteria;

$inventory_condition[‘condition’]=“inventoryItems.inventory_id=:inventoryID”;

$inventory_condition[‘params’]=array(’:inventoryID’=>$filter[‘inventory_id’]);

$inventory_condition[‘joinType’]=‘INNER JOIN’;

$criteria->with=array("inventoryItems"=>$inventory_condition);

$items=new CActiveDataProvider(‘ItemMaster’,array(‘criteria’=>$criteria));

Problem:

Above code is returning me all the items with the required inventory. And if an item does not belong to that inventory than still it returns that Item but without inventory. I just need that only those Items should return which belong to a particular inventory.

Please suggest.

Thanks,

Ravi Verma

2

Try:


$criteria=new CDbCriteria;


$criteria->condition="inventoryItems.inventory_id=:inventoryID";

$criteria->params=array(':inventoryID'=>$filter['inventory_id']);

$criteria->with=array("inventoryItems");


$items=new CActiveDataProvider('ItemMaster',array('criteria'=>$criteria));

If you leave the condition at the level of ItemMaster, it will filter all the records without inventory, even with left outer join.

In your version, you are putting the condition on the ‘on’, with the result you experimented.

3

It worked. Thanks a lot. So many things are clear now. Seems like, all the relationships are being "Properties" in Model?

Thanks,

Ravi Verma

4

But he wants to use "INNER JOIN" and that should also work.

to Ravi: try to also add ‘together’=>true to $inventory_condition. This is a HAS_MAN relation so by default YII AR is not loading it with same query, but in second one and merges results with primary table results.

5

Hey Thanks, It worked also and it will be requiring less changes at my end, since I’m having conditions with other relationships also.

Thanks a lot.

Ravi Verma

6

Yes, the related models will be properties of the model, has_one/belongs_to will be a model, has_many/many_many will be an array of models