Yii2:在多个表之间定义关系的正确方法是什么? [英] Yii2: What is the correct way to define relationships among multiple tables?
问题描述
在控制器中,我有以下代码:
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,
]);
}
请参阅数据库架构: https://github.com/AntoninSlejska/yii-test/blob/master/example/sql/example-schema.png
在视图view.php中显示来自table_2-4的数据,这些数据与table_1相关:
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'];
...
}
请参阅: Yii2 innerJoin()和: http://www.yiiframework.com/doc-2.0/yii-db-query.html
它可以工作,但是我不确定这是否是最正确的Yii2方法.
It works, but I'm not sure, if it is the most correct Yii2's way.
我试图在模型TableOne中定义关系:
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');
}
,然后在控制器TableOneController中加入记录:
and then to join the records in the controller TableOneController:
$records = TableOne::find()
->innerJoinWith(['tableTwoRecords'])
->innerJoinWith(['tableThreeRecords'])
->innerJoinWith(['tableFourRecords'])
->all();
但是它不起作用.如果我仅加入前三个表,那么它将起作用.如果添加第四张表,则会收到以下错误消息:获取未知属性:frontend \ models \ TableOne :: t3_id"
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"
如果我以这种方式更改函数getTableFourRecords():
If I change the function getTableFourRecords() in this way:
public function getTableFourRecords()
{
return $this->hasOne(TableThree::className(), ['t4_id' => 'id']);
}
然后我收到此错误消息:"SQLSTATE [42S22]:找不到列:1054'on子句'中的未知列'table_4.t4_id'正在执行的SQL是: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
"
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
"
推荐答案
Based on the answer of softark the simplest solution can look like this:
Model TableOne:
Model TableOne:
public function getTableTwoRecords()
{
return $this->hasMany(TableTwo::className(), ['t1_id' => 'id']);
}
模型TableTwo:
Model TableTwo:
public function getTableThreeRecord()
{
return $this->hasOne(TableThree::className(), ['id' => 't3_id']);
}
Model TableThree:
Model TableThree:
public function getTableFourRecord()
{
return $this->hasOne(TableFour::className(), ['id' => 't4_id']);
}
Controller TableOneController:
Controller TableOneController:
public function actionView($id)
{
return $this->render('view', [
'model' => $this->findModel($id),
]);
}
视图table-one/view.php:
The view table-one/view.php:
foreach ($model->tableTwoRecords as $record) {
echo ' Table 2 >> ';
echo ' ID: ' . $record->id;
echo ' T1 ID: ' . $record->t1_id;
echo ' T3 ID: ' . $record->t3_id;
echo ' Table 3 >> ';
echo ' ID: ' . $record->tableThreeRecord->id;
echo ' T4 ID: ' . $record->tableThreeRecord->t4_id;
echo ' Table 4 >> ';
echo ' ID: ' . $record->tableThreeRecord->tableFourRecord->id;
echo ' <br>';
}
也可以使用基于GridView的解决方案.
A solution based on the GridView is also possible.
模型TableTwo:
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');
}
使用Gii为TableTwo模型生成的TableOneController中的actionView函数已被
The function actionView in TableOneController, which was generated with Gii for the model TableTwo was edited:
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,
]);
}
以及views/table-one/view.php:
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',
],
]);
请参阅Github上的代码.
See the code on Github.
这篇关于Yii2:在多个表之间定义关系的正确方法是什么?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!