relay-style cursor-based pagination capable of filtering/sorting for SQL

gistfile1.js

import { Base64 } from 'js-base64'
import { Op } from 'sequelize'
import { fromGlobalId } from 'graphql-relay'

// https://github.com/graphql/graphql-relay-js/issues/94#issuecomment-232410564

const effectiveOrder = ({ last }, orderBy) => {
    /* adds `id ASC` to end of `ORDER BY` if `id` is not already in the `ORDER BY` clause
    flips `ASC` to `DESC` (and vice-versa) if pagination arg `last` is defined
    */
    const order = orderBy
        .map(([column, _]) => column)
        .includes('id')
        ? orderBy
        : [...orderBy, ['id', 'ASC']]

    if (last) {
        return { order: flipOrder(order), flip: true }
    }
    return { order, flip: false}
}

/*
cursors are a list of values corresponding to that nodes data values
the list is ordered to correspond with the SQL sort order

e.g.

record = {id: 7, a: 1, b: 2, c: 'three'}
order = [['c', 'ASC'], ['b', 'DESC'], ['id', 'ASC']]

=>

decodedCursor = ['three', 2, 7]
cursor = encodeCursor(decodedCursor)

useful because we can decode the cursor to get conditions for our WHERE clause! (ie. in `orderByCursor` below)
*/

const encodeCursor = cursor => {
    return Base64.encode(JSON.stringify(cursor))
}

const decodeCursor = cursor => {
    return JSON.parse(Base64.decode(cursor))
}

const comparisonOperator = sortDirection => sortDirection == 'ASC' ? Op.gt : Op.lt

const orderByCursor = (order, cursor) => {
    /*
    e.g. 
    
    decodedCursor = ['three', 2, 7]
    order = [['c', 'ASC'], ['b', 'DESC'], ['id', 'ASC']]
    
    =>
    
    WHERE c > 'three' OR (c = 'three' AND b < 2) OR (c = 'three' AND b = 2 AND id > 7)
    */
    const decodedCursor = decodeCursor(cursor)

    const validOrderings = order.map(([column, sortDirection], i) => {
        const allOthersEqual = order
            .slice(0, i)
            .map((other, j) => (
                { [other]: decodedCursor[j] }
            ))

        return Object.assign(
            {
                [column]: { [comparisonOperator(sortDirection)]: decodedCursor[i] },
            },
            ...allOthersEqual
        )
    })

    return {[Op.or]: validOrderings}
}

const flipSortDirection = sortDirection => sortDirection == 'ASC' ? 'DESC' : 'ASC'
const flipOrder = order => order.map(([column, sortDirection]) => (
    [column, flipSortDirection(sortDirection)]
))

const valuesForColumns = (columns, row) => {
    return columns.map(column => row.dataValues[column])
}

const pageInfo = ({ order, results, hasMoreResults, flip }) => {

    const columns = order.map(([column, _]) => column)
    const startCursor = encodeCursor(valuesForColumns(columns, results[0]))
    const endCursor = encodeCursor(valuesForColumns(columns, results.slice(-1)[0]))

    return {
        // TODO reference spec for setting `false`
        hasPreviousPage: flip ? hasMoreResults : false,
        hasNextPage: flip ? false : hasMoreResults,
        startCursor,
        endCursor,
    }
}

const edge = ({result, order}) => {
    const columns = order.map(([column, _]) => column)
    const cursor = encodeCursor(valuesForColumns(columns, result))
    return {
        cursor,
        node: result,
    }
}

export const paginate = (model, { first, last, after, before }, filters=[], orderBy=[]) => {
    const { order, flip } = effectiveOrder({ first, last, after, before }, orderBy)
    // TODO check if Relay supports default limit on client-side
    const limit = (flip ? last : first) || 10

    const cursor = flip ? before : after
    const where = cursor ? orderByCursor(order, cursor) : {}

    return model
        .findAll({
            // TODO combine filters and pagination clauses with AND
            where,
            order,
            limit: limit + 1, // +1 to peek to see if there is more data
        })
        .then(queryResults => {
            const hasMoreResults = queryResults.length == limit + 1
            let results = hasMoreResults ? queryResults.slice(0, -1) : queryResults
            results = flip ? results.reverse() : results
            return {
                pageInfo: pageInfo({results, hasMoreResults, order, flip}),
                edges: results.map(result => edge({result, order})), // TODO to edges
            }
        })
}

use as follows:

import {
    GraphQLObjectType,
} from 'graphql'
import {
    connectionArgs,
    connectionDefinitions,
} from 'graphql-relay'

// helper
const connectionType = nodeType => connectionDefinitions({nodeType}).connectionType

const Query = new GraphQLObjectType({
    name: 'Query',
    fields: () => ({
        // ... other queries here
        things: {
            type: connectionType(Thing),
            args: connectionArgs,
            resolve: (_, paginationArgs) => {
                return helpers.paginate(models.Thing, paginationArgs, {orderBy: [['name', 'ASC']]})
            }
        },
    })
})

nice gist

check out approach here https://github.com/entria/graphql-mongoose-loader

Very nice! @pcattori

Does it accept customArgs? e.g
{...connectionArgs, otherCustomArgs }

pageInfo.hasPreviousPage looks wrong - it should be possible to have previous and next page as true