query based on deep relations
I need help modifying the query code to properly query the chat where it has the the two members.
export const findOrCreateChat = async (fromID: number, toID: number) => {
const existingchat = await db.query.chats.findFirst({
with: { members: true },
where(fields, { and, eq }) {
// the line below needs fix.
return and(eq(chatMembers.userID, fromID), eq(chatMembers.userID, toID));
},
});
if (existingchat) {
return existingchat;
}
const newChat = await db
.insert(chats)
.values({
name: `new chat`,
})
.returning();
await db.insert(chatMembers).values([
{ chatID: newChat[0].id, userID: fromID },
{ chatID: newChat[0].id, userID: toID },
]);
return newChat[0];
};
export const findOrCreateChat = async (fromID: number, toID: number) => {
const existingchat = await db.query.chats.findFirst({
with: { members: true },
where(fields, { and, eq }) {
// the line below needs fix.
return and(eq(chatMembers.userID, fromID), eq(chatMembers.userID, toID));
},
});
if (existingchat) {
return existingchat;
}
const newChat = await db
.insert(chats)
.values({
name: `new chat`,
})
.returning();
await db.insert(chatMembers).values([
{ chatID: newChat[0].id, userID: fromID },
{ chatID: newChat[0].id, userID: toID },
]);
return newChat[0];
};
1 Reply
the schema:
this code is certainly invalid, I need soemone to write they query to select the chat that has two members the sender and the target, based on their IDs.
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] }),
}));