_count doesn't update in many to many relation

[Sakkhor909]

Bug description

I have many-to-many relations in my schema like brand and category. I am using _.count to see how many brands are there in a category. It seems to work but when I delete one of the brand it does not update _.count in the category.

This is the model :

model Category {
  id        String              @id @default(cuid())
  name       String              @unique
  createdAt DateTime            @default(now())
  brands Brand[]
}

model Brand {
  id         String              @id @default(cuid())
  name       String              @unique
  createdAt  DateTime            @default(now())
  categories   Category[] 
}

I am showing how many brands in a category by pulling the data like this

   const Data = await prisma.category.findMany({
        include: {
          _count: {
            select: { brands: true }
          }
        }
      });

It works fine. It shows how many brands are in a category.
When I try to delete a brand like this;

 prisma.brand
      .delete({
        where: {
          id: ID
        }
      })

The brand gets deleted but the category list doesn't update. It still includes that brand in _count.
I have deleted the same way in one-to-many relation where _count updated. How do I do it in many to many?

How to reproduce

The project is complicated so If you want you can use just two models to follow the bugs

model Category {
  id        String              @id @default(cuid())
  name       String              @unique
  createdAt DateTime            @default(now())
  brands Brand[]
}

model Brand {
  id         String              @id @default(cuid())
  name       String              @unique
  createdAt  DateTime            @default(now())
  categories   Category[] 
}

Expected behavior

When I delete a brand, the Category list (._count) also should be updated. like when I use this code to see.

   const Data = await prisma.category.findMany({
        include: {
          _count: {
            select: { brands: true }
          }
        }
      });

Environment & setup

• OS: Windows 10
• Database: MySQL
• Node.js version: v16.14.1

Prisma Version

prisma                  : 3.11.0
@prisma/client          : 3.11.0
Current platform        : windows
Query Engine (Node-API) : libquery-engine b371888aaf8f51357c7457d836b86d12da91658b (at node_modules\@prisma\engines\query_engine-windows.dll.node)
Migration Engine        : migration-engine-cli b371888aaf8f51357c7457d836b86d12da91658b (at node_modules\@prisma\engines\migration-engine-windows.exe)
Introspection Engine    : introspection-core b371888aaf8f51357c7457d836b86d12da91658b (at node_modules\@prisma\engines\introspection-engine-windows.exe)
Format Binary           : prisma-fmt b371888aaf8f51357c7457d836b86d12da91658b (at node_modules\@prisma\engines\prisma-fmt-windows.exe)
Default Engines Hash    : b371888aaf8f51357c7457d836b86d12da91658b
Studio                  : 0.458.0
Preview Features        : referentialIntegrity

[jkomyno]

I can confirm this bug with the current version of Prisma with referentialIntegrity = prisma.
If we use referentialIntegrity = foreignKeys, instead, everything seems to be fine.
I have target databases MySQL 8.0 and Postgres 14, but the issue doesn't seem connector-related.

Schema

    generator client {
      provider = "prisma-client-js"
      previewFeatures = ["referentialIntegrity"]
    }
    
    datasource db {
      provider = "mysql"
      url      = env("DATABASE_URI_MYSQL")
      referentialIntegrity = "prisma"
    }
    
    model Category {
      id     String  @id @default(cuid())
      name   String  @unique
      brands Brand[]
    }
    
    model Brand {
      id         String     @id @default(cuid())
      name       String     @unique
      categories Category[] 
    }

## Test

await prisma.category.create({
  data: {
    name: 'cat-1',
    brands: {
      create: [{ name: 'brand-1' }, { name: 'brand-2' }],
    },
  },
})

await prisma.category.create({
  data: {
    name: 'cat-2',
    brands: {
      create: [{ name: 'brand-3' }, { name: 'brand-4' }],
    },
  },
  include: { brands: true },
})

const categories = await prisma.category.findMany({
  include: {
    _count: {
      select: { brands: true },
    },
  },
})
expect(categories).toMatchObject([
  {
    _count: { brands: 2 },
    name: 'cat-1',
  },
  {
    _count: { brands: 2 },
    name: 'cat-2',
  }
])

await prisma.brand.delete({ where: { name: 'brand-1' } })

const categoriesAfterBrand1Deletion = await prisma.category.findMany({
  include: {
    _count: {
      select: { brands: true },
    },
  },
})
expect(categoriesAfterBrand1Deletion).toMatchObject([
  {
    _count: { brands: 1 }, // the count is 2 with referentialIntegrity = prisma
    name: 'cat-1',
  },
  {
    _count: { brands: 2 },
    name: 'cat-2',
  }
])

[pimeys]

With 4.3.1 I cannot reproduce this issue anymore. I ran the given script and got the right result both times. Can you maybe test it again with the latest version @Sakkhor909?

[Sakkhor909]

With 4.3.1 I cannot reproduce this issue anymore. I ran the given script and got the right result both times. Can you maybe test it again with the latest version @Sakkhor909?

Ok, I will update and see. Should I do anything extra ?

[pimeys]

Read the docs:

https://www.prisma.io/docs/guides/upgrade-guides/upgrading-versions/upgrading-to-prisma-4

Most of the time db pull should be enough.

[Jolg42]

I can reproduce with 4.2.1 but not 4.3.1, it seems it was fixed.

[jkomyno]

My reproduction above still stands for the following recent Prisma versions:

• 4.1.0
• 4.2.0
• 4.2.1

[Sakkhor909]

Yes. The issue has solved!!!

[Jolg42]

After a short investigation I found out that it was fixed in our internal dev version 4.3.0-dev.37
The prisma-engines PR is about the new preview feature filteredRelationCount from @Weakky prisma/prisma-engines#3057
It seems the PR accidentally fixed this issue?