What is the correct way to define relationships among multiple tables?

Yii 2.0 General Discussions
1

The question is also published on StackOverflow: stackoverflow.com/questions/33387126

In a controller I have the following code:


public function actionView($id)

{

	$query = new Query;

	$query->select('*')

		->from('table_1 t1')

		->innerJoin('table_2 t2', 't2.t1_id = t1.id')

		->innerJoin('table_3 t3', 't2.t3_id = t3.id')

		->innerJoin('table_4 t4', 't3.t4_id = t4.id')

		->andWhere('t1.id = ' . $id);

	$rows = $query->all();

	return $this->render('view', [

		'model' => $this->findModel($id),

		'rows' => $rows,

		]);

}

See the db schema: github.com/AntoninSlejska/yii-test/blob/master/example/sql/example-schema.png

In the view view.php are displayed data from tables_2-4, which are related to table_1:


foreach($rows as $row) {

	echo $row['t2_field_1'];

	echo $row['t2_field_2'];

	...

}

See: stackoverflow.com/questions/32480792

and: www.yiiframework.com/doc-2.0/yii-db-query.html

It works, but I’m not sure, if it is the most correct Yii2’s way.

I tried to define the relations in the model TableOne:


public function getTableTwoRecords()

{

	return $this->hasMany(TableTwo::className(), ['t1_id' => 'id']);

}

public function getTableThreeRecords()

{

	return $this->hasMany(TableThree::className(), ['id' => 't3_id'])

	->via('tableTwoRecords');

}

public function getTableFourRecords()

{

	return $this->hasMany(TableFour::className(), ['id' => 't4_id'])

	->via('tableThreeRecords');

}

and then to join the records in the controller TableOneController:


$records = TableOne::find()

	->innerJoinWith(['tableTwoRecords'])

	->innerJoinWith(['tableThreeRecords'])

	->innerJoinWith(['tableFourRecords'])

	->all();

but it doesn’t work. If I join only the first three tables, then it works. If I add the fourth table, then I receive the following error message: “Getting unknown property: frontend\models\TableOne::t3_id”

If I change the function getTableFourRecords() in this way:


public function getTableFourRecords()

{

	return $this->hasOne(TableThree::className(), ['t4_id' => 'id']);

}

then I receive this error message: "SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘table_4.t4_id’ in ‘on clause’

The SQL being executed was: SELECT table_1.* FROM table_1 INNER JOIN table_2 ON table_1.id = table_2.t1_id INNER JOIN table_3 ON table_2.t3_id = table_3.id INNER JOIN table_4 ON table_1.id = table_4.t4_id"

2

Why don’t you use complete table name for ‘on’ conditions? So it would be easily portable.

3

How can I do it? I tried e.g. to change the function in the model TableOne:


    public function getTableFourRecords()

    {

        return $this->hasMany(TableFour::className(), ['id' => 'table_3.t4_id']);

    }

But then I receive the following error message:

SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘table_1.table_3.t4_id’ in ‘on clause’

The SQL being executed was: SELECT table_1.* FROM table_1 INNER JOIN table_2 ON table_1.id = table_2.t1_id INNER JOIN table_3 ON table_2.t3_id = table_3.id INNER JOIN table_4 ON table_1.table_3.t4_id = table_4.id

4

I guess all the relations listed below have already been created by Gii’s model generator.

Model TableOne:




public function getTableTwos()

{

    return $this->hasMany(TableTwo::className(), ['t1_id' => 'id']);

}

Model TableTwo:




public function getTableThree()

{

    return $this->hasOne(TableThree::className(), ['id' => 't3_id']);

}

Model TableThree:




public function getTableFour()

{

    return $this->hasOne(TableFour::className(), ['id' => 't4_id']);

}

Then, using these relations, I would probably write something like the following in the view:




    foreach($model->tableTwos as $tableTwo) {

	echo $tableTwo->field_1;

	echo $tableTwo->field_2;

        echo $tableTwo->tableThree->field_a;

        echo $tableTwo->tableThree->field_b;

        echo $tableTwo->tableThree->TableFour->field_x;

        echo $tableTwo->tableThree->TableFour->field_y;

    }

In order to render the view, we just need the following in the controller:




public function actionView($id)

{

    $model = TableOne::find()

        ->where('id' => $id)

        ->one();

    return $this->render('view', ['model' => $model]);

}

Of course, you can eagerly load the related models like this:




public function actionView($id)

{

    $model = TableOne::find()

        ->with(['tableTwos', 'tableTwos.tableThree', 'tableTwos.tableThree.tableFour'])

        ->where('id' => $id)

        ->one();

    return $this->render('view', ['model' => $model]);

}

Please check "with()" in the API reference:

http://www.yiiframework.com/doc-2.0/yii-db-activequerytrait.html

5

Thank you very much! It works quite nice:

Model TableOne:


public function getTableTwoRecords()

{

    return $this->hasMany(TableTwo::className(), ['t1_id' => 'id']);

}

Model TableTwo:


public function getTableThreeRecord()

{

    return $this->hasOne(TableThree::className(), ['id' => 't3_id']);

}

Model TableThree:


public function getTableFourRecord()

{

    return $this->hasOne(TableFour::className(), ['id' => 't4_id']);

}

Controller TableOneController:


public function actionView($id)

{

    $model = TableOne::find($id)->one();

    return $this->render('view', [

         'model' => $model,

    ]);

}

The view table-one/view.php:


foreach ($model->tableTwoRecords as $record) {

    echo 'Table 2 >> ';

    echo 'ID: <b>' . $record->id;

    echo '</b>, T1 ID: <b>' . $record->t1_id;

    echo '</b>, T3 ID: <b>' . $record->t3_id;

    echo '</b>;<br>';

    echo 'Table 3 >> ';

    echo 'ID: <b>' . $record->tableThreeRecord->id;

    echo '</b>, T4 ID: <b>' . $record->tableThreeRecord->t4_id;

    echo '</b>;<br>';

    echo 'Table 4 >> ';

    echo 'ID: <b>' . $record->tableThreeRecord->tableFourRecord->id;

    echo '</b>;<br>';

}
6

Why don’t use ListView widget do display data?




<?php

use yii\widgets\ListView;

...

echo ListView::widget( [

    'dataProvider' => $dataProvider,

    'itemView' => '_item',

] ); ?>

or GridView with activeDataProvider?


 <?= GridView::widget([

        'dataProvider' => new ActiveDataProvider(['query' => $model->PassDataAsArray() ]),

        'layout'=>"{items}\n{pager}",

        'rowOptions' => ['style' => 'text-align: center;'],

        'headerRowOptions' => ['style' => 'text-align: center;'],

        'showHeader' => true,

        'export' => FALSE,

        'bootstrap' => true,

        'condensed' => true,

        'responsive' => true,

        'hover' => true,

        'emptyText' => '<span style="text-align:center">No Constructors found</span>', 

        'emptyTextOptions' => ['style' => 'text-align:center;'],

        'columns' => [

            [

                'header' => '',

                'class' => 'yii\grid\SerialColumn',

                'options' => ['style' => 'width: 20px; text-align: center;'],

                'contentOptions' => ['style' => 'text-align: center; font-weight: bold'],

                

                ],

            [

                'value' => 'user.username',

                'label' => 'Constructor',

                //'contentOptions' => ['style' => 'width:50px'],

                'headerOptions' => ['style' => 'text-align: center']

                

            ],

}); ?>
7

I tried it:

controllers/TableOneController:


public function actionView($id)

{

	$query = TableOne::find();

	

	$dataProvider = new ActiveDataProvider([

		'query' => $query,

		'pagination' => [

			'pageSize' => 10,

		],

	]);


	return $this->render('view', [

		 'model' => $this->findModel($id),

		 'dataProvider' => $dataProvider,

	]);

}

views/table-one/view.php:


use yii\widgets\ListView;

...

echo ListView::widget( [

    'dataProvider' => $dataProvider,

    'itemView' => '_view',

]);

views/table-one/_view.php:


use yii\helpers\Html;

echo $model->tableTwoRecords->id;

...

But I receive the following error message:

[b]Trying to get property of non-object

[/b]

The problem is with:


echo $model->tableTwoRecords->id;
8 

$query = TableOne::find();

change to


$query = TableOne::find($id);

also, can you explaing what do you want to archive doing:




'model' => $this->findModel($id),

and then:


echo $model->tableTwoRecords->id;
9

I changed


$query = TableOne::find();

to


$query = TableOne::find($id);

But I still receive the same error message: Trying to get property of non-object

The:


'model' => $this->findModel($id),

is standard output from the Gii. If I create a CRUD for a model with Gii, then in the controller is allways:


public function actionView($id)

{

	return $this->render('view', [

		'model' => $this->findModel($id),

	]);

}
10

find() return an ActiveQuery object

findOne() return an ActiveRecord object

So if you need for an ActiveRecord object, you have to use:




$object = TableOne::findOne($id);
11

true, the easiest way to see the difference would be to var_dump each object in the view and see what your are getting

12

I tried in views/table-one/view.php


use yii\grid\GridView;

...

echo GridView::widget([

    'dataProvider' => $dataProvider,

    'columns' => [

         $model->tableTwoRecords->id,

    ],

]);

But I receive the same error message as by ListView: Trying to get property of non-object

The problem is on the line:


$model->tableTwoRecords->id,
13

can you var_dump your $model in view, and show what you get?

14

This is not the right way to popupate GridView.

See the documentation:

http://www.yiiframework.com/doc-2.0/yii-grid-gridview.html

15

fsnxdi and Fabrizio

What do you want him to do with ListView or GridView? He is not working with actionIndex but actionView of tableOneController. Please be careful not to confuse a beginner.

Antonín

There’s no need to use ListView or GridView to display a single model. It makes no sense.

But, you may consider using it for displaying related tableTwo models of the tableOne model.

Controller:




public function actionView($id)

{

    $model = $this->loadModel($id);

    $query = TableTwo::find(['t1_id' => $id]);

    $dataProvider = new ActiveDataProvider([

        'query' => $query,

        'pagination' => [

            'pageSize' => 10,

        ],

    ]);

    return $this->render('view', [

        'model' => $model,

        'dataProvider' => $dataProvider,

    ]);

}

view.php:




    // for TableOne

    echo $model->something;

    // for TableTwo

    echo ListView::widget( [

        'dataProvider' => $dataProvider,

        'itemView' => '_view_t2',

    ]);

_view_t2.php:




    echo $model->field_1;

    echo $model->field_2;

    echo $model->tableThree->field_a;

    echo $model->tableThree->field_b;

    echo $model->tableThree->TableFour->field_x;

    echo $model->tableThree->TableFour->field_y;

In view.php, $model is an istance of TableOne. But in _view_t2.php, $model is an instance of TableTwo.

ListView uses $dataProvider to get an array of TableTwo objects whose ‘t1_id’ equals the id of the main model, and loop through it calling _view_t2 for each element in the array.

16

Thanks. Now the GridView works (I will try also the ListView).

I added these functions to the model TableTwo:


public function getTableOneRecord()

{

	return $this->hasOne(TableOne::className(), ['id' => 't1_id']);

}

public function getTableThreeRecord()

{

	return $this->hasOne(TableThree::className(), ['id' => 't3_id']);

}

public function getTableFourRecord()

{

	return $this->hasOne(TableFour::className(), ['id' => 't4_id'])

		->via('tableThreeRecord');

}

I generated CRUD with Gii for the model TableTwo and edited the controller TableOneController:


use app\models\TableTwo;

use app\models\TableTwoSearch;

...

public function actionView($id)

{

	$searchModel = new TableTwoSearch([

		't1_id' => $id, // the data have to be filtered by the id of the displayed record

	]);

	$dataProvider = $searchModel->search(Yii::$app->request->queryParams);


	return $this->render('view', [

		 'model' => $this->findModel($id),

		 'searchModel' => $searchModel,

		 'dataProvider' => $dataProvider,

	]);

}

and also the views/table-one/view.php:


echo GridView::widget([

    'dataProvider' => $dataProvider,

    'columns' => [

         'id',

         't1_id',

         'tableOneRecord.id',

         't3_id',

         'tableThreeRecord.id',

         'tableThreeRecord.t4_id',

         'tableFourRecord.id',

    ],

]);

You can see the result on c9:

yii-test-antoninslejska.c9.io/example/web/table-one/view?id=1

17

I’ve answered to a request about findOne() using.

I think that I have not confused a beginner, I have only specified differences about ActiveQuery and ActiveRecord.

18

Antonín

I’m sorry that I called you a beginner. I was wrong. You are not.

fsnxdi and Fabrizio

I’m very sorry for my offending words. I’ve never doubted your good will to offer help.

But it looked to me that there’s a significant mismatch between Antonín’s intention and your answers.

19

The GridView approach looks promising. I added to the views/table-one/view.php the ActionColumn:


echo GridView::widget([

    'dataProvider' => $dataProvider,

    'columns' => [

        ...

        [

            'class' => 'yii\grid\ActionColumn',

            'template' => '{update} {delete}',

            'buttons' => [

                'update' => function ($url, $model, $key) {

                    return Html::a('<span class="glyphicon glyphicon-pencil"></span>',

                        Url::to(['table-two/update', 'id' => $key ]),

                        [

                            'title' => 'Update',

                            'aria-label' => 'Update',

                        ]

                    );

               },

                'delete' => function ($url, $model, $key) {

                    return Html::a('<span class="glyphicon glyphicon-trash"></span>',

                        Url::to(['table-two/delete', 'id' => $key ]),

                        [

                            'title' => 'Delete',

                            'aria-label' => 'Delete',

                            'data-confirm' => 'Are you sure you want to delete this item?',

                            'data-method' => 'post',

                            'data-pjax' => '0',

                        ]

                    );

                },

            ],

        ],

    ],

]);

Now I can update and delete the records from the junction table (table_2). I thought I will have to use AJAX for this…

See: yii-test-antoninslejska.c9.io/example/web/table-one/view?id=1

By the way, I’m beginner (not only in Yii) and I’m proud of it :slight_smile: // I like Zen Buddhism :slight_smile:

20 

'class' => 'yii\grid\ActionColumn',

If you ini your action column with default settings, without: template and so on, you will get auto edit/delete/view actions, that will be linked to the current controller, it may be helpful if u don’t need to customize them.

Here you can also see, how far you can go with Grid View customization:

http://demos.krajee.com/grid-demo