Add cursor pagination

[rubenbase]

We want to pick adonisjs as the node.js framework for a project but the project will need cursor pagination support and AFAIK Lucid doesn't support it. Reopening the cursor pagination discussion because other frameworks support it and it became quite common nowadays.

[thetutlage]

Hey @rubenbase

Would you like to open a discussion for it and lead the implementation with a PR? I think cursor pagination has many moving parts, so it will be nice to start with the initial set of features we want and then starting the implementation

[epenflow]

This is an example of cursor pagination based on relay spec :

import { LucidRow } from '@adonisjs/lucid/types/model'

export interface CursorConnectionContract<T> {
  edges: {
    node: T
    cursor: string
  }[]
  pageInfo: {
    hasNextPage: boolean
    hasPrevPage: boolean
    startCursor: string | null
    endCursor: string | null
  }
}

export interface CursorContractProps {
  cursorColumn?: string
  sortColumn: string
  sortDirection: 'asc' | 'desc'
  first?: number | null
  after?: string | null
  last?: number | null
  before?: string | null
}

declare module '@adonisjs/lucid/types/model' {
  interface ModelQueryBuilderContract<Model extends LucidModel, Result = InstanceType<Model>> {
    cursor(props: CursorContractProps): Promise<CursorConnectionContract<Result>>
  }
}
declare module '@adonisjs/lucid/orm' {
  interface ModelQueryBuilder {
    cursor(props: CursorContractProps): Promise<CursorConnectionContract<LucidRow>>
  }
}


import { Exception } from '@adonisjs/core/exceptions'
import stringHelpers from '@adonisjs/core/helpers/string'
import { ModelQueryBuilder } from '@adonisjs/lucid/orm'
import { LucidRow } from '@adonisjs/lucid/types/model'
import { CursorContractProps } from '../../contracts/lucid.js'

export const encodeCursor = (id: string | number, sortValue: unknown): string => {
  const cursorData = JSON.stringify([id, sortValue])
  return Buffer.from(cursorData).toString('base64')
}
export const decodeCursor = (cursorData: string): [string | number, unknown] => {
  try {
    const decode = Buffer.from(cursorData, 'base64').toString('utf-8')
    const [id, sortValue] = JSON.parse(decode)
    return [id, sortValue]
  } catch (error) {
    throw new Exception('Invalid cursor provided.', { status: 400 })
  }
}

ModelQueryBuilder.macro(
  'cursor',
  async function cursor(this: ModelQueryBuilder, props: CursorContractProps) {
    const sortColumn = stringHelpers.camelCase(props.sortColumn)
    const primarySort = props.sortDirection
    const reverseSort = primarySort === 'asc' ? 'desc' : 'asc'
    const primaryKey = props.cursorColumn
      ? stringHelpers.camelCase(props.cursorColumn)
      : this.model.primaryKey

    const { first, after, last, before } = props
    const limit = first || last || 10

    if ((first !== null && first !== undefined) || after) {
      if (after) {
        const [id, sortValue] = decodeCursor(after)
        const operator = primarySort === 'asc' ? '>' : '<'

        this.where(sortColumn, operator, sortValue).orWhere((query: ModelQueryBuilder) => {
          query.where(sortColumn, '=', sortValue).where(primaryKey, operator, id)
        })
      }
      this.orderBy(sortColumn, primarySort).orderBy(primaryKey, primarySort)
      this.limit(limit + 1)

      return this.exec().then((results: LucidRow[]) => {
        const hasNextPage = results.length > limit
        const hasPrevPage = !!after
        const data = hasNextPage ? results.slice(0, -1) : results
        const firstEdge = data[0]
        const lastEdge = data[data.length - 1]

        const edges = data.map((node) => ({
          node,
          cursor: encodeCursor((node as any)[primaryKey], (node as any)[sortColumn]),
        }))
        const pageInfo = {
          hasNextPage,
          hasPrevPage,
          startCursor: firstEdge
            ? encodeCursor((firstEdge as any)[primaryKey], (firstEdge as any)[sortColumn])
            : null,
          endCursor: lastEdge
            ? encodeCursor((lastEdge as any)[primaryKey], (lastEdge as any)[sortColumn])
            : null,
        }

        return {
          edges,
          pageInfo,
        }
      })
    }

    if ((last !== null && last !== undefined) || before) {
      if (before) {
        const [id, sortValue] = decodeCursor(before)
        const operator = reverseSort === 'asc' ? '>' : '<'

        this.where(sortColumn, operator, sortValue).orWhere((query: ModelQueryBuilder) => {
          query.where(sortColumn, '=', sortValue).where(primaryKey, operator, id)
        })
      }
      this.orderBy(sortColumn, reverseSort).orderBy(primaryKey, reverseSort)
      this.limit(limit + 1)

      return await this.exec().then((results: LucidRow[]) => {
        const hasPrevPage = results.length > limit
        const hasNextPage = !!before
        const data = hasPrevPage ? results.slice(0, -1) : results

        data.reverse()
        const firstEdge = data[0]
        const lastEdge = data[data.length - 1]

        const edges = data.map((node) => ({
          node,
          cursor: encodeCursor((node as any)[primaryKey], (node as any)[sortColumn]),
        }))
        const pageInfo = {
          hasNextPage,
          hasPrevPage,
          startCursor: firstEdge
            ? encodeCursor((firstEdge as any)[primaryKey], (firstEdge as any)[sortColumn])
            : null,
          endCursor: lastEdge
            ? encodeCursor((lastEdge as any)[primaryKey], (lastEdge as any)[sortColumn])
            : null,
        }

        return {
          edges,
          pageInfo,
        }
      })
    }

    return {
      edges: [],
      pageInfo: {
        hasNextPage: false,
        hasPrevPage: false,
        startCursor: null,
        endCursor: null,
      },
    }
  }
)

  async index({ request }: HttpContext) {
    const search = await vine
      .compile(
        vine.object({
          first: vine.number().nullable().optional(),
          after: vine.string().nullable().optional(),
          last: vine.number().nullable().optional(),
          before: vine.string().nullable().optional(),
        })
      )
      .validate(request.qs())
    console.log(search)
    const users = User.query().cursor({
      sortColumn: 'created_at',
      sortDirection: 'desc',
      ...search,
    })
    return users
  }