DT
Drizzle Team•9mo ago
harshcut

Help needed for Nested Query

For this example, I have 4 tables - user, podcast, episodes, and bookmarks. - bookmarks table has two columns - userId and episodeId - each podcast can have multiple episodeIds, each episode can have only one podcastId (one to many) - each user can bookmark multiple episodeIds, each episode can be bookmarked by multiple userIds (many to many) I want to get all the episodeIds when userId and podcastId is provided from the bookmarks table. 
export const bookmarks = mysqlTable('bookmarks', {
    userId: varchar('userId', { length: 255 }).references(() => user.id),
    episodeId: varchar('episodeId', { length: 50 }).references(() => episodes.id),
  }, (b) => ({
    compoundKey: primaryKey({ columns: [b.userId, b.episodeId] }),
}))
export const podcast = mysqlTable('podcast', {
  id: varchar('id', { length: 32 }).primaryKey(),
  title: varchar('title', { length: 250 }).notNull(),
})
export const episodes = mysqlTable('episodes', {
  id: varchar('id', { length: 50 }).primaryKey(),
  title: varchar('title', { length: 250 }).notNull(),
  podcastId: varchar('podcastId', { length: 32 }).notNull().references(() => podcast.id),
})
export const user = mysqlTable('user', {
  id: varchar('id', { length: 255 }).primaryKey().$defaultFn(() => randomUUID()),
  name: varchar('name', { length: 255 }),
})

export const bookmarksRelations = relations(bookmarks, ({ one }) => ({
  user: one(user, {
    fields: [bookmarks.userId],
    references: [user.id],
  }),
  episode: one(episodes, {
    fields: [bookmarks.episodeId],
    references: [episodes.id],
  }),
}))
export const podcastRelations = relations(podcast, ({ many }) => ({
  episodes: many(episodes),
}))
export const episodesRelations = relations(episodes, ({ one, many }) => ({
  podcast: one(podcast, {
    fields: [episodes.podcastId],
    references: [podcast.id],
  }),
  bookmarks: many(bookmarks),
}))
export const bookmarks = mysqlTable('bookmarks', {
    userId: varchar('userId', { length: 255 }).references(() => user.id),
    episodeId: varchar('episodeId', { length: 50 }).references(() => episodes.id),
  }, (b) => ({
    compoundKey: primaryKey({ columns: [b.userId, b.episodeId] }),
}))
export const podcast = mysqlTable('podcast', {
  id: varchar('id', { length: 32 }).primaryKey(),
  title: varchar('title', { length: 250 }).notNull(),
})
export const episodes = mysqlTable('episodes', {
  id: varchar('id', { length: 50 }).primaryKey(),
  title: varchar('title', { length: 250 }).notNull(),
  podcastId: varchar('podcastId', { length: 32 }).notNull().references(() => podcast.id),
})
export const user = mysqlTable('user', {
  id: varchar('id', { length: 255 }).primaryKey().$defaultFn(() => randomUUID()),
  name: varchar('name', { length: 255 }),
})

export const bookmarksRelations = relations(bookmarks, ({ one }) => ({
  user: one(user, {
    fields: [bookmarks.userId],
    references: [user.id],
  }),
  episode: one(episodes, {
    fields: [bookmarks.episodeId],
    references: [episodes.id],
  }),
}))
export const podcastRelations = relations(podcast, ({ many }) => ({
  episodes: many(episodes),
}))
export const episodesRelations = relations(episodes, ({ one, many }) => ({
  podcast: one(podcast, {
    fields: [episodes.podcastId],
    references: [podcast.id],
  }),
  bookmarks: many(bookmarks),
}))
1 Reply
harshcut
harshcutOP•9mo ago
This is what i got working! 
await db
  .select({ episodeId: bookmarks.episodeId })
  .from(bookmarks)
  .leftJoin(episodes, eq(bookmarks.episodeId, episodes.id))
  .where(and(eq(bookmarks.userId, userId), eq(episodes.podcastId, body.podcastId)))
await db
  .select({ episodeId: bookmarks.episodeId })
  .from(bookmarks)
  .leftJoin(episodes, eq(bookmarks.episodeId, episodes.id))
  .where(and(eq(bookmarks.userId, userId), eq(episodes.podcastId, body.podcastId)))

Did you find this page helpful?