Querying over a relationship table

[felippepuhle]

Hey team, how is it going?

Great job on this library! I've been playing around with it and it will make my life easy peasy! :)

I have a question though: I have a chat project and we have a connection for chats that user is currently a member, and for performance issues I need to query over a relationship table instead of the chat table itself... model would be something like:
chat <-> chat_user <-> user

I didn't fully understand what would be the right implementation if I want to query over chat_user table but returning a connection of chats instead, can you help me with that?

Here's a simplified version of my schema.prisma: (hiding a bunch of fields just for demonstration purposes)

model chat {
  id                        String               @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  name                      String?
  image_url                 String?
}

model chat_user {
  chat_id                 String    @db.Uuid
  user_id                 String    @db.Uuid
  last_opened             DateTime  @default(now()) @db.Timestamptz(6)
  created_at              DateTime? @default(now()) @db.Timestamptz(6)
  is_admin                Boolean   @default(false)
}

model user {
  id                 String               @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  first_name         String
  last_name          String
}

Thanks so much!

[queicherius]

Hey, glad you are enjoying the library :)

I didn't fully understand what would be the right implementation if I want to query over chat_user table but returning a connection of chats instead, can you help me with that?

What is your intention, are you trying to return all the chats of a particular user? If that's the case it would probably be something like this:

const baseArgs = {
  where: {
    chat_user: {
      some: {
        user_id: '<uuid>'
      }
    }
  }
}

const result = await findManyCursorConnection(
  (args) => client.chat.findMany({ ...args, ...baseArgs }),
  () => client.chat.count({ where: baseArgs.where }),
  { first: 5 }
)

See more here: https://www.prisma.io/docs/concepts/components/prisma-client/relation-queries#relation-filters

Note that you can also offer those every / some / none queries via your API if you offer them in whatever args comes from (we do that in our GraphQL API), but you have to take a bit extra care that users can not use these to filter related records that they might not have permissions to.

[felippepuhle]

@queicherius thanks for you quick reply!
Yup, plan would be returning all chats of a particular user.

For now I'm already using where.chat_user.some as you suggested and it works, but TBH we're currently using Hasura and I'm coming with a PoC on how we can get rid of that and have our own GQL server - trying to mimic a exact same query that we're already doing there. I remember in the past we were doing a subquery like that but at some point for performance issues we moved to querying directly into chat_user joining chat instead, which solved our problem.

As our chat_user table uses a composite PK instead (does not have an id column) I was trying to dig and find how I could do that. I even tried returning a connection of chat_user instead (so I could access chat from there) but no success so far.

If you think issue is invalid I can just close it, it's more like a question for now as it's just a PoC (I can keep using where.chat_user.some for now), if it becomes a real implementation problem and lib does not support that yet I can reopen the issue in the future and check if I can find what would be changes to put a PR.

Thanks so much!

[queicherius]

As our chat_user table uses a composite PK instead (does not have an id column) I was trying to dig and find how I could do that. I even tried returning a connection of chat_user instead (so I could access chat from there) but no success so far.

I am not sure if this is supported in the ORM part of Prisma, but you should be able to do it with queryRaw. If you need to then transform the query result you should be able to do basically whatever you want in the callbacks passed to findManyCursorConnection and via custom edges and nodes.

---

I will close the issue here, because it's more a topic of database design & how to use Prisma than with this library. Maybe you will find help in the Prisma discussions.

Feel free to re-open/PR in the future if this library becomes a limitation :)