Drizzle Team•4w ago

Nested queries

Based on the provided schema, how to react the query to fetch first 25 posts a long with their comments and reactions, and each comment have it's own reaction, anyone have any ideas?

export const now = () => sql<Date>`now()`

export const post = pgTable('posts', {
    id: integer('id',).primaryKey().generatedByDefaultAsIdentity(),
    createdAt: timestamp('created_at').defaultNow().notNull(),
    updatedAt: timestamp('updated_at')
        .defaultNow()
        .$onUpdate(now),
    title: varchar('name', { length: 255 }).notNull(),
    content: text('content').notNull()
})


export const reaction = pgTable('reactions', {
    id: integer('id',).primaryKey().generatedByDefaultAsIdentity(),
    createdAt: timestamp('created_at').defaultNow().notNull(),
    type: ReactionType('type').notNull(),
    key: varchar('key', { length: 255 }).notNull()
})

export const comment = pgTable('comments', {
    id: integer('id',).primaryKey().generatedByDefaultAsIdentity(),
    createdAt: timestamp('created_at').defaultNow().notNull(),
    content: text('content').notNull(),
    postsID: integer('post_id').references(() => post.id, { onDelete: 'cascade' }).notNull()
})

export const now = () => sql<Date>`now()`

export const post = pgTable('posts', {
    id: integer('id',).primaryKey().generatedByDefaultAsIdentity(),
    createdAt: timestamp('created_at').defaultNow().notNull(),
    updatedAt: timestamp('updated_at')
        .defaultNow()
        .$onUpdate(now),
    title: varchar('name', { length: 255 }).notNull(),
    content: text('content').notNull()
})


export const reaction = pgTable('reactions', {
    id: integer('id',).primaryKey().generatedByDefaultAsIdentity(),
    createdAt: timestamp('created_at').defaultNow().notNull(),
    type: ReactionType('type').notNull(),
    key: varchar('key', { length: 255 }).notNull()
})

export const comment = pgTable('comments', {
    id: integer('id',).primaryKey().generatedByDefaultAsIdentity(),
    createdAt: timestamp('created_at').defaultNow().notNull(),
    content: text('content').notNull(),
    postsID: integer('post_id').references(() => post.id, { onDelete: 'cascade' }).notNull()
})

1 Reply

Anas Badran•4w ago

the key field in reaction can be in these two forms: - post-{postID}, eg: post-123. - comment-{commentID, eg: comment-123.

Gaming

Programming

Nested queries