A computed field in Hasura that returns an array of integer

user_1673761 0
Viewed 868

The problem

I am trying to add computed field that returns an array of integer. Hasura currently supports return types of either SETOF <table-name> or BASE type. I tried returning an integer[] but it is failing (it is not a BASE type). Is there a work around ?

Here is What I tried

I have two tables: courses and quizzes, a course have many quizzes, each quiz have an integer field named difficulty. I want to add a computed field to the courses table that lists the distinct difficulties of the related quizzes.

With SQL we can create an array that produce this list with this query:

SELECT array(SELECT DISTINCT difficulty FROM quizzes
  WHERE course_id = 1 ORDER BY difficulty ASC);

So I create a function that returns the result of this query, and add it as a calculated field on the courses table

CREATE OR REPLACE FUNCTION courses_quiz_difficulties(course_row courses)
RETURNS integer[] AS $$
  SELECT array(SELECT DISTINCT difficulty FROM quizzes
  WHERE course_id = course_row.id ORDER BY difficulty ASC);
$$
LANGUAGE SQL STABLE;

When I try to add the computed field, I get an error because and array of int is not a BASE type.

in table "courses": in computed field "quiz_difficulties": the computed field "quiz_difficulties" cannot be added to table "courses" because the function "courses_quiz_difficulties" returning type _int4 is not a BASE type

Is there a work around to make it work with Hasura?

Here is the corresponding documentation:

https://hasura.io/docs/1.0/graphql/manual/schema/computed-fields.html#supported-sql-functions

hasura postgresql
1 Answers

A simple view gave me the result I wanted:

CREATE VIEW course_quiz_difficulties AS        
    SELECT 
        course_id, 
        array_agg(DISTINCT difficulty ORDER BY difficulty) as quiz_difficulties
    FROM quizzes GROUP BY course_id

After that we can manually add a relationship between courses and the new view: courses . id → course_quiz_difficulties . course_id

Hasura allows us to query quiz_difficulties from courses:

query MyCourseQuizDifficulties {
  courses(limit: 1) {
    id
    quiz_difficulties {
      quiz_difficulties
    }
  }
}

And we get this result:

{
  "data": {
    "courses": [
      {
        "id": 1,
        "quiz_difficulties": {
          "quiz_difficulties": [
            1,
            2
          ]
        }
      }
    ]
  }
}
user_16737610
Related