---
title: "Sort query data on associated table in PostGraphile"
description: "How to sort query data on associated tables in PostGraphile."
canonical_url: "https://www.bigbinary.com/blog/sort-query-data-on-associated-tables-in-postgraphile-using-order-by-plugin"
markdown_url: "https://www.bigbinary.com/blog/sort-query-data-on-associated-tables-in-postgraphile-using-order-by-plugin.md"
---

# Sort query data on associated table in PostGraphile

How to sort query data on associated tables in PostGraphile.

- Author: Taha Husain
- Published: January 19, 2021
- Categories: Misc

[PostGraphile](https://www.graphile.org/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](https://github.com/graphile-contrib/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

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

#### Adding the plugin

```javascript
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

```graphql
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](https://www.graphile.org/postgraphile/make-add-pg-table-order-by-plugin/)
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`

```javascript
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`

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

#### Append custom `orderBy` plugins to `postgraphile`

```javascript
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

```graphql
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](https://github.com/graphile-contrib/pg-order-by-related)
and
[makeAddPgTableOrderByPlugin](https://www.graphile.org/postgraphile/make-add-pg-table-order-by-plugin/)
pages for detailed documentation.

## Links

- [Human page](https://www.bigbinary.com/blog/sort-query-data-on-associated-tables-in-postgraphile-using-order-by-plugin)