I am trying to aggregate multiple values in one graphQL query. It seems to be impossible to do this (Using Hasura GraphiQL to build the query). It is possible to do something like it in Postgres.
Background
I want to show statistics about my advertisement's number of views (shown), clicks and dismisses. In graphQL I need to make separate queries for interaction_type=
"shown"
, "dismissed"
or "clicked"
query AdStatistics {
ads_interaction_aggregate(
where: {created_at: {_gte: "2023-06-22"},
interaction_type: {_eq: "shown"},
ad_id: {_eq: "7e9250bc-4f85-45ad-900d-f08558ebc775"}}) {
aggregate {
count(columns: [interaction_type], distinct: false)
}
}
}
My goal is to re-create this SQL query in GraphQL.
select
count(*) FILTER (WHERE interaction_type = 'clicked') as clicked,
count(*) FILTER (WHERE interaction_type = 'shown') as shown,
count(*) FILTER (WHERE interaction_type = 'dismissed') as dismissed
from ads.interaction group by ad_id
Disclosure: I'm a developer at Hasura.
At the moment, Hasura doesn't directly support what you want. However, we're currently thinking about adding group_by
support to aggregations that in the future will allow you to group by ad_id
, then interaction_type
, then apply a count aggregation. Please see this GitHub issue where we've put out an RFC.
For now, the easiest way to achieve what you want is to use the Native Queries feature. Native queries should allow you to take your desired SQL snippet and query it from GraphQL.
For example, you could use this parameterised SQL query in your Native Query:
select
ad_id,
count(*) FILTER (WHERE interaction_type = 'clicked') as clicked,
count(*) FILTER (WHERE interaction_type = 'shown') as shown,
count(*) FILTER (WHERE interaction_type = 'dismissed') as dismissed
from ads.interaction
where created_at >= {{created_since}}
group by ad_id
and then once that's set up as a Native Query named ads_interaction_summary
, you could query it from your GraphQL like so:
query {
ads_interaction_summary(
args: { created_since: "2023-06-22" }
where: { ad_id: {_eq: "7e9250bc-4f85-45ad-900d-f08558ebc775"} }
) {
clicked
shown
dismissed
}
}