Help with GraphQL query for aggregating and ordering data

[LukasDeco]

Hi everyone,
I'm working on a GraphQL query that's not behaving as expected. I'm trying to aggregate data and order by the aggregate result. Here's an equivalent SQL query that works as intended:

SELECT entity_a.name, entity_b.id, SUM(entity_b.numeric_value)
FROM entity_b
JOIN entity_c ON entity_b.foreign_key_1 = entity_c.id
JOIN entity_d ON entity_c.foreign_key_2 = entity_d.id
JOIN entity_a ON entity_a.id = entity_d.foreign_key_3
WHERE entity_a.name = 'SpecificName'
GROUP BY entity_a.name, entity_b.id
ORDER BY SUM(entity_b.numeric_value) DESC;

I'm trying to achieve the same result with this GraphQL query:

query ($condition: entity_b_bool_exp) {
  entity_b_list {
    id
    aggregate_data(where: $condition) {
      aggregate {
        sum {
          numeric_value
        }
      }
    }
  }
}

With these variables:

{
  "condition": {
    "related_entity_1": {
      "related_entity_2": {
        "related_entity_3": {
          "identifier": {
            "_eq": "specific_value"
          }
        }
      }
    }
  }
}

The issue is that this GraphQL query isn't producing the same results as the SQL query. Specifically, I'm having trouble replicating the grouping and ordering by the sum of the numeric value.
How can I modify my GraphQL query to achieve the same result as the SQL query, including the grouping and ordering by the aggregate sum?
Any help or insights would be greatly appreciated. Thanks in advance!

[meetzaveri]

Hey @LukasDeco

Apologies for replying late. I read your use case and the SQL query you used for comparison.

For this use case, it seems like you can leverage Hasura's Native Query feature. Assume you just copy that SQL and define it as Native Query. Set Logical Model for it, which will act as schema for GraphQL API essentially exposing the native query to your GraphQL API (schema). I am not aware if you have already tried Hasura's Native Query feature but please let me know in case.

After this, native query would be automatically be part of your GraphQL API. This would unblock you atleast so you can move forward with your development.

Let me know if you have questions regarding this approach.