Skip to main content
Version: v2.x

Postgres: Create Relationships

Introduction

A relationship from one table/view/Native Query to another can be created by defining a link between a column of the table/view/Native Query to a column of the other table/view/Native Query.

Typically, relationships between tables are defined using foreign-key constraints. But in some cases, it might not be possible to use foreign-key constraints to create the relationship. For example, while trying to create a relationship involving a view or Native Query since foreign-keys cannot be created on them.

Using foreign keys

Say we created two tables, authors(id, name) and articles(id, title, content, rating, author_id).

Let us now connect these tables to enable nested queries using a foreign-key:

Step 1: Add foreign-key constraint

Let's add a foreign-key constraint to the author_id column in the articles table.

In the Console, navigate to the Modify tab of the articles table. Click the Add button in the Foreign Keys section and configure the author_id column as a foreign-key for the id column in the authors table:

Add foreign-key constraint

Step 2: Create an object relationship

Each article has one author. This is an object relationship.

The Console infers potential relationships using the foreign-key created above and recommends these in the Relationships tab of the articles table.

Add an object relationship named author for the articles table as shown here:

Create an object relationship

We can now run a nested object query that is based on this object relationship.

Fetch a list of articles and each article's author:

GraphiQL
Query Variables
Request Headers
Documentation Explorer
No Schema Available

Step 3: Create an array relationship

An author can write multiple articles. This is an array relationship.

You can add an array relationship in the same fashion as an object relationship as shown above.

On the Console, add an array relationship named articles for the authors table as shown here:

Create an array relationship

We can now run a nested object query that is based on this array relationship.

Fetch a list of authors and a nested list of each author's articles:

GraphiQL
Query Variables
Request Headers
Documentation Explorer
No Schema Available

Using manual relationships

Let's say you have a table authors (id, name) and a view author_avg_rating (id, avg) which has the average rating of articles for each author.

Let us now create an object relationship called avg_rating from the authors table to the author_avg_rating view using a manual relationship:

Step 1: Open the manual relationship section

  • Open the Console and navigate to the Data -> authors -> Relationships tab.
  • Click on the Add Relationship button:
Open the manual relationship widget

Step 2: Fill in the relationships details

Once the widget is open, fill in the name of the relationship and pick a reference source

Define the relationship name and target

This will open up a "details" section below where you can fill in the rest of the relationship definition

Fill the relationship details

Step 3: Create the relationship

Now click on the Create Relationship button to proceed.

Fill the relationship details

We can now run a nested object query that is based on this object relationship.

Fetch a list of authors with the average rating of their articles:

GraphiQL
Query Variables
Request Headers
Documentation Explorer
No Schema Available

Tracking existing relationships inferred via foreign-keys

As mentioned in the Introduction section above, relationships can be inferred via foreign-keys that exist in your database:

The Console infers potential relationships using existing foreign-keys and recommends these on the Data -> Schema page

Track all relationships

You can choose to track the relationships individually using the Track buttons or hit the Track all button to track all the inferred relationships in one go.

Tracking relationships between tables and Native Queries

As mentioned in the Introduction section above, a relationship from a table to a Native Query can only be set up manually.

Given a table named articles and an existing Native Query named get_author, we can set up a relationship between the two.

POST /v1/metadata HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
"type": "bulk",
"args": [
{
"type": "pg_create_object_relationship",
"args": {
"source": "<db_name>",
"table": "articles",
"name": "author",
"using": {
"manual_configuration": {
"remote_native_query": "get_author",
"column_mapping": {
"id": "author_id"
}
}
}
}
}
]
}