Filter On Single Relations

[syndesis]

I have

CREATE TABLE publisher (
    publisher_id serial PRIMARY KEY,
    name text
);

CREATE TABLE author (
    author_id serial PRIMARY KEY,
    name text
);

CREATE TABLE book (
    book_id serial PRIMARY KEY,
    title text
    publisher integer REFERENCES publisher(publisher_id),
    author integer REFERENCES author(author_id)
);

I display all the books in a table from the query:

query allBooks {
    allBooks {
        nodes {
            book_id
            title
            publisherByPublisher {
                name
            }
            authorByAuthor {
                name
            }
        }
    }
}

I want to be able to apply arbitrary filters such as all the books with publisher.name ilike '%foo%' or author.name ilike '%bar%' or both. Also, I would like to be able to construct a single query that accepts filters but will return all results if "unfiltered."

This seems somewhat related to #26 but I decided to open a new issue because of how graphile has changed since that one was opened. Looking into how to implement this myself but any assistance you can provide would be greatly appreciated.

[barbalex]

Maybe build a view that integrates book, author and publisher? As all the fields are then direct children of nodes, you can filter by author name as well as book title.

[barbalex]

Actually: Seems that you can filter directly on authorByAuthor and publisherByPublisher (https://github.com/graphile-contrib/postgraphile-plugin-connection-filter/issues/26#issuecomment-370784000). I did not realize that.

[syndesis]

I have a function that does this right now, but I thought it would be nicer to have the functionality built in. The method in https://github.com/graphile-contrib/postgraphile-plugin-connection-filter/issues/26#issuecomment-370784000 doesn't work for forward relations. Also, it retrieves all books with null entries for authorByAuthor and publisherByPublisher for rows that don't match instead of actually filtering them out.

[mattbretl]

I just published a forward-relations branch with this functionality included. I still need to add proper tests before merging.

For anyone curious as to how this actually works in PostGraphile, I'll walk you through it..

Let's start with a simple schema:

create table author (
    author_id serial PRIMARY KEY,
    name text
);

create table book (
    book_id serial PRIMARY KEY,
    title text,
    author integer REFERENCES author(author_id)
);

insert into author (author_id, name) values
  (1, 'Alice'),
  (2, 'Bob');

insert into book (book_id, title, author) values
  (1, 'The Alice Book', 1),
  (2, 'The Bob Book', 2);

and a simple GraphQL query with no filter argument:

query allBooks {
  allBooks {
    nodes {
      title
      authorByAuthor {
        name
      }
    }
  }
}

which PostGraphile transforms into the following SQL:

with __local_0__ as (
  select
    to_json((__local_1__."title")) as "title",
    to_json((
      select json_build_object('name'::text, (__local_2__."name")) as object
      from "public"."author" as __local_2__
      where (__local_1__."author" = __local_2__."author_id") and (TRUE) and (TRUE)
    )) as "@authorByAuthor"
  from "public"."book" as __local_1__
  where (TRUE) and (TRUE) -- ◀️◀️◀️ The filter plugin manipulates this where clause
  order by __local_1__."book_id" ASC
),
__local_3__ as (select json_agg(to_json(__local_0__)) as data from __local_0__)
select coalesce((select __local_3__.data from __local_3__), '[]'::json) as "data"

Now let's run a GraphQL query with a simple filter:

query allBooksFilteredOnTitle {
  allBooks(filter: {
    title: { equalTo: "The Alice Book" }
  }) {
    nodes {
      title
      authorByAuthor {
        name
      }
    }
  }
}

which PostGraphile transforms into the following SQL:

with __local_0__ as (
  select
    to_json((__local_1__."title")) as "title",
    to_json((
      select json_build_object('name'::text, (__local_2__."name")) as object
      from "public"."author" as __local_2__
      where (__local_1__."author" = __local_2__."author_id") and (TRUE) and (TRUE)
    )) as "@authorByAuthor"
  from "public"."book" as __local_1__
  where (((__local_1__."title" = $1))) and (TRUE) and (TRUE)  -- ◀️◀️◀️
  order by __local_1__."book_id" ASC
),
__local_3__ as (select json_agg(to_json(__local_0__)) as data from __local_0__)
select coalesce((select __local_3__.data from __local_3__), '[]'::json) as "data"

Now let's assume we want to filter on the name field in the author table using the following GraphQL query:

query allBooksFilteredOnAuthorName {
  allBooks(filter: {
    authorByAuthor: { name: { equalTo: "Alice" } }
  }) {
    nodes {
      title
      authorByAuthor {
        name
      }
    }
  }
}

If PostGraphile had generated the original SQL using joins, the __local_2__ alias would be accessible to the where clause where the filter plugin operates, and we could simply add another condition such as __local_2__."name" = $1. But since __local_2__ is buried in a subselect, we need to rebuild that link within the where clause.

(NOTE: Using a join here would almost certainly be more performant, but PostGraphile does not currently support joins; see https://github.com/graphile/graphile-engine/issues/2. If join support is added at some point, we should revisit this implementation.)

The SQL query we want to generate (credit to @benjie for the where exists(select 1 from ... ) tip) looks like this:

with __local_0__ as (
  select
    to_json((__local_1__."title")) as "title",
    to_json((
      select json_build_object('name'::text, (__local_2__."name")) as object
      from "public"."author" as __local_2__
      where (__local_1__."author" = __local_2__."author_id") and (TRUE) and (TRUE)
    )) as "@authorByAuthor"
  from "public"."book" as __local_1__
  where
    exists(                                                  -- ◀️◀️◀️
      select 1                                               -- ◀️◀️◀️
      from "public"."author" as __local_3__                  -- ◀️◀️◀️
      where (__local_1__."author" = __local_3__."author_id") -- ◀️◀️◀️
        and (((__local_3__."name" = $1)))                    -- ◀️◀️◀️
    )                                                        -- ◀️◀️◀️
    and (TRUE) and (TRUE)
  order by __local_1__."book_id" ASC
),
__local_4__ as (select json_agg(to_json(__local_0__)) as data from __local_0__)
select coalesce((select __local_4__.data from __local_4__), '[]'::json) as "data"

We just duplicated the logic from the subselect (with __local_2__ becoming __local_3__) and then tacked on __local_3__."name" = $1.

This approach can even be used to recursively resolve relations in the GraphQL query. To prove this, let's start with a schema that includes an additional relation:

create table account (
    account_id serial PRIMARY KEY,
    email text
);

create table author (
    author_id serial PRIMARY KEY,
    name text,
    account integer REFERENCES account(account_id)
);

create table book (
    book_id serial PRIMARY KEY,
    title text,
    author integer REFERENCES author(author_id)
);

insert into account (account_id, email) values
  (1, 'alice@example.com'),
  (2, 'bob@example.com');

insert into author (author_id, name, account) values
  (1, 'Alice', 1),
  (2, 'Bob', 2);

insert into book (book_id, title, author) values
  (1, 'The Alice Book', 1),
  (2, 'The Bob Book', 2);

and then consider the following GraphQL query:

query allBooksFilteredOnAuthorAccountEmail {
  allBooks(filter: {
    authorByAuthor: {
      accountByAccount: {
        email: {
          equalTo:"alice@example.com"
        }
      }
    }
  }) {
    nodes {
      title
      authorByAuthor {
        name
      }
    }
  }
}

The SQL query we want to generate looks like this:

with __local_0__ as (
  select
    to_json((__local_1__."title")) as "title",
    to_json((
      select json_build_object('name'::text, (__local_2__."name")) as object
      from "public"."author" as __local_2__
      where (__local_1__."author" = __local_2__."author_id") and (TRUE) and (TRUE)
    )) as "@authorByAuthor"
  from "public"."book" as __local_1__
  where
    exists(
      select 1
      from "public"."author" as __local_3__
      where (__local_1__."author" = __local_3__."author_id")
        and exists(                                                -- ◀️◀️◀️
          select 1                                                 -- ◀️◀️◀️
          from "public"."account" as __local_4__                   -- ◀️◀️◀️
          where (__local_3__."account" = __local_4__."account_id") -- ◀️◀️◀️
            and (((__local_4__."email" = $1)))                     -- ◀️◀️◀️
        )                                                          -- ◀️◀️◀️
    )
    and (TRUE) and (TRUE)
  order by __local_1__."book_id" ASC
),
__local_5__ as (select json_agg(to_json(__local_0__)) as data from __local_0__)
select coalesce((select __local_5__.data from __local_5__), '[]'::json) as "data"

This is the approach I took on the forward-relations branch. Don't expect it to be performant, but I believe it's the best we can do until PostGraphile supports joins.

[benjie]

Nice one @mattbretl! There's quite a lot of relational algebra that PostgreSQL can perform (e.g. turning subqueries into joins and vice-versa), so I'm not certain that this won't be performant - I'd love to see benchmarks on it.

I'm hoping to come up with a generic way where this kind of nested behaviour could be added/augmented via a different plugins - currently only the plugin that introduces the original argument can add the nested abilities.

[mattbretl]

I merged #54 and #55 and released beta.16 with support for this. It's disabled by default; set connectionFilterRelations: true on graphileBuildOptions to enable.

[barbalex]

Sounds great.

Would that be --enable-connection-filter-relations when using the command line?

Or --connection-filter-relations true?

[mattbretl]

AFAIK, plugins can't access CLI options, so you'd have to be using it as a library.

[benjie]

Schema plugins can't. Server plugins can, but it's currently undocumented and experimental. (Server plugins can also trigger schema plugins to be loaded, so ultimately you might wind up making this a server plugin.)

You might want to track this issue:

https://github.com/graphile/postgraphile/issues/705

I intend to add a way for CLI users to add schema options using postgraphilerc. Not there yet (I think? haven't tested in a while...)

[barbalex]

What exactly is the difference between schema plugins and server plugins?

Im am currently using this on the server:

pm2 start ./node_modules/.bin/postgraphile -- -c postgres://user:password@localhost:5432/apflora --append-plugins `pwd`/node_modules/postgraphile-plugin-connection-filter/index.js --jwt-token-identifier auth.jwt_token --default-role anon --jwt-secret secret -s apflora --cors --disable-query-log --enable-query-batching

How could I get connection-filter-relations true? Could I somehow use a server plugin?

Sorry for probably asking basics.

[benjie]

General note: rather than

--append-plugins `pwd`/node_modules/postgraphile-plugin-connection-filter/index.js

do

--append-plugins postgraphile-plugin-connection-filter

---

Schema plugins affect the generated GraphQL schema only: https://www.graphile.org/postgraphile/extending/

Server plugins affect the CLI, HTTP, etc (and may also load schema plugins): https://www.graphile.org/postgraphile/plugins/

---

There's currently no CLI flags for enabling connectionFilterRelations: true; however you can enable that setting by either using PostGraphile as a library or using a .postgraphilerc.js file https://www.graphile.org/postgraphile/usage-cli/#rc-file-options using the secret graphileBuildOptions:

module.exports = {
  options: {
    graphileBuildOptions: {
      connectionFilterRelations: true
    }
  }
};

Note postgraphilerc will be replaced in v5 with a better system.

[barbalex]

@benjie Thanks a lot for this great help!!!

[barbalex]

@benjie and everyone with the same problem:

A .postgraphilerc.js file containing

module.exports = {
  options: {
    graphileBuildOptions: {
      connectionFilterRelations: true
    }
  }
};

works like a charm!

[barbalex]

Hm. I have realized that filtering on related fields works in local dev. But not in production. In production I get a graphql error because apByArtIdExists does not exist.

Why could that be?

[benjie]

I'd guess that your database is out of sync with your development one, or you need to restart PostGraphile to trigger re-introspection.

[barbalex]

your database is out of sync with your development one

I just recreated the local db from a dump of the productive one. The issue remains.

or you need to restart PostGraphile to trigger re-introspection

I have done that a bunch of times. Does not help.

[benjie]

@barbalex I think you should create a new issue with as many reproduction steps as you can muster. I can't think of a reason why it would behave differently in production vs development, I don't think we have any code that gates on that. Are you using readCache or similar to cache the introspection results?

[barbalex]

Are you using readCache or similar to cache the introspection results?

No

[barbalex]

O.k., got it: On more of my nooby errors: I had simply forgotten to upload the .postgraphilerc.js file to the api server.

@benjie I owe you a beer

[benjie]

Hah, that makes sense and easy to miss. Thanks for letting me know, and for sponsoring me 🙏