yiicactivedataprovider

Yii CactiveDataProvider multiple table join?

I'm trying to join 3 tables in a CActiveDataProvider to help me order things fully and better. But I'm struggling to get the arrangement down properly. I've put comments to try and help outline what i'm after. I can do it in SQL fine, but i'd like to do it in Active Record ideally.

Item Table: FK = Area table ID

Area Table: Takes $id from function to select the area

Featured Table: FK = Item_id

$dataProvider=new CActiveDataProvider('Item', array(
        'criteria' => array(
        'with' =>'sector', // and need 'featureditem'
        'condition' =>'t.sector_id=:id', // t.id = featureditem.item_id
        'params' => array(':id'=>$id), // is the area to search in
        'order' => 'featureditem.item_id DESC', 
    )
));
Solution

  • It seems what you want can be achieved through a INNER JOIN:

    $dataProvider=new CActiveDataProvider('Item', array(
    'criteria' => array(
        'with' =>'sector',
        'condition' =>'t.sector_id=:id',
        'params' => array(':id'=>$id),
        'order' => 'fi.item_id DESC',
        'join' => 'INNER JOIN featureitem fi ON fi.item_id=t.id',
    )
));