Help needed for Nested Query
For this example, I have 4 tables -
useruserpodcastpodcast, episodesepisodes, and bookmarksbookmarks.- bookmarks table has two columns -
userId
anduserIdepisodeIdepisodeId - each podcast can have multiple
episodeIdepisodeIds, each episode can have only onepodcastIdpodcastId(one to many) - each user can bookmark multiple
episodeIdepisodeIds, each episode can be bookmarked by multipleuserId
s (many to many)userId
episodeIdepisodeIds when userIduserIdpodcastIdpodcastId 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),
}))