Hasura: How to query counts of records from multiple related tables

Question

So I have create this query for my GraphiQL in Hasura, This will get all of the users data and it aims to include the total amount/count of the other data from analytics and follow table where user's id is equal to user_id from the analytics and follow table

query getAllUsersData {
  users {
    id
    username
    bio
    first_name
    last_name
    banner
    profile_image_url
    created_at
    followers: follow_aggregate(where: {user_id: {_eq: id}, is_follow: {_eq: 1}}) {
        aggregate {
          count: count
        }
      }
      visits: analytics_aggregate(where: {user_id: {_eq: id}, type: {_eq: "visit"}}) {
        aggregate {
          count: count
        }
      }
      shares: analytics_aggregate(where: {user_id: {_eq: id}, type: {_eq: "share"}}) {
        aggregate {
          count: count
        }
      }
  }
}

The problem here is I wont be able to run it because of the follow_aggregate and analytics_aggregate, It gives me an error of Cannot query field "follow_aggragate" and "analytics_aggregate" on type "users".

I am trying this thru Hasura's project dashboard, I have check already the documentations of this from Hasura and I am still really lacking. Hoping someone would guide me into this, Would really appreciate thank you in advance.

I have already done my research, Look for steps of aggregation and also look for other methods.

What I am trying to output is:

{
users:
{
userdatahere..
followersinthere,
visitsinthere,
sharesinthere
},
{
userdatahere..
followersinthere,
visitsinthere,
sharesinthere
},
}

Or let me give you a quick structure but SQL:

SELECT u.id, u.username, u.bio, u.first_name, u.last_name, u.banner, u.profile_image_url, u.created_at,
       COUNT(f.user_id) AS followers,
       SUM(CASE WHEN a.type = 'visit' THEN 1 ELSE 0 END) AS visits,
       SUM(CASE WHEN a.type = 'share' THEN 1 ELSE 0 END) AS shares
FROM user u
LEFT JOIN follow f ON u.id = f.user_id
LEFT JOIN analytics a ON u.id = a.user_id
GROUP BY u.id

Update 1/8/2023: I have also tried this method (which is array relationship):

1. Database Table Users relationship to other tables
2. Here's the column of the users table
3. And this is the output users table + links table The only problem now is how can I implement it with schema graphiql

Answer: Preview Output

1. I setup foreign keys
2. Tracked the Untracked foreign-key relationships
3. and it created array of relationship
4. The I use my schema again but from follow to follows

query getAllUsersData {
 users {
   id
   username
   bio
   first_name
   last_name
   banner
   profile_image_url
   created_at
   followers: follows_aggregate(where: {is_follow: {_eq: 1}}) {
       aggregate {
         count: count
       }
     }
     visits: analytics_aggregate(where: {type: {_eq: "visit"}}) {
       aggregate {
         count: count
       }
     }
     shares: analytics_aggregate(where: {type: {_eq: "share"}}) {
       aggregate {
         count: count
       }
     }
 }
}

Thanks to spatialaustin

Answer 1

Are you sure that the follow and analytics relationships are properly configured in your DB and tracked by Hasura? You can validate by looking at the "relationships" tab of the users table.

You also need to configure Hasura's permissions to allow aggregate queries (docs).

If you're using Hasura Cloud, you might want to double-check that you're allowing queries of a depth of at least 2 (docs).

The easiest way to check all of this is to build your query in the Hasura console's GraphIQL editor, which will show you which entities are available in your queries.

Lastly, if your relationships are indeed configured correctly, you should not need to include a foreign key in your query's where clauses—this is implied through the query structure.

  query getAllUsersData {
    users {
      id
      username
      bio
      first_name
      last_name
      banner
      profile_image_url
      created_at
      followers: follow_aggregate {
        aggregate {
          count: count
        }
      }
      visits: analytics_aggregate(where: { type: { _eq: "visit" } }) {
        aggregate {
          count: count
        }
      }
      shares: analytics_aggregate(where: { type: { _eq: "share" } }) {
        aggregate {
          count: count
        }
      }
    }
  }

I hope that helps!