select certain fields in joined table

Yii 2.0 General Discussions
1

When selecting rows using activerecord, sometimes I need to join secondary tables to the primary table rows. However, I don’t necessarily need all fields from these secondary tables.

I tried selecting only the fields I want:




Customer::find()->select(['customer.id','customer.firstname','customer.surname','organization.name'])->where(['active' => true])->joinWith(['role_history' => function (\yii\db\ActiveQuery $query) { $query->andWhere('date_end IS NULL');}, 'role_history.organization'])->asArray()->all()

This selects id, firstname and surname from the customer table, however, the resulting array still contains the full fields of the role_history and organization tables. Is there a way to omit those unwanted fields?

2

Try to specify secondary table columns in inner query:




Customer::find()->select(['customer.id','customer.firstname','customer.surname','organization.name'])

->where(['active' => true])

->joinWith(['role_history' => function (\yii\db\ActiveQuery $query) { 

     // HERE - Fields to select

     $query->select('...');

     $query->andWhere('date_end IS NULL');

}, 

'role_history.organization'])

->asArray()->all()
3

That works, thanks for the help.

It’s important to add the id of the secondary tables as well, otherwise I get an error.

4

Maybe you could set the 2nd parameter of "joinWith" to false.




Customer::find()

    ->select([

        'customer.id',

        'customer.firstname',

        'customer.surname',

        'organization.name'

    ])

    ->where(['active' => true])

    ->joinWith([

        'role_history' => function (\yii\db\ActiveQuery $query) {

            $query->andWhere('date_end IS NULL');

         }, 

        'role_history.organization'

    ], false)

    ->asArray()->all();

"joinWith()" is a kind of a shortcut to "join()". It enables you to simplify the code to join a table by using the name of a relation.

But "joinWith()" is also a derivative of "with()". And just like "with()", it tries to eagerly load the related models by default.

Check in the debugger and see that your code executes 3 queries. One for the customer and the others for the related role_history and organization.

In your case, you could suppress the eager loading of the related models by setting $eagerLoading of "joinWith()" to false.