Hello
I have products organized in different categories. One product can exist in several categories, and a category can obviously have many products.
My (somewhat simplified) schema looks like this
model SimpleProduct {
sku String @id
name String
regular_price Float
stock Int
images String?
ean String?
manufacturer String?
categories Category[]
CategoryToSimpleProduct CategoryToSimpleProduct[]
}
model Category {
id Int @id @default(autoincrement())
name String
parent Int?
parentCategory Category? @relation("CategoryParent", fields: [parent], references: [id])
childrenCategories Category[] @relation("CategoryParent")
products SimpleProduct[]
CategoryToSimpleProduct CategoryToSimpleProduct[]
}
model CategoryToSimpleProduct {
simpleProduct SimpleProduct @relation(fields: [sku], references: [sku])
sku String
category Category @relation(fields: [categoryId], references: [id])
categoryId Int
@@id([sku, categoryId])
}
Given a category id, how can I query for all products in that category?
IIRC I’d do something like this in SQL (it’s been like 10 years since I worked with SQL, but hope you get the idea)
SELECT name, regular_price, images
FROM SimpleProduct
WHERE sku IN (SELECT sku
FROM CategoryToProduct
WHERE categoryId = 54)
Or, if you prefer joins
SELECT name, regular_price, images
FROM SimpleProduct
JOIN CategoryToProduct ON SimpleProduct.sku = CategoryToProduct.sku
WHERE CategoryToProduct.categoryId = 54
How would I do that same thing in Prisma?
I tried starting building it, but this is how far I got:
export const productsInCategory = (categoryId) => {
return db.simpleProduct.findMany({
where: {
sku:
}
})
}
Didn’t know what to put for the value of sku
…
(This is a x-post of Subqueries in Prisma, M-N relation · Discussion #3893 · prisma/prisma · GitHub)