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 BadranOP•3mo 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....
}

Gaming

Programming

find chat based on users IDs