Query aggregates through a relationship

[danielcompton]

It would be useful for us to be able to aggregate through a join table to calculate an aggregate over a parent table. For a concrete example, consider:

• There are many blogs
• Each blog has many posts
• Each post has many comments

We would like to be able to query for the number of comments posted to each blog. Currently, it doesn't appear as if aggregations can query "through" a relationship.

The SQL to do this is:

-- Select number of comments in each blog
SELECT blog.id, blog.name, COUNT(comment.comment_id) AS comment_count
FROM blog
LEFT JOIN post ON post.blog_id = blog.id
LEFT JOIN comment on comment.post_id = post.post_id
GROUP BY (blog.id, blog.name)

With Hasura, we can query the count of comments, but only by post, not by blog.

query {
  blog {
    id
    name
    postsByblogId {
      commentsBypostId_aggregate {
        aggregate {
          count 
        }
      }
    }
  }
}

Query result

{
  "data": {
    "blog": [
      {
        "id": 1,
        "name": "blog1",
        "postsByblogId": [
          {
            "commentsBypostId_aggregate": {
              "aggregate": {
                "count": 1
              }
            }
          },
          {
            "commentsBypostId_aggregate": {
              "aggregate": {
                "count": 0
              }
            }
          }
        ]
      },
      {
        "id": 2,
        "name": "blog2",
        "postsByblogId": [
          {
            "commentsBypostId_aggregate": {
              "aggregate": {
                "count": 0
              }
            }
          }
        ]
      }
    ]
  }
}

What we would like is something closer to this:

{
  "data": {
    "blog": [
      {
        "id": 1,
        "name": "blog1",
        "commentsByblogId_aggregate": {
          "aggregate": {
            "count": 1
          }
        }
      },
      {
        "id": 2,
        "name": "blog2",
        "commentsByblogId_aggregate": {
          "aggregate": {
            "count": 0
          }
        }
      }
    ]
  }
}