Need help building up a query based on nested fields.

I need some help to query the chat that has only the two members only private chat based on the schema below.

3 Replies

export const messages = pgTable('messages', {
  id: integer('id').primaryKey().generatedByDefaultAsIdentity(),
  createdAt: timestamp('created_at', { withTimezone: true })
    .defaultNow()
    .notNull(),
  content: text('content').notNull(),
  chatID: integer('chat_id')
    .notNull()
    .references(() => chats.id),
  senderID: integer('from_id')
    .references(() => users.id, { onDelete: 'cascade' })
    .notNull(),
  isEdited: boolean('is_edited').default(false).notNull(),
});

export const chats = pgTable('chats', {
  id: integer('id').primaryKey().generatedByDefaultAsIdentity(),
  createdAt: timestamp('created_at', { withTimezone: true })
    .defaultNow()
    .notNull(),
  name: varchar('name', { length: 255 }).notNull(),
  image: varchar('image'),
  bio: text('bio'),
});

export const chatMembers = pgTable('chat_member', {
  id: integer('id').primaryKey().generatedByDefaultAsIdentity(),
  chatID: integer('chat_id')
    .references(() => chats.id, { onDelete: 'cascade' })
    .notNull(),
  joinedAt: timestamp('joined_at', { withTimezone: true })
    .defaultNow()
    .notNull(),
  leftAt: timestamp('left_at'),
  userID: integer('user_id')
    .references(() => users.id, { onDelete: 'cascade' })
    .notNull(),
});

export const chatRelations = relations(chats, ({ many }) => ({
  members: many(chatMembers),
  messages: many(messages),
}));

export const chatMemberssRelations = relations(chatMembers, ({ one }) => ({
  user: one(users, {
    fields: [chatMembers.userID],
    references: [users.id],
  }),
  chat: one(chats, {
    fields: [chatMembers.chatID],
    references: [chats.id],
  }),
}));

export const messagessRelations = relations(messages, ({ one }) => ({
  chat: one(chats, {
    fields: [messages.chatID],
    references: [chats.id],
  }),
  sender: one(users, { fields: [messages.senderID], references: [users.id] }),
}));

export const messages = pgTable('messages', {
  id: integer('id').primaryKey().generatedByDefaultAsIdentity(),
  createdAt: timestamp('created_at', { withTimezone: true })
    .defaultNow()
    .notNull(),
  content: text('content').notNull(),
  chatID: integer('chat_id')
    .notNull()
    .references(() => chats.id),
  senderID: integer('from_id')
    .references(() => users.id, { onDelete: 'cascade' })
    .notNull(),
  isEdited: boolean('is_edited').default(false).notNull(),
});

export const chats = pgTable('chats', {
  id: integer('id').primaryKey().generatedByDefaultAsIdentity(),
  createdAt: timestamp('created_at', { withTimezone: true })
    .defaultNow()
    .notNull(),
  name: varchar('name', { length: 255 }).notNull(),
  image: varchar('image'),
  bio: text('bio'),
});

export const chatMembers = pgTable('chat_member', {
  id: integer('id').primaryKey().generatedByDefaultAsIdentity(),
  chatID: integer('chat_id')
    .references(() => chats.id, { onDelete: 'cascade' })
    .notNull(),
  joinedAt: timestamp('joined_at', { withTimezone: true })
    .defaultNow()
    .notNull(),
  leftAt: timestamp('left_at'),
  userID: integer('user_id')
    .references(() => users.id, { onDelete: 'cascade' })
    .notNull(),
});

export const chatRelations = relations(chats, ({ many }) => ({
  members: many(chatMembers),
  messages: many(messages),
}));

export const chatMemberssRelations = relations(chatMembers, ({ one }) => ({
  user: one(users, {
    fields: [chatMembers.userID],
    references: [users.id],
  }),
  chat: one(chats, {
    fields: [chatMembers.chatID],
    references: [chats.id],
  }),
}));

export const messagessRelations = relations(messages, ({ one }) => ({
  chat: one(chats, {
    fields: [messages.chatID],
    references: [chats.id],
  }),
  sender: one(users, { fields: [messages.senderID], references: [users.id] }),
}));

the user:

export const users = pgTable('users', {
  id: integer('id').primaryKey().generatedByDefaultAsIdentity(),
  createdAt: timestamp('created_at', { withTimezone: true, mode: 'string' })
    .defaultNow()
    .notNull(),
  firstName: varchar('first_name', { length: 255 }).notNull(),
  middleName: varchar('middle_name', { length: 255 }),
  lastName: varchar('last_name', { length: 255 }).notNull(),
  username: varchar('username', { length: 255 }).notNull().unique(),
  nickname: varchar('nickname', { length: 255 }),
  password: varchar('password', { length: 255 }).notNull(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  role: UserRole('role').default('user').notNull(),
  profileImage: varchar('profile_image', { length: 255 }),
  isVerified: boolean('is_verified').default(false).notNull(),
  bio: text('bio'),
  isApproved: boolean('is_approved').default(false).notNull(),
  isFamily: boolean('is_family').default(false).notNull(),
});

export const users = pgTable('users', {
  id: integer('id').primaryKey().generatedByDefaultAsIdentity(),
  createdAt: timestamp('created_at', { withTimezone: true, mode: 'string' })
    .defaultNow()
    .notNull(),
  firstName: varchar('first_name', { length: 255 }).notNull(),
  middleName: varchar('middle_name', { length: 255 }),
  lastName: varchar('last_name', { length: 255 }).notNull(),
  username: varchar('username', { length: 255 }).notNull().unique(),
  nickname: varchar('nickname', { length: 255 }),
  password: varchar('password', { length: 255 }).notNull(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  role: UserRole('role').default('user').notNull(),
  profileImage: varchar('profile_image', { length: 255 }),
  isVerified: boolean('is_verified').default(false).notNull(),
  bio: text('bio'),
  isApproved: boolean('is_approved').default(false).notNull(),
  isFamily: boolean('is_family').default(false).notNull(),
});

DoggeSlapper•2mo ago

Something like this should work

async function getPrivateChats() {
  const privateChats = await db
    .select()
    .from(chats)
    .innerJoin(chatMembers, chatMembers.chatID.eq(chats.id))
    .groupBy(chats.id)
    .having(db.raw('COUNT(chat_member.user_id) = 2'));

  return privateChats;
}

async function getPrivateChats() {
  const privateChats = await db
    .select()
    .from(chats)
    .innerJoin(chatMembers, chatMembers.chatID.eq(chats.id))
    .groupBy(chats.id)
    .having(db.raw('COUNT(chat_member.user_id) = 2'));

  return privateChats;
}

Anas BadranOP•2mo ago

What about getting a single chat being provided the fromID and targetID I mean getting the chat that has two members the sender: fromID and the target: targetID 'createOrFindChat(fromID, targetID)` https://discord.com/channels/1043890932593987624/1284040665453563936

Gaming

Programming

Need help building up a query based on nested fields.