In Yii 2.0, one field in a correlation table is implemented by the two tables at the same time
1. Column configuration: pa_config_column, as shown in Figure 1
Figure 1
2. Column staff configuration: PA_CONFIG_COLUMN_USER, as shown in Figure 2
Figure 2
3. Topic: PA_plan, as shown in Figure 3
Figure 3
4. Topic selection task: PA_PLAN_TASK, as shown in Figure 4
Figure 4
5. Task management – my task, query rules
(1) The tenant ID of the task is the tenant ID of the currently logged in user;
(2) The column configuration has not been deleted;
(3) The topic selection has not been deleted;
(4) The topic selection task has not been deleted;
(5) The execution user ID of the topic selection task (`PA_PLAN_TASK`.`EXEC_USER_ID`) is the current login user ID && To select the topic to create a user ID (`pa_plan`.`create_user_id`) is the current logged in user ID && && Column staff configuration character IDs include the person in charge of the column leader && && Column staff configuration has not been deleted);
6. The current query rules are required to be the main table of topic selection tasks: PA_PLAN_TASK, which requires an association table Topic selection: PA_PLAN; Column staff configuration: PA_CONFIG_COLUMN_USER, based on `PA_PLAN_TASK`.`EXEC_USER_ID`; Column staff configuration: PA_CONFIG_COLUMN_USER, based on `PA_plan`.`create_user_id`
7. \common\logics\plantask.php, declare the relationship
public function getConfigColumn()
{
return $this->hasOne(ConfigColumn::className(), [ 'id' => 'config_column_id' ]);
}
public function getPlanById()
{
return $this->hasOne(Plan::className(), [ 'id' => 'plan_id' ]);
}
public function getConfigColumnUser()
{
return $this->hasOne(ConfigColumnUser::className(), [ 'config_column_id' => 'config_column_id', 'user_id' => 'exec_user_id' ]);
}
8. \common\logics\plan.php, declare the relationship
public function getConfigColumn()
{
return $this->hasOne(ConfigColumn::className(), [ 'id' => 'config_column_id' ]);
}
public function getConfigColumnUser()
{
return $this->hasOne(ConfigColumnUser::className(), [ 'config_column_id' => 'config_column_id', 'user_id' => 'create_user_id' ]);
}
9. The query code is finally as follows. If a field in an association table is associated with two tables at the same time, it is necessary to set the alias of the associated table
$query = $modelClass::find()
->joinWith('configColumn')
->joinWith(['configColumnUser ccue'])
->joinWith(['planById' => function($q) {
$q->joinWith('configColumnUser ccuc');
}])
->where([
'and',
[$modelClass::tableName() . '.group_id' => $identity->group_id],
[ConfigColumn::tableName() . '.is_deleted' => ConfigColumn::IS_DELETED_NO],
[Plan::tableName() . '.is_deleted' => Plan::IS_DELETED_NO],
[$modelClass::tableName() . '.is_deleted' => $modelClass::IS_DELETED_NO]
])
->andWhere([
'or',
[
'and',
[$modelClass::tableName() . '.exec_user_id' => $identity->id],
['ccue.is_deleted' => ConfigColumnUser::IS_DELETED_NO]
],
[
'and',
[Plan::tableName() . '.create_user_id' => $identity->id],
['ccuc.is_deleted' => ConfigColumnUser::IS_DELETED_NO]
],
[
'in',
Plan::tableName() . '.config_column_id',
$configColumnIds
]
])
->orderBy([$modelClass::tableName() . '.sort_order' => SORT_ASC, $modelClass::tableName() . '.id' => SORT_DESC]);
10. The final generated SQL statement is as follows:
SELECT `pa_plan_task`.* FROM `pa_plan_task` LEFT JOIN `pa_config_column` ON `pa_plan_task`.`config_column_id` = `pa_config_column`.`id` LEFT JOIN `pa_config_column_user` `ccue` ON `pa_plan_task`.`config_column_id` = `ccue`.`config_column_id` AND `pa_plan_task`.`exec_user_id` = `ccue`.`user_id` LEFT JOIN `pa_plan` ON `pa_plan_task`.`plan_id` = `pa_plan`.`id` LEFT JOIN `pa_config_column_user` `ccuc` ON `pa_plan`.`config_column_id` = `ccuc`.`config_column_id` AND `pa_plan`.`create_user_id` = `ccuc`.`user_id` WHERE (`pa_plan_task`.`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`pa_config_column`.`is_deleted`=0) AND (`pa_plan`.`is_deleted`=0) AND (`pa_plan_task`.`is_deleted`=0) AND (((`pa_plan_task`.`exec_user_id`='8') AND (`ccue`.`is_deleted`=0)) OR ((`pa_plan`.`create_user_id`='8') AND (`ccuc`.`is_deleted`=0)) OR (`pa_plan`.`config_column_id`=1)) ORDER BY `pa_plan_task`.`sort_order`, `pa_plan_task`.`id` DESC LIMIT 10
