Postgraphile order by result of calculation (with input)

Question

Some context:

My table has two columns, longitude and latitude. I want to be able to create a query that can receive an input of a longitude, latitude and a number that represents the maximum number of kilometers distance.

The query will return the rows that are within that distance ordered by the resulting distance.

I have seen few options for custom conditions, but that might be pretty hacky... I thought maybe there is a way to do it and use the build in order by that PostGraphile generates, but the I'm if there is a way to pass variable to the order by.

The question:

What is the best to do it in PostGraphile?

Thanks in advance!

Answer 1

To do this, step 1 is to build your filter object with your x, y and radius:

const { makePluginByCombiningPlugins, makeAddPgTableConditionPlugin } = require('graphile-utils');

const PositionFilterObjectPlugin = builder => {
  builder.hook('build', build => {
    const nonNullFloat = new build.graphql.GraphQLNonNull(build.graphql.GraphQLFloat);
    const PositionFilter = new build.graphql.GraphQLInputObjectType({
      name: 'PositionFilter',
      fields: {
        x: {
          type: nonNullFloat,
        },
        y: {
          type: nonNullFloat,
        },
        radius: {
          type: nonNullFloat,
        },
      },
    });
    build.addType(PositionFilter);

    return build;
  });
};

/*
   Due to a plugin ordering issue that cannot be addressed in V4, we can't
   just use makeExtendSchemaPlugin to add this type; we have to write it
   out by hand in the build hook.

   input PositionFilter {
     x: Float!
     y: Float!
     radius: Float!
   }
*/

(build.graphql is an instance of the graphql module, the reference GraphQL implementation, you can read more about it here and here.)

Step 2 is to add a condition that also does ordering:

const PositionConditionPlugin = makeAddPgTableConditionPlugin(
  'public',
  'entities',
  'closeTo',
  build => ({
    type: build.getTypeByName('PositionFilter'),
  }),
  (value, { queryBuilder, sql, sqlTableAlias }) => {
    if (value == null) {
      return;
    }

    const { x, y, radius } = value;
    const valX = sql.value(x);
    const valY = sql.value(x);
    const valR = sql.value(radius);

    const distance = sql.fragment`(((${sqlTableAlias}.x - ${valX}) ^ 2 + (${sqlTableAlias}.y - ${valY}) ^ 2) ^ 0.5)`;

    // Order the result set by the proximity of the entity to the given point
    queryBuilder.orderBy(
      distance,
      true, // Ascending
      false,
    );

    // Filter to only entities within the given radius
    return sql.fragment`${distance} < ${valR}`;
  },
);

Finally you either load both these plugins or if you prefer you can make a single plugin that encompasses both:

module.exports = makePluginByCombiningPlugins(PositionFilterObjectPlugin, PositionConditionPlugin);

I've added an example with ordering to the docs:

https://www.graphile.org/postgraphile/make-add-pg-table-condition-plugin/#example-with-ordering

NOTE: before graphile-utils 4.9.1 is released you'll need to add this plugin via --prepend-plugins (or prependPlugins for library users) because otherwise there's a plugin ordering issue that means that orders added by makeAddPgTableConditionPlugin come after the default orders.