Yii2 Select only few columns from related model

Question

In controller i have:

public function actionGetItems()
{
    $model = new \app\models\WarehouseItems;
    $items = $model->find()->with(['user'])->asArray()->all();
    return $items;
}

In WarehouseItem model i have standard (created by gii) relation declaration:

public function getUser()
{
    return $this->hasOne('\dektrium\user\models\User', ['user_id' => 'user_id']);
}

How can i control which column data do i get from "user" relation? I currently get all columns which is not good as that data is being sent to Angular in JSON format. Right now i have to loop trough $items and filer out all columns i dont want to send.

soju · Accepted Answer · 2017-08-22T14:09:08.973

18

You should simply modify the relation query like this :

$items = \app\models\WarehouseItems::find()->with([
    'user' => function ($query) {
        $query->select('id, col1, col2');
    }
])->asArray()->all();

Read more : http://www.yiiframework.com/doc-2.0/yii-db-activequerytrait.html#with()-detail

edited Aug 22 '17 at 14:09

answered Oct 13 '15 at 16:40

soju

24,068
3
63
66

1

I get this error: PHP Notice – yii\base\ErrorException Undefined index: user_id What could it be? – Ljudotina Oct 14 '15 at 08:34
1

In order it works you need also select linking field of the related class. In this case it would be `user_id` of `User` model: `$query->select('user_id, col1, col2');`. – Roman Grinyov Jul 07 '17 at 11:48

score 2 · Answer 2 · answered Feb 16 '18 at 09:23

Your code should go this way.

public function actionGetItems()
{
    $items = \app\models\WarehouseItems::find()
        ->joinWith([
             /*
              *You need to use alias and then must select index key from parent table
              *and foreign key from child table else your query will give an error as
              *undefined index **relation_key**
              */
            'user as u' => function($query){
                $query->select(['u.user_id', 'u.col1', 'u.col2']);
            }
        ])
        ->asArray()
        ->all();

    return $items;
}

score 0 · Answer 3 · answered Nov 23 '19 at 21:19

0

Inside WarehouseItem model

/**
 * @return ActiveQuery
 */
public function getUser()
{
    $query = User::find()
        ->select(['id', 'col1', 'col2'])
        ->where([
            'id' => $this->user_id,
        ]);
    /** 
     * Default hasOne, setup multiple for hasMany
     * $query->multiple = true;
     */
    return $query;
}

answered Nov 23 '19 at 21:19

mat.twg

88
1
9

I read it, did you? You have eager loading example in question. – rob006 Nov 26 '19 at 00:27
first off all read agan [link](https://www.yiiframework.com/wiki/834/relational-query-lazy-loading-and-eager-loading-in-yii-2-0#eager-loading) And use `joinWith` if want eager --- _'joinWith' doesn't require the table name and the condition for 'ON'. You just have to specify the relation name, and it will result in eager loading by default._ – mat.twg Nov 27 '19 at 11:45
You've found link to documentation. Now try to read it - at the begging of it you have an example how definition of relation should look like. The crucial part of it is that it relies on column mapping (`['user_id' => 'user_id']`) while your solution uses value (`['user_id' => $this->user_id]`), which will not work, because `$this->user_id` is not yet initialized when query is build. So your relation will not work neither for joins nor eager loading. And `with()` (used by OP) enables eager loading. – rob006 Dec 08 '19 at 10:25

Yii2 Select only few columns from related model

3 Answers3