viaTable sum: error Undefined index

Yii 2.0 General Discussions
1

I do have three tables in which the middle table acts as filter.

tbl_dagboek (1)

key: dagboek_id


attr: spelvorm_id


attr: gebeurtenisinspelvorm_id

tbl_gebeurtenisinspelvorm (2)

key: gebeurtenisinspelvorm_id


attr: spelvorm_id


attr: gebeurtenis_id

tbl_gebeurtenis (3)

key: gebeurtenis_id


attr: motiefbestaan


attr: motiefsamenhang

tbl_dagboek (1) has multiple records with the same gebeurtenisinspelvorm_id.

tbl_gebeurtenisinspelvorm (2) has multiple records with the same gebeurtenis_id.

Every record in tbl7dagboek (1) has just one gebeurtenis in tbl(gebeurtenis (3).

The logic is that the tbl_dagboek (1) has more spelvorms and every spelvorms has it own set of gebeurtenis.

I am trying the calculate the sum of fields in tbl_gebeurtenis (3).

The SQL statement generated by Yii2 is:

SELECT sum(motiefbestaan) as motiefbestaan, sum(motiefsamenhang) as motiefsamenhang,

FROM tbl_dagboek

LEFT JOIN tbl_gebeurtenisinspelvorm ON tbl_dagboek.gebeurtenisinspelvorm_id = tbl_gebeurtenisinspelvorm.gebeurtenisinspelvorm_id

LEFT JOIN tbl_gebeurtenis ON tbl_gebeurtenisinspelvorm.gebeurtenis_id = tbl_gebeurtenis.gebeurtenis_id

WHERE spelronde_id=‘1’

ORDER BY tijdstip DESC

This is a correct sql statement which works correct in phpmyadmin

However, I Yii2 I get the error: Undefined index: dagboek_id in all().

yii\db\ActiveQuery::all()

yii\db\ActiveQuery::populate([[‘motiefbestaan’ => ‘68’, ‘motiefsamenhang’ => ‘74’, …

yii\db\ActiveQuery::removeDuplicatedModels([[‘motiefbestaan’ => ‘68’, ‘motiefsamenhang’ => ‘74’, …]]) at line 220

yii\base\ErrorHandler::handleError(8, ‘Undefined index: dagboek_id’,

The query in Yii2 is:

$searchMotievenModel = new DagboekSearch();


$motieven = $searchMotievenModel->search(Yii::$app->request->getQueryParams());


$motieven 


	->query


	->select(['sum(motiefbestaan) as motiefbestaan', 'sum(motiefsamenhang) as motiefsamenhang'])


	->joinWith('gebeurtenis')


	->where(['spelronde_id' => $spelronde_id])


	->orderBy('tijdstip desc')


	->asArray()


	->all();

The declaration of the relation in model Dagboek is:

public function getGebeurtenis()

{


	return $this->hasOne(Gebeurtenis::className(), ['gebeurtenis_id' => 'gebeurtenis_id'])


            ->viaTable(Gebeurtenisinspelvorm::tableName(), ['gebeurtenisinspelvorm_id' => 'gebeurtenisinspelvorm_id']);


}

HasOne or HasMany give the same error.

Of course, I can use SQL, but I want to use Yii2. Yii2 is fantastic simple to use once you know how to use it. But here my knowlegde falls short.

Where goes my thinking astray?