I have a column in my DB that was supposed to be unique, but it’s not. Some bad data got in and I ended with the same value in multiple rows. Does prisma have a method to find all rows whose column value occurs more than once in the DB?

You may find groupBy and count methods with condition where _count > 1.

Thanks for reply Takeo! I ran something like that:

const result = await prisma.product.groupBy({
  by: ["handleCz"],
  having: {
    handleCz: {
      _count: {
        gt: 1,
      },
    },
  },
});

But it yields only 19 results where it should be 49. Any idea what’s wrong here?

Hi @Patrick, how about you try this

const result = await prisma.product.groupBy({
    by: ['handleCz'],
    where: {
      handleCz: {
        contains: '....' // enter some value
      }
    },
    _count: {
      handleCz: true,
    },

  })

It may be integrated to one record each having same value, maybe sum of duplicated records’ count of 19 records is 49. If you want all these records in a list, input your result to next query ,

findMany({where: {  handleCz: {in: [...result.map(data => data.handleCz)] }}})

. If not so, sorry, I have no idea unless seeing actual data and the table.

Takeo I tried that and it gives 68 which is a sum of 19 and 49 lol. Idk what happens here but sounds like I’m close, thank you

How did you know how many duplicated records before querying? If you’ve counted without counting based records first had appeared, the answer of subtracting 68 to 19(based records) is 49 and 68 records exists having same values, seem not wrong (though I don’t guess it’s so simple from your reaction). Anyway, including selecting id to your findMany query and listing theme should clarify how many truly duplicated records exist.

I exported data to CSV and my CSV app has the option to find the duplicates. You’re correct. 49 is the number of duplicates without the first occurrences, while 68 is the number of duplicates including the first occurrences