yii CActiveDataProvider Multiple Table Join

Question

I'm working on a magazine site right now and I'm having trouble with getting Yii to return all data with CActiveDataProvider. The SQL query runs fine when run through SQL, and I receive no errors in Yii when it's run. Checking logs, the whole SQL statement is being run when put through Yii.

public function actionIndex()
{
    $this->layout = '//layouts/column1';

    if(isset($_GET['cat']))
    {
        $cat = $_GET['cat'];
        $catname = Yii::app()->db->createCommand();
        $catname->select='category_name';
        $catname->from='category';
        $catname->where('id=:cid', array(':cid'=>$cat));
        $categoryname = $catname->queryScalar();

        $criteria = new CDbCriteria();
        $criteria->alias = 'article';
        $criteria->select ='article.id, article.title, article.content, article.date_published, image.file_name, image.alt_text, author.first_name, author.last_name';
        $criteria->join='INNER JOIN category_article ON article.id = category_article.article_id ';
        $criteria->join.='INNER JOIN image ON image.article_id = article.id ';
        $criteria->join.='INNER JOIN author ON author.id = article.author_id';
        $criteria->condition='category_article.category_id=:cid AND article.completed = 3 AND article.live = 1 AND article.review = 1';
        $criteria->params=array(':cid'=>$cat);
        $dataProvider=new CActiveDataProvider('Article', array('criteria'=>$criteria));

        $this->render('index',array(
            'dataProvider'=>$dataProvider,
            'cat'=>$cat,
            'categoryname'=>$categoryname,
        ));
        Yii::app()->end();
    }

    $dataProvider=new CActiveDataProvider('Category');
    $this->render('index',array(
        'dataProvider'=>$dataProvider,
    ));
}

In case it's needed, here are the relations for the article model:

public function relations()
{
    // NOTE: you may need to adjust the relation name and the related
    // class name for the relations automatically generated below.
    return array(
        'author' => array(self::BELONGS_TO, 'Author', 'author_id'),
        'articleCounts' => array(self::HAS_MANY, 'ArticleCount', 'article_id'),
        'articleNote' => array(self::HAS_ONE, 'ArticleNote', 'article_id'),
        'categories' => array(self::MANY_MANY, 'Category', 'category_article(article_id, category_id)'),
        'comments' => array(self::HAS_MANY, 'Comment', 'article_id'),
        'featureds' => array(self::HAS_MANY, 'Featured', 'article_id'),
        'image' => array(self::HAS_ONE, 'Image', 'article_id'),
        'nonmembercomments' => array(self::HAS_MANY, 'Nonmembercomment', 'article_id'),
        'tags' => array(self::MANY_MANY, 'Tag', 'postid_tagid(article_id, tag_id)'),
        'videos' => array(self::HAS_ONE, 'Video', 'article_id'),
    );
}

and here is the print_r printout:

Article Object
(
    [_new:CActiveRecord:private] => 
    [_attributes:CActiveRecord:private] => Array
        (
            [id] => 56
            [title] => Hello My Lovely
            [content] => Lorem ipsum dolor sit amet, consectetur adipiscing elit. Morbi a arcu dictum, tincidunt libero vel, imperdiet ante. Proin consectetur risus vel purus vestibulum, nec scelerisque felis posuere. Maecenas ut erat lobortis, rhoncus tellus at, ultricies turpis. Donec pretium aliquet mi. Integer luctus interdum magna, quis dictum ligula bibendum sed. Curabitur nibh felis, sollicitudin hendrerit nunc eget, interdum fringilla leo. Cras consectetur elit metus, at tempus erat vehicula quis.



        [date_published] => 2014-07-15 23:16:07
    )

[_related:CActiveRecord:private] => Array
    (
        [image] => Image Object
            (
                [_new:CActiveRecord:private] => 
                [_attributes:CActiveRecord:private] => Array
                    (
                        [id] => 36
                        [article_id] => 56
                        [file_name] => hello-you20140624190852.jpg
                        [image_name] => Hello You
                        [alt_text] => My Hottie!
                    )

                [_related:CActiveRecord:private] => Array
                    (
                    )

                [_c:CActiveRecord:private] => 
                [_pk:CActiveRecord:private] => 36
                [_alias:CActiveRecord:private] => t
                [_errors:CModel:private] => Array
                    (
                    )

                [_validators:CModel:private] => 
                [_scenario:CModel:private] => update
                [_e:CComponent:private] => 
                [_m:CComponent:private] => 
            )

        [author] => 
    )

[_c:CActiveRecord:private] => 
[_pk:CActiveRecord:private] => 56
[_alias:CActiveRecord:private] => t
[_errors:CModel:private] => Array
    (
    )

[_validators:CModel:private] => 
[_scenario:CModel:private] => update
[_e:CComponent:private] => 
[_m:CComponent:private] => 
)

As you can see, everything from the query is returned and yet the author remains blank and I'm not sure why. I did notice the image object did the same thing until I changed the relation in the article model to HAS_ONE, which is fine since each article will only have one image associated with it. Anyone see anything wrong in the way it's written that would be causing that issue? Thanks to all answers in advance.

Answer 1

I think 'author' => array(self::BELONGS_TO, 'Author', 'author_id'), needs to be 'author' => array(self::BELONGS_TO, 'Author', array('author_id' => 'author_id'),

From the docs

In case you need to specify custom PK->FK association you can define it as array('fk'=>'pk').

Yii uses the PK of the model as the foreign key unless otherwise told. It looks like images does indeed use the article's id (article.id) where the author does not (article.author_id) .