find chat based on users IDs

I want help writing the query to find the chat between two users knowing their IDs. the schema:
export const directChats = pgTable('direct_chats', {
id: uuid('id').primaryKey().defaultRandom(),
createdAt: timestamp('created_at', { withTimezone: true })
.defaultNow()
.notNull(),
lastMessageAt: timestamp('last_message_at', { withTimezone: true }),
});

export const chatMembers = pgTable('chat_members', {
id: integer('id').primaryKey().generatedByDefaultAsIdentity(),
chatID: uuid('chat_id')
.references(() => chats.id, { onDelete: 'cascade' })
.notNull(),
joinedAt: timestamp('joined_at', { withTimezone: true })
.defaultNow()
.notNull(),
leftAt: timestamp('left_at', { withTimezone: true }),
userID: integer('user_id')
.references(() => users.id, { onDelete: 'cascade' })
.notNull(),
role: ChatMemberRole('role').default('member').notNull(),
nickname: varchar('nickname', { length: 50 }),
isBanned: boolean('is_banned').default(false).notNull(),
isMuted: boolean('is_muted').default(false).notNull(),
});

export const directChatMembers = pgTable(
'direct_chat_members',
{
chatID: uuid('chat_id')
.references(() => directChats.id)
.notNull(),

userID: integer('user_id')
.references(() => users.id, { onDelete: 'cascade' })
.notNull(),

createdAt: timestamp('created_at', { withTimezone: true })
.defaultNow()
.notNull(),
},
(t) => ({
pk: primaryKey({ columns: [t.chatID, t.userID] }),
})
);
export const directChats = pgTable('direct_chats', {
id: uuid('id').primaryKey().defaultRandom(),
createdAt: timestamp('created_at', { withTimezone: true })
.defaultNow()
.notNull(),
lastMessageAt: timestamp('last_message_at', { withTimezone: true }),
});

export const chatMembers = pgTable('chat_members', {
id: integer('id').primaryKey().generatedByDefaultAsIdentity(),
chatID: uuid('chat_id')
.references(() => chats.id, { onDelete: 'cascade' })
.notNull(),
joinedAt: timestamp('joined_at', { withTimezone: true })
.defaultNow()
.notNull(),
leftAt: timestamp('left_at', { withTimezone: true }),
userID: integer('user_id')
.references(() => users.id, { onDelete: 'cascade' })
.notNull(),
role: ChatMemberRole('role').default('member').notNull(),
nickname: varchar('nickname', { length: 50 }),
isBanned: boolean('is_banned').default(false).notNull(),
isMuted: boolean('is_muted').default(false).notNull(),
});

export const directChatMembers = pgTable(
'direct_chat_members',
{
chatID: uuid('chat_id')
.references(() => directChats.id)
.notNull(),

userID: integer('user_id')
.references(() => users.id, { onDelete: 'cascade' })
.notNull(),

createdAt: timestamp('created_at', { withTimezone: true })
.defaultNow()
.notNull(),
},
(t) => ({
pk: primaryKey({ columns: [t.chatID, t.userID] }),
})
);
1 Reply
Anas Badran
Anas BadranOP2mo ago
the function should look something like this:
const findOrCreateChat = async (fromID, targetID) => {
const existingChat = db.query.directChats.findFirst....
}
const findOrCreateChat = async (fromID, targetID) => {
const existingChat = db.query.directChats.findFirst....
}
Want results from more Discord servers?
Add your server