sql - TypeScript Sequelize : How to join two tables with common

Question

一共有三个表。

表格:

旅行

id | start_destination_id | end_destination_id | arrive_time |
-------------------------------------------------------------------
 1 |          S           |          E         |    09:00    |

目的地

id | name
---------
S  | Start
E  | End

日程安排

id | start_destination_id | end_destination_id | should_arrive |
-------------------------------------------------------------------
 1 |          S           |          E         |     08:00     |
 2 |          A           |          E         |     10:00     |

查询

SELECT 
    Trip.*,
    Schedule.should_arrive
FROM 
    Trip
LEFT JOIN 
    Schedule
ON 
    Trip.start_destination_id = Schedule.start_destination_id 
AND 
    Trip.end_destination_id = Schedule.end_destination_id

我试图在 Trip.findAll 中包含 Schedule 但收到错误

异常:SequelizeEagerLoadingError:Schedule is not associated to Trip!

有没有一种方法可以在不使用外键和原始查询的情况下将它们连接在一起？

非常感谢。

Answer 1

终于找到了解决办法(不知道是不是hack)

Schedule.ts

// add these lines
...
@ForeignKey(() => Trip)
@Column({ type: DataType.VIRTUAL })
private _dummyForTrip: undefined;
...

然后在 Schedule 和 Trip 之间创建关联。

Trip.ts

@HasMany(() => Schedule)
public schedules: Schedule[] | null

然后您可以使用 include.on

在 Trip 中包含 Schedule

    const trips = await Trip.findAll({
        include: [{
            model: Schedule,
            on: {
                '$schedules.start$': { [Op.col]: "Trip.start_destination" },
                '$schedules.end$': { [Op.col]: "Trip.end_destination" },
            }
        }],
        where: {
            id: { [Op.in]: payload.inputTripIdArr }
        }
    });