Making relational queries with Postgraphile

Question

I have the following db setup

events

| event_id | event_name |
|----------|------------|
| 1        | Test       |
| 2        | World      |

awards

| award_id | event_id | award_name  |
|----------|----------|-------------|
| 1        | 1        | greatness   |
| 2        | 2        | really good |

and I am trying to query the db as follows:

{ 
  allAwards {
    edges {
      node {
        awardId
        eventByEventId(eventId: eventId) {
          year
          name
          country
          location
          date
          dateCreated
        }
      }
    }
  }
}

With the following error:

Cannot query field "eventByEventId" on type "Award". Did you mean "eventId"?",

I updated your title and added the `postgraphile` tag. This question is really about Postgraphile and not about GraphQL in general. — Daniel Rearden, Jun 24 '19 at 14:49
Cheers Daniel. It is? You are able to effectively use a `Where` clause in graphQL? — Jamie Hutber, Jun 24 '19 at 16:03
GraphQL itself is agnostic to the underlying storage layer. It might interface with a database, like Postgraphile allows you to do, but it could just as easily get data from the file system or other APIs. As such, database-specific concepts don't apply to GraphQL in general. — Daniel Rearden, Jun 24 '19 at 16:19
How did you create your `awards` table? Did you add a foreign key constraint to `event_id` as shown in the [docs](https://www.graphile.org/postgraphile/relations)? — Daniel Rearden, Jun 24 '19 at 16:26
ye, so in my awards table I've 3 FKs, or for the example awards has fk's on `event_id`. Currently the only filtering I have is via `postgraphile-plugin-connection-filter` — Jamie Hutber, Jun 24 '19 at 16:38
The error you’ve quoted suggests that there’s no foreign key constraint; if there really is then try restarting the server to re-run the introspection. Your eventByEventId field also requires no arguments since the EventId is supplied on the awards table. Here’s an example (using the simplify inflectors plugin for nicer field names): https://www.graphile.org/postgraphile/examples/#Relations__Forums_topics_posts — Benjie, Jun 24 '19 at 22:47

score 2 · Accepted Answer · answered Jun 26 '19 at 07:56

The error you are receiving suggests that you don't have a foreign key constraint between the tables (i.e. references events), see relations in the PostGraphile docs. Your query is also invalid in that it's adding arguments that do not exist to the eventByEventId field: the eventId is implicit as it already exists on the record you're querying. If you use GraphiQL or a similar GraphQL IDE it should show you where your query does not match the GraphQL schema, and even give you hints as to how to fix it.

I've recreated your schema with the following SQL:

create table events (
  event_id serial primary key,
  event_name text
);
insert into events (event_name) values ('Test'), ('World');

create table awards (
  award_id serial primary key,
  event_id int references events, -- < THIS IS THE IMPORTANT BIT
  award_name text
);
insert into awards (event_id, award_name) values (1, 'greatness'), (2, 'really good');

And then ran the latest postgraphile against it:

$ npx postgraphile@latest -c deleteme
npx: installed 119 in 11.26s

PostGraphile v4.4.1 server listening on port 5000 

  ‣ GraphQL API:         http://localhost:5000/graphql
  ‣ GraphiQL GUI/IDE:    http://localhost:5000/graphiql (enhance with '--enhance-graphiql')
  ‣ Postgres connection: postgres:///deleteme
  ‣ Postgres schema(s):  public
  ‣ Documentation:       https://graphile.org/postgraphile/introduction/
  ‣ Join Jimmy McBroom in supporting PostGraphile development: https://graphile.org/sponsor/

* * *

And issued the GraphQL query:

{ 
  allAwards {
    edges {
      node {
        awardId
        eventByEventId {
          eventName
        }
      }
    }
  }
}

This all works as expected:

PS: I'd recommend you use the @graphile-contrib/pg-simplify-inflector plugin to automatically simplify the names of the various relations. With this plugin, the command line would be:

postgraphile -c deleteme --append-plugins @graphile-contrib/pg-simplify-inflector

And the query:

{ 
  awards {
    edges {
      node {
        awardId
        event {
          eventName
        }
      }
    }
  }
}

Amazing Benjie. Thank you for this detailed explanation. I do believe I have set up my `FK`'s correctly and restarted the servers. But with this I can go through step by step and clearly nail down my problem. But this does bring me great joy, knowing my db set up isn't completely wrong. — Jamie Hutber, Jun 26 '19 at 11:27
When you figure it out, do let me know, as it will help me guide other users in future — Benjie, Jun 26 '19 at 13:14
A long time later and here I am. Thanks again Benjie. So indeed it was the `FK` missing and once tht `FK` was placed in the correct position everything worked as expected!! Now queries are trivial. https://stackoverflow.com/questions/59524125/how-to-use-a-condition-for-a-nested-relation-gql?noredirect=1 This is my next relational question. If you have any free time! Thank you again — Jamie Hutber, Jan 02 '20 at 08:29

score -1 · Answer 2 · answered Jun 24 '19 at 12:49

-1

GraphQL does not query databases, what middleware are you using?

eventByEventId must be defined in the GraphQL resolver.

answered Jun 24 '19 at 12:49

Sebdej

318
2
8

Interesting that I can't query, postgraphile has created eventByEventId for me. I thought possible you can have graphQL stich them together for me. I imagine I'll need to query both and filter in the app? – Jamie Hutber Jun 24 '19 at 12:51

Making relational queries with Postgraphile

events

awards

2 Answers2