DOCS  vx.y

Support

Part X: Aggregations, views and custom relationships

The data API select query is designed to be simple yet powerful. But there are some queries that you cannot express with the select query syntax. For example, getting the number of likes for each article. Aggregations (like count, avg, group_by, etc) are not supported in the select query syntax. This is a conscious decision we have made to keep the query language small.

To express complex queries like aggregations, window functions, custom joins etc, use SQL, which is designed for this purpose. If you can express your query in SQL, you can define a view with it on which you can then use the data APIs as we do on tables.

For example, let’s see how we can get the average rating of all articles written by an author.

Let’s define the view in SQL:

CREATE VIEW author_average_rating AS
    SELECT author.id, avg(article.rating)
    From author, article
    WHERE author.id = article.author_id
    GROUP BY author.id

Now, let’s set this view up on Hasura via the API console:

Navigate to the Data > SQL tab in the API console and enter the above SQL statement. Ensure the This is a migration and Track table boxes are checked so that the query is added as a database migration and the Data microservice is aware of the created view.

Hit Run to create the view on the Postgres database and to track it in the Data microservice (i.e. allow querying via data APIs).

Now, you can use a select query to fetch the author’s average rating as if author_average_rating was a table.

Views are read only!

Views are like read-only logical tables on the database. So that means that data API requests to select will work, but you cannot insert/update/delete items from the view.

Relationships to/from views

We have seen how we can get the author’s average rating using the data APIs. However, additional information of each author can be attached to the author_average_rating view using an object relationship say, author.

All the relationships that we’ve defined till now use foreign key constraints. However, you cannot define foreign key constraints on/to views. So, in these cases, we have to manually define a relationship.

Here, we are defining a relationship from a author_average_rating view to the author table:

The above relationship will allow you to fetch the author’s details when querying the view. But we might want to fetch the author’s average rating when querying the author table itself. So we will now create a relationship from the author table to the author_average_rating view:

Now, let’s fetch the author details including their average rating:

GraphQL

JSON API

query fetch_author {
  author(order_by: ["+name"]) {
    id
    name
    average_rating {
      avg
    }
  }
}

POST /v1/query HTTP/1.1
Content-Type: application/json
Authorization: Bearer <auth-token>
X-Hasura-Role: admin

{
    "type" : "select",
    "args" : {
        "table" : "author",
        "columns": [
            "id", "name",
            {
                "name" : "average_rating",
                "columns" : ["avg"]
            }
        ],
        "order_by" : "+name"
    }
}

Note

The above query will work only after adding permissions to the author_average_rating view.

Next: Custom code and microservices

Next, head to Part XI: Custom code and microservices.

« previous | next »

Was this page helpful?

Submit

Thank you for your feedback!

Want to contribute or report missing content? Check out the github repo for docs.
Powered by Sphinx. Copyright © 2019 Hasura.