Imagine you're storing which videos are on which playlist (like Youtube). One requirement is that duplicate videos are allow, so instead of using

playlistId

and

videoId

as composite keys, you have a unique

id

on the model. If you had the `playlistId`/`videoId` pairs, but not the

id

s, is there an efficient method to find all of the rows for the pairs? (

schema.prisma

and data in thread)

Do you mean you want to list up the distinct pairs?

Hey Ben 👋 Do you need to search on playlistId and videoId? For efficient querying, I would recommend you create a multi-column index by combining both these fields. Here’s an example on how you can define the index: Multi Column Index

@Hyo yes, I want to find the

id

for each of those distinct pairs of `playlistId`/`videoId` @Nurul correct, and in bulk. I'm given a list of pairs of `playlistId`/`videoId` and I need to find the corresponding

id

from

playlistVideo

. Can you

find

by a multi-column index? Say my new schema is as follows:

model PlaylistVideo {
  id         String @id
  playlistId String @map("playlist_id")
  videoId    String @map("video_id")

  @@map("playlist_video")
  @@index(fields: [playlist_id, video_id], name: "multi_index")
}

I know this next part isn't the correct syntax, but is something like this possible?

await prisma.playlistVideo.findMany({
  where: {
    multi_index: {
      in: [
        { playlistId: 'playlist-one', videoId: 'video-one' },
        { playlistId: 'playlist-one', videoId: 'video-two' },
        // ... (hundreds of entries)
        { playlistId: 'playlist-ninety', videoId: 'video-eighty' }
      ]
    }
  }
})

Got it! I don’t think this would be possible but definitely a valid use case we should support. For now, you could do something like this, by iterating over the array.

const arr: any[] = [
    { playlistId: 'playlist-one', videoId: 'video-one' },
    { playlistId: 'playlist-one', videoId: 'video-two' },
  ];

  for (let i = 0; i < arr.length; i++) {
    const videos = await prisma.playlistVideo.findMany({
      where: {
        playlistId: arr[i].playlistId,
        videoId: arr[i].videoId,
      },
    });
  }

Yeah we would love to have this supported! We have a few models where we would need this. Should I open an issue for this? Do you think your recommendation would be more performant than doing a massive

OR

in the

where

? And this would potentially be for hundreds of records at a time. That's why we were leaning away from one

findMany

per record.

const arr: any[] = [
  { playlistId: 'playlist-one', videoId: 'video-one' },
  { playlistId: 'playlist-one', videoId: 'video-two' }
];

const videos = await prisma.playlistVideo.findMany({
    where: {
        OR: arr
    },
});

Using

OR

operator is indeed a suitable solution. I would suggest logging time taken by query in both the cases, there shouldn’t be much difference but it would help in determining which approach to take. You can use middleware to log the time taken.

Sweet, we'll play around with that. From our first attempt with it, it was able to find ~4k records in under a second (all indexed columns), so it seems pretty quick!

hi, stumbled upon this thread and thought to share. we too had to do a similar query so we used

this.prismaService.protectionAction.deleteMany({
    where: {
          OR: protectionActionsToDelete,
       },
     }),

and for big queries it took very long or even filled postgres ram entirely. when we switched to

Prisma.sql`delete from protection_actions
                 where (scope_id, protection_permanent_id, profile_permanent_id) in ((${Prisma.join(
                   protectionActionsWhereTuple,
                   '),(',
                 )}))`

the times dropped by 90% and memory use by 75% (table has index on the required fields). do you think the

in

way of doing it will be supported by prisma soon?