Subqueries in Prisma, Many-Many relation

[Tobbe]

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)

[dthyresson]

@Tobbe

export const productsInCategory = (categoryId) => {
  return db.simpleProduct.findMany({
    where: {
      sku: 
    }
  })
}

@Tobbe:

Have you tried the “include where” – eager load + join + filter?

export const productsInCategory = (categoryId) => {
  return db.simpleProduct.findMany({
    include: { 
      category: { where: { id: categoryId } } 
    }
  })
}

You can also turn on query logging to see the generated query sql to double-check it is doing what you expect.

[Tobbe]

I would try it, if I could actually generate the required sdl…

$ C:\Users\tobbe\dev\redwood\acm-store\node_modules\.bin\rw g sdl CategoryToSimpleProduct
  × Generating SDL files...
    → Cannot generate SDL without an `id` database column
Cannot generate SDL without an `id` database column
Done in 2.12s.

model CategoryToSimpleProduct {
  simpleProduct SimpleProduct @relation(fields: [sku], references: [sku])
  sku           String
  category      Category      @relation(fields: [categoryId], references: [id])
  categoryId    Int

  @@id([sku, categoryId])
}

Why is an id required? Especially when I already have @@id

[dthyresson]

For the one many-to-many relationship I have, I just decided to let Prisma implicitly create the join table:

An article can have many tweets (if some tweets linked an article in it) and a Tweet can have many articles( if multiple ones mentioned).

model Article {
  id             String          @id @default(cuid())
  createdAt      DateTime        @default(now())
  updatedAt      DateTime        @default(now())
  publishedAt    DateTime        @default(now())
  entry          Entry           @relation(fields: [entryId], references: [id])
  entryId        String          @unique
  author         String
  siteName       String?
  title          String
 ...
  tweets         Tweet[]
}

model Tweet {
  id              String          @id @default(cuid())
  createdAt       DateTime        @default(now())
  updatedAt       DateTime        @default(now())
  publishedAt     DateTime
  entry           Entry           @relation(fields: [entryId], references: [id])
  entryId         String          @unique
  author          String
 ...
  articles        Article[]
}

And they are not required because sometimes tweets don’t have articles and vice versa.

Prisma creates the join table with A and B … literally A and B:

But works fine.

You can then link existing via update and connect:

    logger.debug(
      { articleId: article.id, article: article },
      `Linking article ${article.id} to tweet ${tweet.id}`
    )

    await db.tweet.update({
      where: { id: tweet.id },
      data: { articles: { connect: [{ id: article.id }] } },
    })

[Tobbe]

Yeah, I had an implicit join table first, but wanted to make it explicit, with better name, to easier see what’s going on.

[morganmspencer]

I’ve also been trying to figure out something similar. Implicit works well, but it would be good to use explicit if you want to save extra data for the relationship, such as a createdAt value.

[Tobbe]

I wrote a PR that changes it so that an id isn’t always required. https://github.com/redwoodjs/redwood/pull/1327
That was enough at least for me to be able to run the generator and be able to use my explicit lookup table

[dthyresson]

@morganmspencer

you want to save extra data for the relationship, such as a createdAt value.

Ah, right. Good point. Since you cannot necessarily rely on the created or updated (if you set that yourself) of either the A or B record to say what/when was “connected” to the other.

[Tobbe]

@dthyresson I finally got my environment/code in a working state again, so that I could test your code.

const products = await db.simpleProduct.findMany({
  include: {
    categories: { where: { id: categoryId } },
  },
})

It returns 2190 products for a category that has 19 products.

[Tobbe]

I managed to come up with a solution, but I very much feel like there has to be a better way to do this

const mappings = await db.categoryToSimpleProduct.findMany({
  where: {
    categoryId
  }
})

const skus = mappings.map(m => ({sku: m.sku}));

const products = await db.simpleProduct.findMany({
  where: { OR: skus }
})

[dthyresson]

@Tobbe

It returns 2190 products for a category that has 19 products.

Huh - I really thought that was a way.

Of course if you just find the category by id and category.simpleProducts() that may work.

[Tobbe]

The prisma people suggested this:

const products = await db.category.findOne({
  where: { id: 54 },
  include: { products: true },
})

But that gave me this:

api | prisma:query SELECT "public"."Category"."id", "public"."Category"."name", "public"."Category"."parent" FROM "public"."Category" WHERE "public"."Category"."id" = $1 LIMIT $2 OFFSET $3
api | prisma:query SELECT "A", "B" FROM "public"."_CategoryToSimpleProduct" WHERE "A" IN ($1)
api | POST /graphql 200 195.450 ms - 1027
api |
api | GraphQLError: Expected Iterable, but did not find one for field "Query.productsInCategory".

It’s trying to use the implicit lookup table, but I don’t have one because I’m using an explicit one

[Tobbe]

We solved it over here: https://github.com/prisma/prisma/discussions/3893

TL;DR

export const productsInCategory = async ({ categoryId }) => {
  const productsOfCategory = await db.category
    .findOne({
      where: { id: categoryId },
    })
    .CategoryToSimpleProduct({
      select: { simpleProduct: true },
    })

  return productsOfCategory.map(wrapped => wrapped.simpleProduct)
}

[dthyresson]

Seriously? I don’t mind the Prisma query, but … they asked you to do a map? That goes against most database practices where it is discouraged to then process over the result set after a query.

At this point, I might just do a rawQuery. I have been writing some of these today to do “math” – 7 day rolling averages, rankings, etc.

I don’t suppose:

const products = await db.simpleProduct.findMany({
  include: {
    categoryToSimpleProduct: { where: { categoryId } },
  },
})

That feels like a join on simpleProduct to the “join table” where the join table’s category is the id.

[Tobbe]

@dthyresson

const products = await db.simpleProduct.findMany({
  include: {
    categoryToSimpleProduct: { where: { categoryId } },
  },
})

This again gave me 2190 products for a category that has 19 products

[dthyresson]

@Tobbe

This again gave me 2190 products for a category that has 19 products

I give up.

No, I don’t! There has got to be a better way.

I mean when you read:

const result = await prisma.user.findMany({
  where: {
    email: {
      contains: 'prisma.io',
    },
  },
  include: {
    Post: {
      where: {
        published: false,
      },
    },
  },
})

You’d think:

export const productsInCategory = (categoryId) => {
  return db.simpleProduct.findMany({
    include: { 
      categories: { where: { id: categoryId } } 
    }
  })
}

So

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[]
}

Does this mean that you have many-to-many (products to categories), one-to-many (product to categories) and nested self-relations (category to parent/child category)?

[Tobbe]

Yeah, sorry. That’s not the latest schema

model SimpleProduct {
  sku                     String                    @id
  name                    String
  regular_price           Float
  stock                   Int
  images                  String?
  ean                     String?
  manufacturer            String?
  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")
  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])
}