Postgraphile: Expose related fields as orderBy parameters

[ivosabev]

Since postgraphql is using the foreign keys to establish the relationships automatically. I think it would be a good idea to generate sort fields based on the related tables.

For example:

{
  userNodes(orderBy: TEAM_NAME) {
    Users: nodes {
      id,
      firstName,
      Team: teamByTeamId {
        name
      }
    }
  }
}

[calebmer]

This is an interesting idea, could you elaborate?

Another thing I think @langpavel has mentioned is being able to order against custom indexes as well.

[ivosabev]

Well I haven't read all the source code in detail, but the same way foreign keys are retrieved from the Postgres schema to provide the teamByTeamId relations inside the userNodes, they can be used to generate enums for the UserOrdering type.

So if your team table has columns id, name, city, then it would add those enums and you will have them available as TEAM_NAME, TEAM_CITY inside the UserOrdering just as you have now FIRST_NAME there.

I think the easiest way would be for the resolve function to do a simple left outer join if such relational ordering is requested. So the upper GQL query would result in the following SQL code:

SELECT U.*
FROM "user" AS U LEFT OUTER JOIN team AS T ON U."teamId" = T.id
ORDER BY T.name ASC

[calebmer]

Yep, makes sense. My worry is the potential performance impact. Currently for relations (like teamByTeamId) we send another SQL query instead of adding a join to the original SQL query (something I’d like to change in the future). Therefore a query that adds an implicit join and an order by of this nature could be repeating work (for now, until we add joins to relation queries).

Also its worth asking how performant is an order by like this on its own? Would we just be adding a perf footgun? I’m not sure if you can index this approach, it may be worth just denormalizing this data if its super important to order by.

Is this a problem that can be solved by a custom index? I definitely think it would be best to support sorting by custom indexes vs columns or related columns.

[calebmer]

@lfittl is there a good way to order using a joined row? (See the above query) Or maybe a way to generate an index using a joined row.

[lfittl]

@calebmer Not sure I understand the question correctly, but a few thoughts:

1) AFAIK, indexes are only relevant for ordering when you're applying LIMIT - in that particular case, if the index is on the value you're ORDERing by it can save a bunch of work on the database. Without a LIMIT (as it seems to be in the example query) you can basically order by anything, since it'll just be applied as the last operation

2) I'm still somewhat conflicted on the whole topic of generating JOINs, vs generating IN(...) lists or similar and doing two queries - in general JOINs would be better for the database - even if you're running a very similar query again later, the results would likely still be in shared memory and could be retrieved quickly. However, for readability's sake, I often find simple queries without JOINs nicer to read and optimize - especially if we're talking more than one level deep.

Overall, it might be worth trying this out, and checking the EXPLAIN ANALYZE output for both approaches (i.e. once with a join, once without). If at all possible using an actual data set. Thats the only way to know for sure.

[ivosabev]

I do not think it is possible to beat the performance of raw SQL JOIN with well placed indexes both on the FK and the sorting column, but the IN(...) might be the better and cleaner approach if there is a cache that the resolver hits to reduce the amount of fetched data in the IN(...) select. Then of course you have to do the sorting on the data structure in JS without index optimization on the column that would be used for sorting, which won't be an issue for small datasets, but might reduce performance and increase Node memory footprint significantly.

It might be worth reaching out to the GraphQL community for a larger discussion as this is not specific to Postgres, but more of a general GraphQL/Relay architecture question.

[calebmer]

I definitely agree with you that this would be a useful/awesome feature to have, I’m more concerned about perf/footgun implications on the actual implementation side :blush:

[ivosabev]

I brought the issue with the graphql-relay-js team and conclusion is that it is not feasible to implement this with IN(...) and JS sorting.

Imagine your table having 10,000 or 100,000 rows. Selecting all that information in two queries and then sorting them in JS is an overkill, while in Postgres with indexes it will be a matter of milliseconds.

So I think the only course of action is to create an ENUM that would trigger a custom SQL with JOINS being generated and then passing back the result as normally. I need this for my project so I will work on it in the next couple of weeks and send you my solution.

[calebmer]

Another option is use a procedure which returns a table. Then all we need to do is implement natural ordering which we can do using offsets vs column values.

I would prefer this option.

[ivosabev]

Can you elaborate?

[calebmer]

You can write a procedure that returns a GraphQL connection like so:

create function my_function() returns setof person as $$
  select * from person order by some.complicated.thing desc;
$$ language sql stable;

This would pretty much solve your use case except that PostGraphQL currently doesn’t allow natural ordering, so you couldn’t do this:

select * from my_function();

PostGraphQL needs to control order by so it can generate cursors. So if you order by person.name for instance PostGraphQL would use the person.name column for GraphQL pagination cursors.

So that means PostGraphQL currently doesn’t support natural ordering (ordering without an order by clause), however supporting such a case should be easy, just use offsets as the cursor. Most connection implementations today use the query offset as the cursor, this isn’t the _best_ solution as offsets change frequently as you insert new rows. However there’s no reason we can’t support natural ordering with offsets, and a more correct implementation where we control order by.

If this was a confusing explanation, see how we implement connection resolution and read this excellent overview of pagination with GraphQL by the Apollo team which is a good overview of the strategy PostGraphQL implements. To see an implementation of offset based cursor pagination, see the graphql-relay-js connection example implementation which uses an array.

[calebmer]

I’m going to close this for now as you can use a procedure to define your own custom orderings if the simple column ascending/descending ordering isn’t enough for you :+1:

If there’s enough interest from other community members and/or a community PR I’ll reprioritize 😊

[JKing-B16]

It would be very nice if there was an ORDERBYS that supported an ARRAY, because sometimes I need to sort on multiple columns.

[benjie]

This is already supported in the core query builder in v4; we just don't have anything exposing it to GraphQL yet. If you wanted to look at implementing it,

this plugin adds the 'orderBy' argument to connections:

https://github.com/graphile/graphile-build/blob/master/packages/graphile-build-pg/src/plugins/PgConnectionArgOrderBy.js

this plugin adds the primary key orderBy value to the enum:

https://github.com/graphile/graphile-build/blob/master/packages/graphile-build-pg/src/plugins/PgOrderByPrimaryKeyPlugin.js

and this plugin adds all the other columns to the orderBy enum:

https://github.com/graphile/graphile-build/blob/master/packages/graphile-build-pg/src/plugins/PgOrderAllColumnsPlugin.js

---

I would do it by adding a new plugin that adds an orderByMany argument by copying the second hook in PgConnectionArgOrderBy:

https://github.com/graphile/graphile-build/blob/062239f3746bc23bb77879b388e8942ce2fe82b6/packages/graphile-build-pg/src/plugins/PgConnectionArgOrderBy.js#L48-L105

Then changing the type of the field from

          type: TableOrderByType,

to

          type: new GraphQLList(new GraphQLNonNull(TableOrderByType)),

and then adjusting the addArgDataGenerator so that the pgCursorPrefix is constructed from all of the aliases joined together, unique is true if any of the orderByMany's have unique true, and the orders are made by concatenating all the individual orderBy's that make up the orderByMany argument.

Also throw an error if both orderByMany and orderBy are specified.

[svendpeter]

Hi ,

I am trying to use postgraphql with an Angular grid , and this forces me to do limit/offset (first/offset) pagination , without using a cursor . Is it possible with postgraphql ? Because I have seen tutorials only using cursor based pagination . Can you kindly point me to a good tutorial or explain to me how I might do first/offset pagination with postgraphql ?
Thank you..

[benjie]

Same way, just use “first” for the limit and “offset” for offset.

[svendpeter]

Ok !

Thank you ..

On Sat, Jan 20, 2018 at 11:07 AM, Benjie Gillam [e-mail]
wrote:

Same way, just use “first” for the limit and “offset” for offset.

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/postgraphql/postgraphql/issues/119#issuecomment-359157538,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AgsnUyUhxDoLqeav8LYtkjk4-SQ-BDfcks5tMazRgaJpZM4KCs0a
.

[svendpeter]

Hi ,

I do have one more question , if I may ..

Is there a way , in postgraphql , to change something so that I can produce
a query with following 'signature' for orderBy? Thank you for your time .
Really .

query { users (orderBy:[{field:"firstName", direction:"ASC"} }]) }

On Sat, Jan 20, 2018 at 2:12 PM, timon ionutadamclisi <
[email protected]> wrote:

Ok !

Thank you ..

On Sat, Jan 20, 2018 at 11:07 AM, Benjie Gillam [e-mail]
wrote:

Same way, just use “first” for the limit and “offset” for offset.

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/postgraphql/postgraphql/issues/119#issuecomment-359157538,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AgsnUyUhxDoLqeav8LYtkjk4-SQ-BDfcks5tMazRgaJpZM4KCs0a
.

[benjie]

You can replace the ordering plugins to acheive that.

https://github.com/graphile/graphile-build/blob/master/packages/graphile-build-pg/src/plugins/PgConnectionArgOrderBy.js

[svendpeter]

Thank you for your help !

On Wed, Jan 24, 2018 at 9:13 AM, Benjie Gillam [e-mail]
wrote:

You can replace the ordering plugins to acheive that.

https://github.com/graphile/graphile-build/blob/master/
packages/graphile-build-pg/src/plugins/PgConnectionArgOrderBy.js

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/postgraphql/postgraphql/issues/119#issuecomment-360041642,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AgsnU-HliHDCVnTo45GwY5KIuSu2pR8Dks5tNtg2gaJpZM4KCs0a
.

[barbalex]

@calebmer mentioned https://github.com/graphile/postgraphile/issues/119#issuecomment-254646243

as you can use a procedure to define your own custom orderings

I could not find this functionality. Where is it documented?

[benjie]

I believe Caleb was referring to "Custom Queries" which allow you to order by whatever you like by expressing the ordering within the PostgreSQL function: https://www.graphile.org/postgraphile/custom-queries/

However, in PostGraphile 4.4 it's also possible to order by computed fields. This isn't in the docs yet (and 4.4 isn't released yet); but basically you just add the @sortable smart comment to the function:

comment on function users_foo(users) is E'@sortable';

{
  allUsers(orderBy: [FOO_ASC]) { ... }
}

To try it out; yarn add postgraphile@next

[barbalex]

There is also a plugin to add ordering by fields in related tables: https://github.com/graphile-contrib/pg-order-by-related