Drizzle Team•14mo ago

Infinite scroll on Drizzle ORM not working

Please I haven't gotten the solution on how to solve the Cursor issue on Drizzle ORM. I was able to use a custom limit by using the Slice method on Javascript, however for the cursor scroll I still dont know how to add a cursor here:

      const selectedMessage = limitUserMessage.map((_message) => ({
        isUserMessage: _message.isUserMessage!,
        createdAt: _message.createdAt!,
        text: _message.text!,
        id: _message.id === cursor ? _message.id : undefined,
      }));

      const selectedMessage = limitUserMessage.map((_message) => ({
        isUserMessage: _message.isUserMessage!,
        createdAt: _message.createdAt!,
        text: _message.text!,
        id: _message.id === cursor ? _message.id : undefined,
      }));

If it were to be Prisma I'd have done it this way:

        cursor: cursor ? { id: cursor } : undefined,

        cursor: cursor ? { id: cursor } : undefined,

However, cursor and Pagination feel's strange on Drizzle ORM, here is full code below:

      const { userId } = ctx;
      const { fileId, cursor } = input;
      const limit = input.limit ?? INFINITE_QUERY_LIMIT;

      const userFile = await db
        .select()
        .from(_file)
        .where(eq(_file.userId, userId));

      const selectedFile = userFile.find((_file) => _file.id === fileId);

      if (!selectedFile) throw new TRPCError({ code: "NOT_FOUND" });

      const userMessage = await db
        .select()
        .from(_message)
        .where(eq(_message.fileId, fileId))
        .orderBy(desc(_message.createdAt));

      console.log("USER_MESSAGE", userMessage);

      function limitMessage(messages: DBMessage[]) {
        const messagesToLimit = limit + 1;
        const lastIndex = Math.max(messagesToLimit - messages.length, 0);
        return messages.slice(lastIndex);
      }

      const limitUserMessage = limitMessage(userMessage);

      const selectedMessage = limitUserMessage.map((_message) => ({
        isUserMessage: _message.isUserMessage!,
        createdAt: _message.createdAt!,
        text: _message.text!,
        id: _message.id === cursor ? _message.id : undefined,
      }));

      console.log("SELECTED_MESSAGES", selectedMessage);

      const { userId } = ctx;
      const { fileId, cursor } = input;
      const limit = input.limit ?? INFINITE_QUERY_LIMIT;

      const userFile = await db
        .select()
        .from(_file)
        .where(eq(_file.userId, userId));

      const selectedFile = userFile.find((_file) => _file.id === fileId);

      if (!selectedFile) throw new TRPCError({ code: "NOT_FOUND" });

      const userMessage = await db
        .select()
        .from(_message)
        .where(eq(_message.fileId, fileId))
        .orderBy(desc(_message.createdAt));

      console.log("USER_MESSAGE", userMessage);

      function limitMessage(messages: DBMessage[]) {
        const messagesToLimit = limit + 1;
        const lastIndex = Math.max(messagesToLimit - messages.length, 0);
        return messages.slice(lastIndex);
      }

      const limitUserMessage = limitMessage(userMessage);

      const selectedMessage = limitUserMessage.map((_message) => ({
        isUserMessage: _message.isUserMessage!,
        createdAt: _message.createdAt!,
        text: _message.text!,
        id: _message.id === cursor ? _message.id : undefined,
      }));

      console.log("SELECTED_MESSAGES", selectedMessage);

3 Replies

Luxaritas•14mo ago

First off, you don’t want to use slice to apply the limit - that still means everything is returned from your DB, which you don’t want The way to approach this is to have a where condition like gt(_message.id, cursor) and then use .limit on the query builder Or well, that would give you the cursor if you sort by increasing ID If you’re supporting other sorts, you’d either need to construct different where clauses or you need to count how many items are before your last ID in your ordered query and then filter on that - you should be able to do that with a subquery If you want to see how Prisma does it, you could try running the relevant Prisma query with sql query logging enabled

VictorTimiOP•14mo ago

Thank you very much sir Good evening @Luxaritas , I am very sorry to disturb you sir. I tried following your instruction yesterday however it went reloading the same data that was on the page instead of fetching a new 10 data, please help me see what I am doing wrong. Thanks so much! Here is the redefined code:

getFileMessages: privateProcedure
    .input(
      z.object({
        limit: z.number().min(1).max(100).nullish(),
        cursor: z.string().nullish(),
        fileId: z.string(),
      })
    )
    .query(async ({ ctx, input }) => {
      const { userId } = ctx;
      const { fileId, cursor } = input;
      const limit = input.limit ?? INFINITE_QUERY_LIMIT;

      const userFile = await db
        .select()
        .from(_file)
        .where(eq(_file.userId, userId));

      const selectedFile = userFile.find((_file) => _file.id === fileId);

      if (!selectedFile) throw new TRPCError({ code: "NOT_FOUND" });

      const userMessage = await db
        .select()
        .from(_message)
        .where(or(eq(_message.fileId, fileId), gt(_message.id, cursor!)))
        .limit(limit + 1)
        .orderBy(desc(_message.createdAt));

      console.log("USER_MESSAGE", userMessage);

      const selectedMessage = userMessage.map((_message) => ({
        isUserMessage: _message.isUserMessage!,
        createdAt: _message.createdAt!,
        text: _message.text!,
        id: _message.id!,
      }));

      console.log("SELECTED_MESSAGES", selectedMessage);

      let nextCursor: typeof cursor | undefined = undefined;
      if (selectedMessage.length > limit) {
        const nextItem = selectedMessage.pop();
        nextCursor = nextItem?.id;
      }
      return {
        selectedMessage,
        nextCursor,
      };
    }),

getFileMessages: privateProcedure
    .input(
      z.object({
        limit: z.number().min(1).max(100).nullish(),
        cursor: z.string().nullish(),
        fileId: z.string(),
      })
    )
    .query(async ({ ctx, input }) => {
      const { userId } = ctx;
      const { fileId, cursor } = input;
      const limit = input.limit ?? INFINITE_QUERY_LIMIT;

      const userFile = await db
        .select()
        .from(_file)
        .where(eq(_file.userId, userId));

      const selectedFile = userFile.find((_file) => _file.id === fileId);

      if (!selectedFile) throw new TRPCError({ code: "NOT_FOUND" });

      const userMessage = await db
        .select()
        .from(_message)
        .where(or(eq(_message.fileId, fileId), gt(_message.id, cursor!)))
        .limit(limit + 1)
        .orderBy(desc(_message.createdAt));

      console.log("USER_MESSAGE", userMessage);

      const selectedMessage = userMessage.map((_message) => ({
        isUserMessage: _message.isUserMessage!,
        createdAt: _message.createdAt!,
        text: _message.text!,
        id: _message.id!,
      }));

      console.log("SELECTED_MESSAGES", selectedMessage);

      let nextCursor: typeof cursor | undefined = undefined;
      if (selectedMessage.length > limit) {
        const nextItem = selectedMessage.pop();
        nextCursor = nextItem?.id;
      }
      return {
        selectedMessage,
        nextCursor,
      };
    }),

Luxaritas•14mo ago

Maybe it's because you're ordering by createdAt, but filtering by finding items with IDs greater than your cursor? Your ordering and cusror filter should be the same Though now that I'm looking at it even if that wasn't an issue, your filter is probably backwards - the first page you load should be the newest, but the IDs you're requesting are newer than your current page If ID ordering and created ordering are the same, that means you eg return items 20 through 10, then request IDs greater than 10... which is still 20 through 10

Gaming

Programming

Infinite scroll on Drizzle ORM not working