Data API Reference: Relationships¶

Relationships are used to capture the connectedness of data amongst tables. In a relational database, when modelling data we add foreign key constraints to establish connections between various tables.

Let’s consider a simple scenario where there are two tables article and author. The article table (child) has a column author_id which points to the id column of author table (parent). When we fetch a row from the article table, we may need the author information along with article columns. Similarly, when we fetch a row from the author table, we may need the articles written by the author along with the author columns.

In general, when retrieving data from tables, it is very helpful if we can also fetch the related data alongside the columns. This is where relationships come in. They can be considered as pseudo columns for a table to access the related data. In the above example, the relationships that we can define are

author in article table
articles in author table

As you may have noticed, there are two kinds of relationships, object/many-to-one relationships (author) and array/one-to-many relationships (articles). From now on, we’ll only use object and array relationships as terminology.

Let’s define these relationships:

POST data.<cluster-name>.hasura-app.io/v1/query HTTP/1.1
Content-Type: application/json
Authorization: Bearer <auth-token> # optional if cookie is set
X-Hasura-Role: <role>  # optional. Pass if only specific user role has access

{
    "type": "create_object_relationship",
    "args": {
        "table": "article",
        "name": "author",
        "using": {
            "foreign_key_constraint_on" : "author_id"
        }
    }
}

This reads as follows:

Create an object relationship author on article table, using the foreign_key_constraint_on the author_id column.

POST data.<cluster-name>.hasura-app.io/v1/query HTTP/1.1
Content-Type: application/json
Authorization: Bearer <auth-token> # optional if cookie is set
X-Hasura-Role: <role>  # optional. Pass if only specific user role has access

{
    "type": "create_array_relationship",
    "args": {
        "table": "author",
        "name": "articles",
        "using": {
            "foreign_key_constraint_on" : {
                "table" : "article",
                "column" : "author_id"
            }
        }
    }
}

Create an array relationship articles on author table, using the foreign_key_constraint_on the author_id column of the article table.

The syntax is slightly different for creating an object and an array relationship. But, note that in both the cases, we are doing the same thing, i.e, specifying the column on which the foreign key constraint is defined.

create_object_relationship¶

create_object_relationship is used to create an object relationship on a table. There cannot be an existing column or relationship with the same name.

There are 2 ways in which you can create an object relationship.

1. Using foreign key constraint on a column¶

You can look at this example.

2. Manual configuration¶

This is an advanced feature which is mostly used to define relationships on or to views. We cannot rely on foreign key constraints as they are not valid to or from views. So, when using manual configuration, we have to specify the remote table and how each of the columns in this table are mapped to the columns of the remote table.

Let’s say we have a view called article_detail which has three columns article_id and view_count and average_rating. We can now define an object relationship called article_detail on the article table as follows:

POST data.<cluster-name>.hasura-app.io/v1/query HTTP/1.1
Content-Type: application/json
Authorization: Bearer <auth-token> # optional if cookie is set
X-Hasura-Role: <role>  # optional. Pass if only specific user role has access

{
    "type": "create_object_relationship",
    "args": {
        "table": "article",
        "name": "article_detail",
        "using": {
            "manual_configuration" : {
                "remote_table" : "article_detail",
                "column_mapping" : {
                    "id" : "article_id"
                }
            }
        }
    }
}

Note

It is easy to shoot yourself in the foot when using manual_configuration. One simple check is to ensure that foreign key constraint semantics are valid on the columns being used in column_mapping. In the previous example, if it was allowed, a foreign key constraint could have been defined on article table’s id column to article_count view’s article_id column.

Syntax¶

Key	Required	Schema	Description
table	true	TableName	Name of the table
name	true	RelationshipName	Name of the new relationship
using	true	ObjRelUsing	Use one of the available ways to define object relationship
comment	false	text	comment

`ObjRelUsing`¶

Key	Required	Schema	Description
foreign_key_constraint_on	false	PGColumn	The column with foreign key constraint
manual_configuration	false	ObjRelUsingManualMapping	Manual mapping of table and columns

Note

There has to be at least one and only one of foreign_key_constraint_on and manual_mapping.

`ObjRelUsingManualMapping`¶

Key	Required	Schema	Description
remote_table	true	TableName	The table to which the relationship has to be established
column_mapping	true	Object (PGColumn : PGColumn)	Mapping of columns from current table to remote table

create_array_relationship¶

create_array_relationship is used to create an array relationship on a table. There cannot be an existing column or relationship with the same name.

There are 2 ways in which you can create an array relationship.

1. Using foreign key constraint on a column¶

You can look at this example.

2. Manual configuration¶

This is an advanced feature which is mostly used to define relationships on or to views. We cannot rely on foreign key constraints as they are not valid to or from views. So, when using manual configuration, we have to specify the remote table and how each of the columns in this table are mapped to the columns of the remote table.

Syntax¶

Key	Required	Schema	Description
table	true	TableName	Name of the table
name	true	RelationshipName	Name of the new relationship
using	true	ArrRelUsing	Use one of the available ways to define array relationship
comment	false	text	comment

`ArrRelUsing`¶

Key	Required	Schema	Description
foreign_key_constraint_on	false	ArrRelUsingFKeyOn	The column with foreign key constraint
manual_mapping	false	ArrRelUsingManualMapping	Manual mapping of table and columns

`ArrRelUsingFKeyOn`¶

Key	Required	Schema	Description
table	true	TableName	Name of the table
column	true	PGColumn	Name of the column with foreign key constraint

`ArrRelUsingManualMapping`¶

Key	Required	Schema	Description
remote_table	true	TableName	The table to which the relationship has to be established
column_mapping	true	Object (PGColumn : PGColumn)	Mapping of columns from current table to remote table

drop_relationship¶

drop_relationship is used to drop a relationship (both object and array) on a table. If there are other objects dependent on this relationship like permissions and query templates, the query will fail reporting the dependencies unless cascade is set to true. If cascade is set to true, the dependent objects are also dropped.

An example:

POST data.<cluster-name>.hasura-app.io/v1/query HTTP/1.1
Content-Type: application/json
Authorization: Bearer <auth-token> # optional if cookie is set
X-Hasura-Role: <role>  # optional. Pass if only specific user role has access

{
    "type": "drop_relationship",
    "args": {
        "table": "article",
        "relationship": "article_detail"
    }
}

Syntax¶

Key	Required	Schema	Description
table	true	TableName	Name of the table
name	true	RelationshipName	Name of the relationship that needs to be dropped
cascade	false	Boolean	When set to `true`, all the dependent items on this relationship are also dropped

Note

Be careful when using cascade. First, try running the query without cascade or cascade set to false.

set_relationship_comment¶

set_relationship_comment is used to set/update the comment on a relationship. Setting the comment to null removes it.

An example:

POST data.<cluster-name>.hasura-app.io/v1/query HTTP/1.1
Content-Type: application/json
Authorization: Bearer <auth-token> # optional if cookie is set
X-Hasura-Role: <role>  # optional. Pass if only specific user role has access

{
    "type": "set_relationship_comment",
    "args": {
        "table": "article",
        "name": "article_detail",
        "comment" : "has extra information about an article like count etc."
    }
}

Syntax¶

Key	Required	Schema	Description
table	true	TableName	Name of the table
name	true	RelationshipName	The relationship
comment	false	Text	comment

« previous | next »

Was this page helpful?

Data API Reference: Relationships¶

create_object_relationship¶

1. Using foreign key constraint on a column¶

2. Manual configuration¶

Syntax¶

ObjRelUsing¶

ObjRelUsingManualMapping¶

create_array_relationship¶

1. Using foreign key constraint on a column¶

2. Manual configuration¶

Syntax¶

ArrRelUsing¶

ArrRelUsingFKeyOn¶

ArrRelUsingManualMapping¶

drop_relationship¶

Syntax¶

set_relationship_comment¶

Syntax¶

<%= totalResults %> result<%= totalResults > 1 ? 's' : '' %> for '<%= _.escape(searchString) %>' powered by

`ObjRelUsing`¶

`ObjRelUsingManualMapping`¶

`ArrRelUsing`¶

`ArrRelUsingFKeyOn`¶

`ArrRelUsingManualMapping`¶

<%= totalResults %> result<%= totalResults > 1 ? 's' : '' %> for '<%= _.escape(searchString) %>'

powered by