Filtering data based on columns in the result in hasura query

Question

I have two tables A and B.

A [ a_id, a_num]

B [ b_id, b_num, a_id ]

How can we write a single hasura query to fetch rows from B where b_num < a_num joining the table based on A.a_id = B.a_id?

Answer 1

What you essentially want is to compare two columns in a where-clause. That is not supported by hasura at the moment.

See this issue, which has been closed: https://github.com/hasura/graphql-engine/issues/1387

They suggest you to create a generated column, a view, or a native function. That does this for you.

Imo, creating a view that provides only A and B combinations where b_num is smaller than a_num is best suited for your usecase.

Here is an example on how to create a view, which is called filtered_a_b_combos:

CREATE OR REPLACE VIEW filtered_a_b_combos AS (
  SELECT A.a_id, B.b_id
  FROM A
  JOIN B ON A.a_id = B.a_id
  WHERE B.b_num < A.a_num
)