BigBinary Blog

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 acheived 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

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

Adding the plugin

1const express = require("express");
2const { postgraphile } = require("postgraphile");
3const PgOrderByRelatedPlugin = require("@graphile-contrib/pg-order-by-related");
4
5const app = express();
6
7app.use(
8  postgraphile(process.env.DATABASE_URL, "public", {
9    appendPlugins: [PgOrderByRelatedPlugin],
10  })
11);

Using associated table column enum with orderBy argument

1query getPostsSortedByUserId {
2  posts: postsList(orderBy: AUTHOR_BY_USER_ID__NAME_ASC) {
3    id
4    title
5    description
6    author: authorByUserId {
7      id
8      name
9    }
10  }
11}

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

1import { makeAddPgTableOrderByPlugin, orderByAscDesc } from "graphile-utils";
2
3export default makeAddPgTableOrderByPlugin(
4  "public",
5  "post",
6  ({ pgSql: sql }) => {
7    const author = sql.identifier(Symbol("author"));
8    const address = sql.identifier(Symbol("address"));
9    return orderByAscDesc(
10      "AUTHOR_BY_USER_ID__ADDRESS_ID__COUNTRY__STATE__CITY",
11      ({ queryBuilder }) => sql.fragment`(
12            SELECT
13              CONCAT(
14                ${address}.city,
15                ', ',
16                ${address}.state,
17                ', ',
18                ${address}.country
19              ) AS full_address
20            FROM public.user as ${author}
21            JOIN public.address ${address} ON ${author}.address_id = ${address}.id
22            WHERE ${author}.id = ${queryBuilder.getTableAlias()}.user_id
23            ORDER BY ${address}.country DESC, ${address}.state DESC, ${address}.city DESC
24            LIMIT 1
25          )`
26    );
27  }
28);

Export all custom orderBy plugins

plugins/orderBy/index.js

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

Append custom orderBy plugins to postgraphile

1const express = require("express");
2const { postgraphile } = require("postgraphile");
3import * as OrderByPlugins from "./plugins/orderby";
4
5const app = express();
6
7app.use(
8  postgraphile(process.env.DATABASE_URL, "public", {
9    appendPlugins: [...Object.values(OrderByPlugins)],
10  })
11);

Using custom enum with orderBy argument

1query getPostsSortedByAddress {
2  posts: postsList(
3    orderBy: AUTHOR_BY_USER_ID__ADDRESS_ID__COUNTRY__STATE__CITY
4  ) {
5    id
6    title
7    description
8    author: authorByUserId {
9      id
10      name
11      address {
12        id
13        country
14        state
15        city
16      }
17    }
18  }
19}

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