We write about Ruby on Rails, React.js, React Native, remote work, open source, engineering and design.
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.
pg-order-by-related
pluginpg-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.
1npm i @graphile-contrib/pg-order-by-related
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);
orderBy
argument1query 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
.
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);
orderBy
pluginsplugins/orderBy/index.js
1export { default as orderByPostAuthorAddress } from "./orderByPostAuthorAddress";
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);
orderBy
argument1query 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.