sequelize、mysql 问题求解答！一张表多个关联关系问题。

This topic created in 1748 days ago, the information mentioned may be changed or developed.

Worker.hasMany(Worker_task, { foreignKey: 'worker_id' }) Worker_task.belongsTo(Worker, { foreignKey: 'worker_id', targetKey: 'id' }) Worker.hasMany(Worker_task, { foreignKey: 'other_workerid' }) Worker_task.belongsTo(Worker, { foreignKey: 'other_workerid', targetKey: 'id' })

上面是我的数据表关系。worker_task表中worker_id 字段指向worker.id，为一对多关系。

worker_task表中other_workerid字段同样指向worker.id，为一对多关系。

业务简化逻辑：

员工、员工任务。员工甲委派任务给员工乙，worker_id/code>就是甲的 id，other_workerid就是乙的 id 。

 现在要通过，worker_task 表查询出，worker_id 和 other_workerid 对应的信息。下边是我的 sequelize 查询语句。
 // workerId const data = await Worker_task.findAndCountAll({ attributes: ['worker_id', 'task_id', 'trust_relation', 'other_workerid'], include: [ // TODO 现在查出来，是 other_workerid 对应的 worker 。我想把 worker_id 和 other_workerid 对应的 worker 记录都查询出来。 { model: Worker, attributes: ['name'] }, ], where: { [Op.or]: [ { worker_id: workerId, }, { other_workerid: workerId, }, ], }, }) 
 求助 sequelize 语句或者原始 mysql 语句。或者换其他可行的思路也是 ok 的，提前谢谢大家！

  
  
 Worker
 worker_id
 foreignkey
 workerid
5 replies    2021-07-31 00:53:29 +08:00 
           1
 
 zoharSoul   
   Jul 30, 2021 
 ```
select * from work_task as t1
left join worker as t2 on t1.worker_id = t2.id or t1.other_worker_id = t2.id
```
  
 
 
           2
 
 512357301   
   Jul 30, 2021 via Android 
 试试这个：

select 
t0.*
,t1.name
,t2.name
from work_task t0
left join worker as t1 on t0.other_worker_id = t1.id
left join worker as t2 on t0.worker_id = t2.id
  
 
 
           3
 
 px920906   
   Jul 30, 2021 
 建立关联的时候加个 alias 试试：
```
Worker.hasMany(Worker_task, { foreignKey: 'worker_id', as: 'worker'})
Worker.hasMany(Worker_task, { foreignKey: 'other_workerid' , as: 'other_worker'})

include: [
 { model: Worker, attributes: ['name'], as: 'worker' },
 { model: Worker, attributes: ['name'], as: 'other_worker' },
]
```
  
 
 
           4
 
 yishen0   OP
   Jul 31, 2021 via iPad 
 @512357301 @zoharSoul 谢谢两位。用三楼的的方法了，mysql 还不太熟。 谢谢！谢谢！
  
 
 
           5
 
 yishen0   OP
   Jul 31, 2021 via iPad 
 @px920906 解决了！谢谢老哥。此方法有效。