Bug in ActiveQuery (select, joinWith, asArray combination fails)

[mklemarczyk]

Hi there,

I found problem with ActiveQuery. I tired query like this:

Order::find()->select(['price', 'firstname', 'lastname'])->joinWith('user')->asArray()->all();

Exception:

exception 'yii\base\ErrorException' with message 'Undefined index: id' in E:\Web\root-users\proj1\vendor\yiisoft\yii2\db\ActiveQuery.php:263
Stack trace:
#0 E:\Web\root-users\proj1\vendor\yiisoft\yii2\db\ActiveQuery.php(263): yii\base\ErrorHandler->handleError(8, 'Undefined index...', 'E:\\Web\\root-use...', 263, Array)
#1 E:\Web\root-users\proj1\vendor\yiisoft\yii2\db\ActiveQuery.php(220): yii\db\ActiveQuery->removeDuplicatedModels(Array)
#2 E:\Web\root-users\proj1\vendor\yiisoft\yii2\db\Query.php(207): yii\db\ActiveQuery->populate(Array)
#3 E:\Web\root-users\proj1\vendor\yiisoft\yii2\db\ActiveQuery.php(130): yii\db\Query->all(NULL)
#4 E:\Web\root-users\proj1\backend\controllers\order\ExportCsvAction.php(38): yii\db\ActiveQuery->all()
#5 [internal function]: backend\controllers\order\ExportCsvAction->run()
#6 E:\Web\root-users\proj1\vendor\yiisoft\yii2\base\Action.php(92): call_user_func_array(Array, Array)
#7 E:\Web\root-users\proj1\vendor\yiisoft\yii2\base\Controller.php(151): yii\base\Action->runWithParams(Array)
#8 E:\Web\root-users\proj1\vendor\yiisoft\yii2\base\Module.php(455): yii\base\Controller->runAction('export-csv', Array)
#9 E:\Web\root-users\proj1\vendor\yiisoft\yii2\web\Application.php(84): yii\base\Module->runAction('order/ex...', Array)
#10 E:\Web\root-users\proj1\vendor\yiisoft\yii2\base\Application.php(375): yii\web\Application->handleRequest(Object(yii\web\Request))
#11 E:\Web\root-users\proj1\backend\web\index.php(18): yii\base\Application->run()
#12 {main}

Version: 2.0.4

[mklemarczyk]

It still does not work after update to 2.0.6.

Exception:

exception 'yii\base\ErrorException' with message 'Undefined index: user_id' in E:\Web\root-users\proj1\vendor\yiisoft\yii2\db\ActiveRelationTrait.php:456
Stack trace:
#0 E:\Web\root-users\proj1\vendor\yiisoft\yii2\db\ActiveRelationTrait.php(456): yii\base\ErrorHandler->handleError(8, 'Undefined index...', 'E:\\Web\\root-use...', 456, Array)
#1 E:\Web\root-users\proj1\vendor\yiisoft\yii2\db\ActiveRelationTrait.php(215): yii\db\ActiveQuery->filterByModels(Array)
#2 E:\Web\root-users\proj1\vendor\yiisoft\yii2\db\ActiveQueryTrait.php(170): yii\db\ActiveQuery->populateRelation('user', '<span class="st...')
#3 E:\Web\root-users\proj1\vendor\yiisoft\yii2\db\ActiveQuery.php(225): yii\db\ActiveQuery->findWith(Array, '<span class="st...')
#4 E:\Web\root-users\proj1\vendor\yiisoft\yii2\db\Query.php(207): yii\db\ActiveQuery->populate(Array)
#5 E:\Web\root-users\proj1\vendor\yiisoft\yii2\db\ActiveQuery.php(132): yii\db\Query->all(NULL)
#6 E:\Web\root-users\proj1\backend\controllers\order\ExportCsvAction.php(38): yii\db\ActiveQuery->all()
#7 [internal function]: backend\controllers\order\ExportCsvAction->run()
#8 E:\Web\root-users\proj1\vendor\yiisoft\yii2\base\Action.php(92): call_user_func_array(Array, Array)
#9 E:\Web\root-users\proj1\vendor\yiisoft\yii2\base\Controller.php(151): yii\base\Action->runWithParams(Array)
#10 E:\Web\root-users\proj1\vendor\yiisoft\yii2\base\Module.php(455): yii\base\Controller->runAction('export-csv', Array)
#11 E:\Web\root-users\proj1\vendor\yiisoft\yii2\web\Application.php(84): yii\base\Module->runAction('order/ex...', Array)
#12 E:\Web\root-users\proj1\vendor\yiisoft\yii2\base\Application.php(375): yii\web\Application->handleRequest(Object(yii\web\Request))
#13 E:\Web\root-users\proj1\backend\web\index.php(18): yii\base\Application->run()
#14 {main}

Moreover if I add field 'user_id' to select array, it returns whole data from Order and User instead of only chosen in select closure.

[mikehaertl]

This pretty clearly indicates an issue in your code. You seem to use user_id somewhere, where it doesn't belong. Probably in the relation definition of user.

[mklemarczyk]

@mikehaertl code is fine, but code form Yii still have some problems.
In every place of application user relation work. But when I added select closure to my query everything stop work as is expected.
I know that it can be a black swan for you and no one test it.

If I remove select or joinWith from this query, than it works fine.

Expectations of this functionality are:

• In output is a two dimensional array (no less no more)
• First dimension is a list of arrays.
• Second dimension is a dictionary with keys as column names (prefixed if belong to foreign model), and values only matching to select closure.

Current result:

array(1) {
  array(4) {
    ["price"]=>
    string(9) "23456.78"
    ["firstname"]=>
    string(6) "Test10"
    ["lastname"]=>
    string(6) "Test"
    ["user_id"]=>
    string(1) "4"
    ["user"]=>
    array(12) {
      ["id"]=>
      string(1) "4"
      ["username"]=>
      string(6) "test10"
      ["auth_key"]=>
      string(32) "nWJ_gUFQ4n09X17iXO22dIKh9i9zUqAd"
      ["password_hash"]=>
      string(60) "$2y$13$7LxKbo/MFIHxIetKTnpQYOhjb1z2LkBWc4rOAkGgKSKUhfY4ZlL.S"
      ["password_reset_token"]=>
      NULL
      ["email"]=>
      string(14) "test10@test.pl"
      ["email_confirm_token"]=>
      NULL
      ["firstname"]=>
      string(6) "Test10"
      ["lastname"]=>
      string(4) "Test"
      ["status"]=>
      string(2) "10"
      ["created_at"]=>
      string(10) "1439591224"
      ["updated_at"]=>
      string(10) "1439591257"
    }
  }
}

[mikehaertl]

@mklemarczyk I think it would help the core developers very much, if you supply a boiled down code example that helps to reproduce your issue.

[alex-code]

If you have relations you need to select the related columns.

[mklemarczyk]

@alex-code it should be done automatically.
In select you specify only data what should be outputted not selected from DB. It should be done by ORM.

[ptz-nerf]

The same thing.
There is a workaround:

$result = Order::find()->select(['price', 'firstname', 'lastname'])->joinWith('user')
   ->createCommand()->queryAll();

[djagya]

Same thing.

I think the desired behavior is to return only the list of attributes we specified in $query->select(), but automatically additionally select needed attributes for relations specified in joinWith(), which won't be in the result until they were specified in $query->select()).

[RdeWilde]

I am also experiencing this as a bug. It should work like MYSQL can have a select a specific column and meanwhile join on different fields. It will only return the fields you selected though.

[RdeWilde]

Another example, calling byAmountOfNights:

class RecreationRentalPeriodQuery extends ActiveQuery {
    /* .. */
    public function byAmountOfNights($objectType, $amountOfNights) {
    /* .. */
            $allPeriods = RecreationRentalPeriod::find()->cache()
                ->select([
                    RecreationRentalPeriod::tableName().'.period_id',
                    RecreationRentalType::tableName().'.rental_id',
                ])
                ->byObjectType($instance)->asArray()->all();
    /* .. */
    }

    /**
     * @param RecreationObjectType $objectType
     * @return static
     */
    public function byObjectType(RecreationObjectType $objectType) {
        return $this->innerJoinWith([
            'rentalType.rentalTypeConnection.objects.objectType' => function($q) use ($objectType) {
                $modelClass = $q->modelClass;
                $q->andWhere([$modelClass::tableName().'.type_id' => $objectType->type_id]);
            }]
        );
    }

Where basically the only thing I want to do is

            $allPeriods = RecreationRentalPeriod::find()->cache()
                ->select('period_id')
                ->byObjectType($instance)->asArray()->all();

Can one explain why it does need the rental id? It also uses other relations, where it does not require to select the key? I would have expected something more like this (which would be obviously not workable):

            $allPeriods = RecreationRentalPeriod::find()->cache()
                ->select([
                    RecreationRentalPeriod::tableName().'.period_id',
                    RecreationRentalType::tableName().'.rental_id',
                    RecreationObject::tableName().'.object_id',
                    RecreationObjectType::tableName().'.type_id'

Edit; does this probably have something to do with the ->inverseOf() function on the relation!?

[dynasource]

@RdeWilde : one issue at a time please. Can you formulate it again in a way that it is easy to grasp for people looking at it for the first time?

[RdeWilde]

@dynasource It is one and the same issue I think?

Please explain what I need to clarify. The code gives me an error atm. Not sure what else to add to the select. It 'worked' when I did not have added the inverseOf.

Edit; I seem to have mixed up two issues. I updated the error below and comment.

Here is a gist: https://gist.github.com/RdeWilde/014673eb08078342a72ab4cc7ec33913
Starting with the call on RecreationRentalPeriodQuery::byAmountOfNights

[dynasource]

@RdeWilde, its undoable to help you debugging. To sum up:

• an example with 'rentalType.rentalTypeConnection.objects.objectType'
• an example about 'byAmountOfNights'
• an example about 'byObjectType'
• a reference to 'inverseOf'

You really have to split your issue in the smallest contained environment for us to understand it properly without wasting too much time. Perhabs its better for you to open your own issue.

[RdeWilde]

@dynasource It is in the gist. These are snippets out of thousands of lines, I am not allowed to post all that. Also, it was basically posted as another example use case for the issue. I don't think my particular code matters that much to the issue.

I think the issue has already been made clear by @mklemarczyk issue and #9495 (comment) + #9495 (comment)