ActiveRecord and relations: select only specific fields

Hi all,

I try to convert my Yii 1 code to Yii 2 and encountered a problem. Maybe you can help:

I have two ActiveRecord models: "News" and "NewsContent" with "News hasMany NewsContent". The relation works well. But now I want to create an ActiveDataProvider for News which should eager load NewsContent but only select a few fields of NewsContent to reduce the amount of data transferred. In Yii 1 I did the following:


$provider =  new CActiveDataProvider('News', array(

    'criteria'=>array(

        'select' => 'created',

        'condition' => 'published=1',

        'order' => 'created DESC',

        'with' => array('contents'=>array(

            'select' => array(

                'title',

                'intro',

            ),

            'on' => 'contents.language=:lang',

            'params' => array(':lang' => Yii::app()->language)

        )),

    ),

    'pagination'=>array(

        'pageSize'=>20

    ),

));

Now I tried the following in Yii 2:


$query = News::find()

    ->select(['news.id', 'news.created'])

    ->where(['published' => 1])

    ->orderBy(['created' => SORT_DESC])

    ->joinWith([

        'newsContents' => function ($query) {

                // TODO $query->select(['news_content.id', 'news_content.title', 'news_content.intro']);

                $query->onCondition(['news_content.language' => \Yii::$app->language]);

            }

    ]);


$provider = new ActiveDataProvider([

    'query' => $query,

    'pagination' => [

        'pageSize' => 20,

    ],

]);

This works so far: News and NewsContent are loaded using two SQL-queries (one SELECT for the News and one SELECT for the NewsContents).

However if I include the line marked with the "TODO" to limit the fields which should be selected, then no NewsContents are loaded. But the Yii Debugger Bar states that the correct queries were executed: two SELECT statements (one for News and one for NewsContent with only id, title and intro selected).

Am I doing something wrong or is there a bug with populating the models?

Could you paste here the SELECT queries from debugger?

Sure, these are the SELECT queries when I don’t select specific fields of NewsContent:


SELECT COUNT(*) FROM `news`

LEFT JOIN `news_content` ON (`news`.`id` = `news_content`.`news_id`) AND (`news_content`.`language`='en-US')

WHERE `published`=1


SELECT `news`.`id`, `news`.`created` FROM `news`

LEFT JOIN `news_content` ON (`news`.`id` = `news_content`.`news_id`) AND (`news_content`.`language`='en-US')

WHERE `published`=1 ORDER BY `created` DESC LIMIT 20


SELECT * FROM `news_content` WHERE (`news_id` IN (40, 39, ...)) AND (`news_content`.`language`='en-US')

And when I include the line marked with "TODO" to select specific columns then the first two queries are the same and the third looks like this:


SELECT `news_content`.`id`, `news_content`.`title`, `news_content`.`intro` FROM `news_content`

WHERE (`news_id` IN (40, 39, ...)) AND (`news_content`.`language`='en-US')

This last query returns the correct NewsContents when I run it on the DB. However when using the ActiveDataProvider the returned result does only contain the News-models, not the related NewsContent-models.

How do you use the ActiveDataProvider data? If you are populating Active Record with db data you should get all columns not just the selected ones. Please try again without select() methods.

…And maybe just to be sure check first if adding ‘news_content.language’ to the second select() fixes the problem.

I simply use


$provider->getModels()

Without select() methods it returns the expected News-models including the NewsContent-models. However I want to avoid loading the text-column of all NewsContent-models.

Nope, unfortunately that didn’t solve the problem.

EDIT:

OK, I solved it. I had to include the backreference in the second select(). That is "news_id":


$query->select(['news_content.id', 'news_content.news_id', 'news_content.title', 'news_content.intro']);

Right, forgot about "Note: If you call select() while eagerly loading relations, you have to make sure the columns referenced in the relation declarations are being selected. Otherwise, the related models may not be loaded properly".