|
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就是甲的 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 的,提前谢谢大家!
```select * from work_task as t1left join worker as t2 on t1.worker_id = t2.id or t1.other_worker_id = t2.id```
试试这个:select t0.*,t1.name,t2.namefrom work_task t0left join worker as t1 on t0.other_worker_id = t1.idleft join worker as t2 on t0.worker_id = t2.id
建立关联的时候加个 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' },]```
@512357301 @zoharSoul 谢谢两位。用三楼的的方法了,mysql 还不太熟。 谢谢!谢谢!🙏
@px920906 解决了!谢谢老哥。此方法有效。
|
|