Drizzle Team•4mo ago

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

Gaming

Programming

One to Many with composite primary key?