TypeScript Sequelize : How to join two tables with common

Question

There are three tables.

Tables :

Trip

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

Destination

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

Schedule

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

Query

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

I am trying to include Schedule in Trip.findAll but receive error

Exception: SequelizeEagerLoadingError: Schedule is not associated to Trip!

Is there a way that I can join them together without using foreign keys and raw queries?

Many thanks.

Answer 1

Finally I found a solution (not sure if it is a hack).

Schedule.ts

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

Then create an association between Schedule and Trip.

Trip.ts

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

Then you can include Schedule inside Trip by using include.on

    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 }
        }
    });