Query from 2 Tables
Heya there, I am currently trying to query a product, I basically have a one to many setup with product and images and a many to many with products and tags. So essentially I want ot query the product with the images and tags together. When I add productTags: true, it seems to break the whole query. Any clues 👀
``js
export const products = pgTable("products", {
id: serial("id").primaryKey(),
description: text("description").notNull(),
price: real("price"),
title: text("title").notNull(),
color: text("color").notNull(),
subtitle: text("subtitle").notNull(),
})
export const productImages = pgTable("productImages", {
id: serial("id").primaryKey(),
url: text("image").notNull(),
size: real("size").notNull(),
name: text("name").notNull(),
key: text("key").notNull(),
productID: integer("productID")
.notNull()
.references(() => products.id, { onDelete: "cascade" }),
})
export const productTags = pgTable("productTags", {
id: serial("id").primaryKey(),
tag: text("tag").notNull(),
productID: integer("productID")
.notNull()
.references(() => products.id, { onDelete: "cascade" }),
})
export const productImagesRelations = relations(productImages, ({ one }) => ({
product: one(products, {
fields: [productImages.productID],
references: [products.id],
}),
}))
export const productTagRelations = relations(productTags, ({ many }) => ({
products: many(products),
}))
export const productRelations = relations(products, ({ many }) => ({
productImages: many(productImages),
productTags: many(productTags),
}))
//Get individual product
export async function getProduct(id: number) {
try {
const productData = await db.query.products.findMany({
where: eq(products.id, id),
with: {
productImages: true,
productTags: true //Shows up in intellisense but breaks
},
})
return { productData }
} catch (error) {
return { error: Can't load product 😵` }
}
}2 Replies
When you have a many-to-many relation you need to create an intermediary table
I see that you attempted to add many tags to products, and many products to tags. This will not work.
You can google many-to-many and get a lot of information on how and why this is done
There is also an example in the docs: https://orm.drizzle.team/docs/rqb#many-to-many
Drizzle ORM - next gen TypeScript ORM
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.