yii framework inner join

Question

I have a class Articles with a 1:N relation with table Dates. I need to show a list of articles for each date. I use a findBySql with a Inner join.

'SELECT *
            FROM articles_art as art 
            INNER JOIN dates_dat as dat 
            ON art.id_art = dat.idart_dat 
            WHERE art.validated_art = 1 
                AND dat.date_dat <= "' . $todayDate .
            '" ORDER BY dat.date_dat, art.rank_art');

What I don't understand is why when I try to access articles[$key]->dat, dat is an array of dates and not the object date? Thanks

Answer 1

Yii has a transparent way (without writing sql) to do it. You can use scopes and relations methods on model to get data.

The advantage to this way is that unlike findBySql that you need to define a sql each time that you want find something, you can use more than one time the scopes and relations.

So assuming that you have a model called Article and another called Date, you models will shows like:

  class Articles extends CActiveRecord {
    …

    public function relations() {
       return array('date' => array(self::BELONGS_TO, 'Date', 'date_id'));
    }

    public function scopes() {
       return array('validated' => array('condition' => 'validated_art=1'));
    }

    …
  }

  class Date extends CActiveRecord {
    …

    public function relations() {
      return array('articles' => array(self::HAS_MANY, 'Article', 'date_id'));
    }

    public function scopes() {
      return array('byDate' => array('order' => 'date_dat'),
                   'validated' => array('condition' => 'date_dat < 2011-xx-xx'));
    }
    …
  }

And your find will show likes:

  $model = Date::model()->validated()->byDate()->findAll();
  foreach($model->articles as $k => $article) {
    echo $article->title;
  }

The code above is just an example to illustrate the correct way to do it on Yii, I hope that help you.