Question: how to filter based on parameter?

[t829702]

i have a simple table with field country TEXT in postgres, want to filter based on user input can say WHERE country IN (a-list-of-user-selected-countries) in GraphQL it's like

query Query(countries: [String!] = null) {
  allEntries(filter({ country: { in: $countries } })) {
    totalCount
    node {
      ... // interested fields to query
    }
  }
}

this indeed works when providing variables={ "countries": ["United States", "United Kingdom", ...] }, but then I want to use this same query when user did not select any countries from UI, I don't want to pass all possible values (there are 180+ country names in the world) into variables, to save from providing a too big list; but instead I want to omit countries from variables to mean don't filter on country field, wonder is it possible for GraphQL to check and put WHERE country in (?) only when got a non-null $countries from GraphQL variables?

  allEntries(filter({
    or: [ { $countries isNull },
          { country: { in: $countries } }
    ] })) {
    ...
  }

want it to convert to some SQL query like:

SELECT * FROM table WHERE ($1 IS NULL OR country IN ($1)) AND ...other filters
    -- the $1 is binding to user provided list of selected countries, or a null to mean skip this filter

Another question is when I do provide variables={ "countries": ["United States", "United Kingdom", ...] }, how can I also have a boolean switch in the UI to re-use same query to also select those rows with unknown country (aka country IS NULL )?

SELECT * FROM table WHERE country IS NULL OR country IN (?)
        -- and optionally ORDER BY country ASC with NULLS FIRST or NULLS LAST?

[t829702]

is it even possible? with no custom coding or very little wrapping?

[rattrayalex]

Could you try this, based on your suggestion?

  allEntries(filter({
    or: [
      { isNull: $countries },
      { country: { in: $countries } }
    ] 
})) {

(disclaimer, I don't currently use this library)

[mattbretl]

You can accomplish this using the connectionFilterAllowNullInput option.

CREATE SCHEMA app_public;
CREATE TABLE app_public.entries (
	id serial PRIMARY KEY,
	country text
);
INSERT INTO app_public.entries VALUES
  (1, 'United Kingdom'),
  (2, 'United States');

const http = require("http");
const { postgraphile } = require("postgraphile");
const ConnectionFilterPlugin = require("postgraphile-plugin-connection-filter");

http
  .createServer(
    postgraphile(
      "postgres:///filter_issue_147",
      "app_public",
      {
        appendPlugins: [ConnectionFilterPlugin],
        graphiql: true,
        enhanceGraphiql: true,
        graphileBuildOptions: {
          connectionFilterAllowNullInput: true
        }
      }
    )
  )
  .listen(process.env.PORT || 3000);

query Entries($countries: [String!] = null) {
  allEntries(filter: { country: { in: $countries } }) {
    nodes {
      id
      country
    }
    totalCount
  }
}

With that query, if you supply countries in the query variables, it will add country IN (...) to the SQL WHERE clause. If you don't supply countries, it won't alter the SQL WHERE clause at all, thus returning all countries.

[rattrayalex]

Beautiful! @mattbretl probably okay to close this issue then? Though it might be helpful to add this example to the docs for that option…

[mattbretl]

Yep, @t829702 feel free to reach out if this doesn't solve your issue.

PRs are always welcome on the docs. 🙂