One to Many with composite primary key?

I am working on a transit-related application (not super important, but may provide context for mental model of the below tables). I am importing data that is conforming to the gtfs spec, so I am not doing any pre/post processing of the data provided in a collection of csv files, I am just importing them straight into a database. I have a table of "Shapes" (which really should be though of as points (lat/lon) along a bus trip). The primary key is a combination of the id and sequence fields. I also have a table of "Trips" which contains a shape_id in the table. Different trips may have the same shape_id (e.g. think of trips happening at different times). Because the Shapes table has a composite primary key, I cannot figure out how to create a relation from the Trips table to successfully query the shape rows for a particular trip.
// /*************************
// *
// * SHAPES TABLE
// *
// *************************/

export const shapes = pgTable(
'shapes',
{
id: bigint('shape_id', { mode: 'number' }).notNull(), // NOT A SIMPLE PRIMARY KEY
sequence: integer('shape_pt_sequence').notNull(),
lat: real('shape_pt_lat').notNull(),
lon: real('shape_pt_lon').notNull(),

},
table => {
return {
pk: primaryKey(table.id, table.sequence),
idIndex: index('id_idx').on(table.id),
}
},
)
// /*************************
// *
// * SHAPES TABLE
// *
// *************************/

export const shapes = pgTable(
'shapes',
{
id: bigint('shape_id', { mode: 'number' }).notNull(), // NOT A SIMPLE PRIMARY KEY
sequence: integer('shape_pt_sequence').notNull(),
lat: real('shape_pt_lat').notNull(),
lon: real('shape_pt_lon').notNull(),

},
table => {
return {
pk: primaryKey(table.id, table.sequence),
idIndex: index('id_idx').on(table.id),
}
},
)
// example data
// id, sequence, lat, lon
// 1, 1, 13.818860, -80.450519
// 1, 2, 13.819909, -80.452400
// 1, 3, 13.819940, -80.452449
// 1, 4, 13.820310, -80.453109
// 2, 1, 13.818860, -80.450519
// 2, 2, 13.819909, -80.452400
// 2, 3, 13.819940, -80.452449
// 2, 4, 13.820310, -80.453109
// example data
// id, sequence, lat, lon
// 1, 1, 13.818860, -80.450519
// 1, 2, 13.819909, -80.452400
// 1, 3, 13.819940, -80.452449
// 1, 4, 13.820310, -80.453109
// 2, 1, 13.818860, -80.450519
// 2, 2, 13.819909, -80.452400
// 2, 3, 13.819940, -80.452449
// 2, 4, 13.820310, -80.453109
// /*************************
// *
// * TRIPS TABLE
// *
// *************************/

export const trips = pgTable(
'trips',
{
id: bigint('trip_id', { mode: 'number' }).notNull().primaryKey().unique(),
shortName: text('trip_short_name'),
shapeId: bigint('shape_id', { mode: 'number' }), // cannot say .references(() => shapes.id) b/c that is not unique nor primary key
},
table => {
return {
idIndex: index('id_idx').on(table.id),
}
},
)
// /*************************
// *
// * TRIPS TABLE
// *
// *************************/

export const trips = pgTable(
'trips',
{
id: bigint('trip_id', { mode: 'number' }).notNull().primaryKey().unique(),
shortName: text('trip_short_name'),
shapeId: bigint('shape_id', { mode: 'number' }), // cannot say .references(() => shapes.id) b/c that is not unique nor primary key
},
table => {
return {
idIndex: index('id_idx').on(table.id),
}
},
)
// example data
// id, shortName, shape_id
// 1, Orange Line 1pm, 1
// 2, Orange Line 2pm, 1
// 3, Orange Line 3pm, 1
// 4, Blue Line 1pm, 2
// 5, Blue Line 2pm, 2
// 6, Blue Line 3pm, 2
// example data
// id, shortName, shape_id
// 1, Orange Line 1pm, 1
// 2, Orange Line 2pm, 1
// 3, Orange Line 3pm, 1
// 4, Blue Line 1pm, 2
// 5, Blue Line 2pm, 2
// 6, Blue Line 3pm, 2
While it's true that each shape_id has many trips, I would probably never need to look that up, but I can still say something like
export const shapeRelations = relations(shapes, ({ many }) => {
return {
trips: many(trips),
}
})
export const shapeRelations = relations(shapes, ({ many }) => {
return {
trips: many(trips),
}
})
But where I'm running into trouble is how to create the relationship for Trips to get the shapes[] results
export const tripRelations = relations(trips, ({ many }) => {
return {
// ??!??!!? this doesn't really make sense without being able
// to specify some kind of link between `trips.shape_id` and `shapes.id`
shapes: many(shapes),
}
})
export const tripRelations = relations(trips, ({ many }) => {
return {
// ??!??!!? this doesn't really make sense without being able
// to specify some kind of link between `trips.shape_id` and `shapes.id`
shapes: many(shapes),
}
})
I suppose in the worst case I would have to manually create a many-to-many join table between trips and shapes and use the typical many-to-many pattern, but I would love to avoid that if there is some mechanism to achieve the above. The join table would essentially be
trip_id, shape_id
1, 1
2, 1
3, 1
4, 2
5, 2
6, 2
trip_id, shape_id
1, 1
2, 1
3, 1
4, 2
5, 2
6, 2
with no repeating trip_id (which isn't much of a join table). Anyway, I know this was a lot of text, but I hope it is sufficient information to explain what I am trying to accomplish.
0 Replies
No replies yetBe the first to reply to this messageJoin
Want results from more Discord servers?
Add your server