Sort query data on associated table in PostGraphile

PostGraphile provides sorting on all columns of a table in a GraqhQL query by default with orderBy argument.

Although, sorting based on associated table’s columns or adding a custom sort can be achieved via plugins. In this blog we will explore two such plugins.

Using pg-order-by-related plugin

pg-order-by-related plugin allows us to sort query result based on associated table's columns. It does that by adding enums for all associated table's columns. Here's what we need to do to use this plugin.

Installation

npm i @graphile-contrib/pg-order-by-related

Adding the plugin

const express = require("express");
const { postgraphile } = require("postgraphile");
const PgOrderByRelatedPlugin = require("@graphile-contrib/pg-order-by-related");

const app = express();

app.use(
  postgraphile(process.env.DATABASE_URL, "public", {
    appendPlugins: [PgOrderByRelatedPlugin],
  })
);

Using associated table column enum with orderBy argument

query getPostsSortedByUserId {
  posts: postsList(orderBy: AUTHOR_BY_USER_ID__NAME_ASC) {
    id
    title
    description
    author: authorByUserId {
      id
      name
    }
  }
}

pg-order-by-related plugin is useful only when we want to sort data based on first level association. If we want to apply orderBy on second level table columns or so, we have to use makeAddPgTableOrderByPlugin.

Using makeAddPgTableOrderByPlugin

makeAddPgTableOrderByPlugin allows us to add custom enums that are accessible on specified table's orderBy argument. We can write our custom select queries using this plugin.

We will use a complex example to understand the use-case of custom orderBy enum.

In our posts list query, we want posts to be sorted by author's address. Address has country, state and city columns. We want list to be sorted by country, state and city in the same order.

Here's how we can achieve this using makeAddPgTableOrderByPlugin.

plugins/orderBy/orderByPostAuthorAddress.js

import { makeAddPgTableOrderByPlugin, orderByAscDesc } from "graphile-utils";

export default makeAddPgTableOrderByPlugin(
  "public",
  "post",
  ({ pgSql: sql }) => {
    const author = sql.identifier(Symbol("author"));
    const address = sql.identifier(Symbol("address"));
    return orderByAscDesc(
      "AUTHOR_BY_USER_ID__ADDRESS_ID__COUNTRY__STATE__CITY",
      ({ queryBuilder }) => sql.fragment`(
            SELECT
              CONCAT(
                ${address}.city,
                ', ',
                ${address}.state,
                ', ',
                ${address}.country
              ) AS full_address
            FROM public.user as ${author}
            JOIN public.address ${address} ON ${author}.address_id = ${address}.id
            WHERE ${author}.id = ${queryBuilder.getTableAlias()}.user_id
            ORDER BY ${address}.country DESC, ${address}.state DESC, ${address}.city DESC
            LIMIT 1
          )`
    );
  }
);

Export all custom orderBy plugins

plugins/orderBy/index.js

export { default as orderByPostAuthorAddress } from "./orderByPostAuthorAddress";

Append custom orderBy plugins to postgraphile

const express = require("express");
const { postgraphile } = require("postgraphile");
import * as OrderByPlugins from "./plugins/orderby";

const app = express();

app.use(
  postgraphile(process.env.DATABASE_URL, "public", {
    appendPlugins: [...Object.values(OrderByPlugins)],
  })
);

Using custom enum with orderBy argument

query getPostsSortedByAddress {
  posts: postsList(
    orderBy: AUTHOR_BY_USER_ID__ADDRESS_ID__COUNTRY__STATE__CITY
  ) {
    id
    title
    description
    author: authorByUserId {
      id
      name
      address {
        id
        country
        state
        city
      }
    }
  }
}

Please head to pg-order-by-related and makeAddPgTableOrderByPlugin pages for detailed documentation.