Yii2中的ModelSearch查询错误

Francis 发表于 Dev

弗朗西斯

我有两个桌子project和workload。我想在添加到Workload的某个项目中显示用户，并在该项目中显示另一个成员（我称为该项目的团队成员）。

我的想法是加入2个表project，并workload与user_id条件采取的用户项目user_id，然后从将与参加workload表再次从用户项目采取数据有USER_ID和团队成员的USER_ID将拥有项目。

那我的代码在 WorkloadSearch.php

public function searchWorkloadofUser($params) {
    $user_id = Yii::$app->user->id;
    $query = Workload::find()
        ->select(['workload.project_id', 'workload.commit_time', 'project.project_name', 'workload.from_date', 'workload.to_date', 'workload.workload_type', 'workload.comment'])
        ->join('INNER JOIN', 'project', 'workload.project_id=project.id')
        ->where('workload.user_id = '.$user_id)->orderBy('project.project_name ASC')->distinct();
    $query->join('INNER JOIN','workload', 'project.id = workload.project_id')->distinct();
}

我不明白为什么会出现错误：

SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'workload'
The SQL being executed was: SELECT COUNT(*) FROM (SELECT DISTINCT `workload`.`project_id`, `workload`.`commit_time`, `project`.`project_name`, `workload`.`from_date`, `workload`.`to_date`, `workload`.`workload_type`, `workload`.`comment` FROM `workload` INNER JOIN `project` ON workload.project_id=project.id INNER JOIN `workload` ON project.id = workload.project_id WHERE workload.user_id = 20) `c`

北海贝尔

就像其他人说的那样：您需要一个别名。您的错误消息告诉您：

SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'workload'

这是东阳你加入你的表worload到您的workload表：

$query = Workload::find() //your `workload` table
    ->select([
        ....
    ])
    ->join('INNER JOIN', 'project p'], 'workload.project_id=p.id')
    ->where('workload.user_id = '.$user_id)
    ->orderBy('p.project_name ASC')
    ->distinct();

$query->join('INNER JOIN','workload', 'project.id = workload.project_id') //join the `workload` table to the `workload` table
    ->distinct();

当您想将表联接到自身时，您必须至少为联接表定义一个别名。

我建议您引用这里的Yii2指南中描述的表名和列，不要连接字符串，而是像此处和此处所述绑定参数。此代码应为您工作（未经测试）：

$query = Workload::find() //your `workload` table
    ->select([
        '{{workload}}.[[project_id]]', //quoting tablenames and columns
        '{{%workload}}.[[commit_time]]', //add '%' when you're using table prefix
        ...
    ])
    ->join('INNER JOIN', 'project p'], 'workload.project_id=p.id')
    ->where('workload.user_id' = :user_id, [':user_id' => $user_id]) //You should bind params when use string format
    //or use hash format
    //->where(['workload.user_id' => $user_id])
    ...

$query->join('INNER JOIN','workload w2', 'project.id = w2.project_id') //Use the alias `w2` to  join the `workload` table to the `workload` table
    ->distinct();

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。