Postgraphile: Only NATURAL order when creating a procedure

[charlyz]

Hey !

I have a question about the orderBy argument. I created a procedure to search for a value that looks like the one in your examples:

create function forum_example.search_posts(search text) returns setof forum_example.post as $$
  select post.*
  from forum_example.post as post
  where post.headline ilike ('%' || search || '%') or post.body ilike ('%' || search || '%')
$$ language sql stable;

When I use searchPost, the only order I have for the argument orderBy is NATURAL. How can I get all the other types of ordering like NAME_ASC, NAME_DESC? I tried to find an answer by searching the issues but I had no luck. Sorry if you already answered that question.

EDIT: I'm adding a second question. How can I use a condition to return all the results with a value lower or greater than a threshold? For example condition: {clicksLast30Days < 60}.

Cheers

[calebmer]

Hey @charlyz 😊

Adding the other orderings would be fairly easy to do, just know that with natural ordering you define whatever ordering you want inside of the procedure. Take for instance:

create function forum_example.search_posts(search text) returns setof forum_example.post as $$
  select post.*
  from forum_example.post as post
  where post.headline ilike ('%' || search || '%') or post.body ilike ('%' || search || '%')
  order by post.author_id, post.headline
$$ language sql stable;

Here we order by the author_id _and_ headline, or any other variant. How important is ordering in GraphQL to you? Can ordering in the procedure be enough for now?

Relevant links for anyone interested in implementing this:

• Here is the code responsible for only NATURAL ordering in procedures: https://github.com/calebmer/postgraphql/blob/032bee46b22560e83dc5f76bd7bc5174be4d2e7a/src/postgraphql/schema/procedures/PgProcedurePaginator.ts#L30-L40
• Here is the code responsible for creating all of the column orderings for tables: https://github.com/calebmer/postgraphql/blob/032bee46b22560e83dc5f76bd7bc5174be4d2e7a/src/postgres/inventory/paginator/PgCollectionPaginator.ts#L49-L117

The implementation may be as simple as an if-branch and a copy/paste.

---

For your second question, there is no current way to do this in the GraphQL API 😣. Mostlty because we can’t decide on a good API design for this. See one of our oldest issues, https://github.com/calebmer/postgraphql/issues/47, for more information on the topic. If you have a good design, please share :+1:

For now, you can totally get this behavior with procedures such as:

create function forum_example.search_posts(threshold integer) returns setof my_table as $$
  select *
  from my_table
  where my_column < threshold
$$ language sql stable;

You could even hardcode threshold to 30 in this case. Anything you can do in SQL is possible with a procedure.

[charlyz]

@calebmer Thanks for the clear and detailed answer, it's very appreciated. I'll do a copy paste to solve my ordering problem and will create additional procedures to support the lt and gt conditions. I don't have a suggestion for the design of the conditions mechanism but keep up the good work. So far postgraphql has been a blessing for one of our prototypes :)

[guigrpa]

@calebmer I know this is closed, but I've got some additional input that might be of interest.

I have implemented a procedure in PG that allows filtering records not just by the fields in the initial table (say, post) but by other related tables as well (person, comment). Most of its functionality is extracted in a single polymorphic function, so that the code that is repeated per table is very short; I'd say it's becoming quite nice.

The problem I have now is sorting-pagination. I'd like to improve my procedure so that it allows sorting by fields in the initial table and related ones (only N-to-1 relations from the initial table), or by multiple fields at once. Since this is something that probably falls out of the scope of postgraphql to handle internally, would it be possible to enrich the interface with PG procedures so that:

• they get the connection arguments (first, after, last, before)
• they can (somehow?) output the cursor for each row?

If not, I guess I'll be limited to natural, or offset-based pagination for now, with its inherent limitations.

[calebmer]

@guigrpa that’s a really interesting idea. I hadn’t thought about that. I’d love to support this case perhaps if a function’s name starts with graphql_connection_*. If anyone wants to implement this functionality I’d happily merge a PR 👍

[guigrpa]

@calebmer I'd love to have a go at this, even though it might be quite hard to dive into such a complex project.

I would propose the following API (let's assume the initial table is person):

CREATE TYPE forum_example.graphql_connection_person AS (
  cursor text,
  node forum_example.person
);

CREATE FUNCTION forum_example.graphql_connection_find_people(
  first integer,  -- directly from the GraphQL query
  last integer,   -- directly from the GraphQL query
  after text,     -- decoded from base64
  before text,    -- decoded from base64
  criteria jsonb DEFAULT '{}',
  sort_criteria jsonb DEFAULT '[]'
) RETURNS SETOF forum_example.graphql_connection_person AS $$
...
$$ LANGUAGE sql STABLE;

Such a function would be exposed as findPeople in the GraphQL schema. All of its arguments would be extracted from the GraphQL query, with the first 4 ones corresponding to the Relay specs for connections and the rest being free (the above example would be what I personally need). Each of the values returned by the function would become an edge in the corresponding connection (as is already the case in postgraphql).

What do you think? Any suggestions are welcome, especially those that would simplify a PR! :) For instance, would it be simpler if the return type of the SRF would be a SETOF json with the following structure, instead of the composite type proposed above?

{
  "cursor": "cursorValue",
  "type": "person",
  "row": {
    "id": "..."
  }
}

[benjie]

Aside: if you're new to the codebase, here are a few links that might help you get some understanding faster:

https://github.com/calebmer/postgraphql/blob/master/src/README.md

https://github.com/calebmer/postgraphql/wiki/Code:-how-are-things-like-barByFooId-built%3F

https://github.com/calebmer/postgraphql/issues/265#issuecomment-265736977