Need help building up a query based on nested fields.

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] }),
}));

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(),
});