GraphQL Query Structure to Filter / Aggregate Results from a PostgreSQL Table

Question

I am new to GraphQL and I am struggling to understand how to access / reference a table in the same logical way that I would approach a normal SQL query. I have created a docker container for postgres and I have initialized the database with a simple table of data.

To create the table, I ran this in my \init directory. (Running Windows btw)

CREATE TABLE fda.nfl (
    "team" TEXT,
    "conference" TEXT,
    "division" TEXT,
    "city" TEXT,
    "wins" INT,
    "losses" INT,
    "ties" INT
);

In GraphiQL, I can simply select everything with this query:

{
  allNfls {
    edges {
      node {
        team
        conference
        division
        city
        wins
        losses
        ties
      }
    }
  }
}

I want to run something that can aggregate vertically and horizontally, e.g. sum(losses) as total_losses or (wins / (wins + losses + ties)) as win_ratio. I am unsure how to go about either of those scenarios with GraphQL. I would also need to query on certain conditions, but passing in a column name as an argument to node does not seem to work, i.e. node(team: "Chiefs") spits back an error about type allNfls

Is referencing a Postgres table like this in GraphQL even possible?

Answer 1

I would need to query on certain conditions, but passing in a column name as an argument to node does not seem to work

The standard filtering plugin adds condition arguments to the allNfls field, so try

query {
  allNfls(condition: {team: "Chiefs"}) {
    edges {
      node {
        conference
        division
        city
      }
    }
  }
}

Alternatively, assuming that the team name is a primary key in your table, you should also be able to select the individual team:

query {
  nfl(team: "Chiefs") { # maybe `nflByTeam`?
    city
    wins
    losses
    ties
  }
}

I want to run something that can aggregate vertically and horizontally, e.g. sum(losses) as total_losses or (wins / (wins + losses + ties)) as win_ratio. I am unsure how to go about either of those scenarios with GraphQL.

For vertical aggregates, you would need to use the (non-standard) aggregates plugin, with which you could do something like

query {
  allNfls { # might also use a condition
    sums {
      losses
    }
  }
}

The "horizontal aggregate" is a custom computed column, which you can most easily achieve by writing a Postgres SQL function:

CREATE FUNCTION fda.nfl_win_ratio(n fda.nfl) RETURNS real
LANGUAGE SQL
IMMUTABLE
AS $$ SELECT n.wins / (n.wins + n.losses + n.ties)) $$

For more complicated stuff, you might want to add your own fields to any of the GraphQL types by writing your own plugin with the help of makeExtendSchemaPlugin, which can use the full power of SQL and JavaScript combined.