ActiveQuery crashes when it has select and eager loading

Yii 2.0 General Discussions
1

I have 2 tables: Estimate and Activity.

  • An estimate can have many activity

  • An activity belongs to 1 estimate

I am trying to get all activity to populate a dropdown which has ‘activity name - estimate name’ using the following:




$activities = Activity::getAll();




# inside Activity.php


# this works

public static function getAll() {

    return self::find()

	    ->select(['{{activity}}.id, CONCAT( {{activity}}.name, \' - \', {{estimate}}.name ) as description']) 

	    ->join('INNER JOIN', 'estimate', 'estimate.id = activity.estimate_id')

	    ->asArray()->all();

}


# this give PHP Notice 'yii\base\ErrorException' with message 'Undefined index: estimate_id' <img src='http://www.yiiframework.com/forum/public/style_emoticons/default/huh.gif' class='bbc_emoticon' alt='???' /> WHY ??

public static function getAll() {

    return self::find()

	    ->select(['{{activity}}.id, CONCAT( {{activity}}.name, \' - \', {{estimate}}.name ) as description']) 

	    ->joinWith('estimate', true, 'INNER JOIN')

	    ->asArray()->all();

}


# this is OK if i dont eager loading

public static function getAll() {

    return self::find()

	    ->select(['{{activity}}.id, CONCAT( {{activity}}.name, \' - \', {{estimate}}.name ) as description']) 

	    ->joinWith('estimate', false, 'INNER JOIN')

	    ->asArray()->all();

}

Please help me to understand why Eager Loading breaks ?

2

Probably you incorrectly configure relation ‘estimate’ in model

3

relation works because this works




public static function getAll() {

    return self::find()

            ->select(['{{activity}}.id, CONCAT( {{activity}}.name, \' - \', {{estimate}}.name ) as description']) 

            ->joinWith('estimate', false, 'INNER JOIN')

            ->asArray()->all();

}
4

This is because Yii will try to fetch the relation with another separated SQL.

  1. using join 



# this works

public static function getAll() {

    return self::find()

	    ->select(['{{activity}}.id, CONCAT( {{activity}}.name, \' - \', {{estimate}}.name ) as description']) 

	    ->join('INNER JOIN', 'estimate', 'estimate.id = activity.estimate_id')

	    ->asArray()->all();

}

This will execute a SQL something like:

[sql]

select

activity.id,


concat( activity.name, '-', estimate.name) as description

from

activity


inner join estimate


    on estimate.id = activity.estimate_id;

[/sql]

And it works as expected.

  1. using joinWith (eager) 



# this give PHP Notice 'yii\base\ErrorException' with message 'Undefined index: estimate_id' <img src='http://www.yiiframework.com/forum/public/style_emoticons/default/huh.gif' class='bbc_emoticon' alt='???' /> WHY ??

public static function getAll() {

    return self::find()

	    ->select(['{{activity}}.id, CONCAT( {{activity}}.name, \' - \', {{estimate}}.name ) as description']) 

	    ->joinWith('estimate', true, 'INNER JOIN')

	    ->asArray()->all();

}

This will try to execute 2 SQLs:

[sql]

select

activity.id,


concat( activity.name, '-', estimate.name) as description

from

activity


inner join estimate


    on estimate.id = activity.estimate_id;

select

*

from

activity


where id in (1,2,3, ... x);

[/sql]

The 2nd SQL is for fetching the related model, where (1,2,3, … x) refer to the foreign keys fetched in

the 1st query. It will end in an error since we didn’t get ‘estimate_id’ in the 1st query.

Try this for testing:




public static function getAll() {

    return self::find()

	    ->select(['{{activity}}.id, {{activity}}.estimate_id, CONCAT( {{activity}}.name, \' - \', {{estimate}}.name ) as description']) 

	    ->joinWith('estimate', true, 'INNER JOIN')

	    ->asArray()->all();

}
  1. using joinWith (lazy) 



# this is OK if i dont eager loading

public static function getAll() {

    return self::find()

	    ->select(['{{activity}}.id, {{activity}}.estimate_id, CONCAT( {{activity}}.name, \' - \', {{estimate}}.name ) as description']) 

	    ->joinWith('estimate', false, 'INNER JOIN')

	    ->asArray()->all();

}

This will work, because Yii doesn’t try to execute the 2nd SQL.

But it will fail if you try to access the related model. Try the following for a test:




public static function getAll() {

    $models = self::find()

	    ->select(['{{activity}}.id, CONCAT( {{activity}}.name, \' - \', {{estimate}}.name ) as description']) 

	    ->joinWith('estimate', false, 'INNER JOIN')

	    ->all();

    foreach($models as $model) {

        echo $model->estimate->name;

    }

}

In eager loading, Yii will always try to execute 2 or more queries — the 1st one for the main model, and the separated queries for the related models.