How to implement cursor-based infinite scrolling with prisma + useInfinitequery

How to implement cursor based infinite scrolling in this scenario? Imagine I have this schema:

model MockUser {
  id         String   @id @default(cuid())
  name       String?
  hourlyRate Decimal?
  rating     Decimal?
}

model MockUser {
  id         String   @id @default(cuid())
  name       String?
  hourlyRate Decimal?
  rating     Decimal?
}

I want users to have random ID's for other purposes. Cursor based infinite scrolling requires a sequential and unique id of some sort in table. What is the best way to go about this? It feels a bit weird to add an extra column for that, but I seen no other way. For example:

model MockUser {
  id         String   @id @default(cuid())
  name       String?
  hourlyRate Decimal?
  rating     Decimal?
  myCursor   Int    @id @default(autoincrement())
}

model MockUser {
  id         String   @id @default(cuid())
  name       String?
  hourlyRate Decimal?
  rating     Decimal?
  myCursor   Int    @id @default(autoincrement())
}

Only one id can be used though, so what should I do here? Possible solution?

model MockData {
  id           String        @id @default(cuid())
  name         String?
  hourlyRate   Decimal?
  rating       Decimal?
  MockDataList MockDataList?
}

model MockDataList {
  id         Int      @id @default(autoincrement())
  mockDataId String   @unique
  mockData   MockData @relation(fields: [mockDataId], references: [id])

  @@index([mockDataId])
}

model MockData {
  id           String        @id @default(cuid())
  name         String?
  hourlyRate   Decimal?
  rating       Decimal?
  MockDataList MockDataList?
}

model MockDataList {
  id         Int      @id @default(autoincrement())
  mockDataId String   @unique
  mockData   MockData @relation(fields: [mockDataId], references: [id])

  @@index([mockDataId])
}

35 Replies

cje•3y ago

Read the tRPC useinfinotequery docs

amanuelOP•3y ago

I already read this: https://trpc.io/docs/useInfiniteQuery

useInfiniteQuery | tRPC

- Your procedure needs to accept a cursor input of any type

amanuelOP•3y ago

And this: https://www.prisma.io/docs/concepts/components/prisma-client/pagination#cursor-based-pagination

Prisma

Pagination (Reference)

Prisma Client supports both offset pagination and cursor-based pagination. Learn more about the pros and cons of different pagination approaches and how to implement them.

amanuelOP•3y ago

And I searched a lot, but I think the last schema I arrived at is sufficient for now, until someone suggests a better strategy

cje•3y ago

The tRPC page you linked shows how to do it without a cursor field

amanuelOP•3y ago

No, the schema needs a cursor field, how else will you reference the next section, hahahaha

amanuelOP•3y ago

amanuelOP•3y ago

In this scenario they use {myCursor: cursor}

cje•3y ago

Read the code snippet again and look at where they get the cursor from

amanuelOP•3y ago

~~You clearly don't understand how cursor based infinite scrolling works hahaha~~ What do you think the cursor does in prisma? It needs to reference an autoincremented unique primary key

cje•3y ago

myCursor is just an arbitrary field like id

amanuelOP•3y ago

Even in prisma docs it says:

cje•3y ago

guess what field meets those requirements Id

amanuelOP•3y ago

Yes, but I don't want my id to be sequential That's the whole point

cje•3y ago

It works with uuid also

amanuelOP•3y ago

No it doesn't Let's say you have a bunch of random ID's, how would it reference the next section? They need to be sequential for that purpose That's why I did this:

model MockData {
  id           String        @id @default(cuid())
  name         String?
  hourlyRate   Decimal?
  rating       Decimal?
  MockDataList MockDataList?
}

model MockDataList {
  id         Int      @id @default(autoincrement())
  mockDataId String   @unique
  mockData   MockData @relation(fields: [mockDataId], references: [id])

  @@index([mockDataId])
}

model MockData {
  id           String        @id @default(cuid())
  name         String?
  hourlyRate   Decimal?
  rating       Decimal?
  MockDataList MockDataList?
}

model MockDataList {
  id         Int      @id @default(autoincrement())
  mockDataId String   @unique
  mockData   MockData @relation(fields: [mockDataId], references: [id])

  @@index([mockDataId])
}

That's the closest I got to make this as simple as possible and work I'm just concerned about having an extra table if it is not necessary

cje•3y ago

https://github.com/c-ehrlich/trpc-rq-features-deepdive/blob/main/src/server/trpc/router/post.ts

GitHub

trpc-rq-features-deepdive/post.ts at main · c-ehrlich/trpc-rq-featu...

Contribute to c-ehrlich/trpc-rq-features-deepdive development by creating an account on GitHub.

cje•3y ago

Explain to me why this works Even though it’s using cuid Also try being a bit less rude to people who are trying to help you in the future lol

amanuelOP•3y ago

I'm really sorry, I'm just trying to get to the bottom of this and I've spent all day, I'm just frustrated at myself Thanks for this, I'll try to implement and see if I get expected results @cje Thank you so much, this worked, but I'm still mind boggled as to how this works under the hood, do you have any resources that I can read and learn about this?

cje•3y ago

the cursor itself doesn't need to be sequential like sequential id or whatever because the results won't necessarily be in that order either

amanuelOP•3y ago

Hmm, holy shit this is difficult to understand for me

cje•3y ago

like if you sort tweets by like count, then the ID is not in order

amanuelOP•3y ago

Ok so if the cursor is at 0 initially right Pointing at the first row Then how does it reference the 10th row from there?

cje•3y ago

what the trpc implementation does is if you want 10 items per set it grabs 11 pops the 11th item from which you get the id as cursor and then next time grabs the subset that starts with that item

amanuelOP•3y ago

So it just climbs down linearly whatever result you get from prisma?

cje•3y ago

from what i understand yes

amanuelOP•3y ago

No matter what the results are? Oh ok I see now

cje•3y ago

and it's actually not all that efficient with REALLY big result sets

amanuelOP•3y ago

That's what I was going to say

cje•3y ago

but SQL is fast so usually its ok

amanuelOP•3y ago

So how does social media sites do it then? They have to somehow query a small subset of data only, right? Like query in chunks, then infinite scroll that chunk, and if more is requested, then get another chunk Well, I'm super happy now because it made everything so much easier for me

cje•3y ago

cje•3y ago

something like this idk

amanuelOP•3y ago

Interesting Do you have a link to your site btw? I'm very interested to see it and interact with it

cje•3y ago

its just a repo that i made for a tutorial i never filmed you can probably just pnpm i => pnpm dev need a discord key etc theres no readme but i think with an env file it should just work

Gaming

Programming

How to implement cursor-based infinite scrolling with prisma + useInfinitequery