Yii CactiveDataProvider multiple table join?

Question

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

We need more detail. What is the required result? What is the actual result? Is there an error message? If so what is it? — topher, Jun 27 '13 at 07:33

score 2 · Accepted Answer · answered Jun 27 '13 at 11:09

2

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

answered Jun 27 '13 at 11:09

Michael Härtl

8,428
5
35
62

Thank you so much! I had totally misunderstood the CActiveDataProvider when working with more than 2 models. – Jonnny Jun 27 '13 at 13:42

Yii CactiveDataProvider multiple table join?

1 Answers1