Graphql-engine: Computed fields for relationship values

[oskery]

What I am attempting is to create a computed field that sums a value from table A with another value from table B, where A and B has a relationship. I have followed your example in the docs (https://hasura.io/docs/1.0/graphql/manual/schema/computed-fields.html#scalar-computed-fields) and succeded with summing two values for the same table, but not succeded with adding another nested value (nested in a GraphQL sense).

This is how I defined my current, working function:

CREATE FUNCTION avg_price(product_row product)
RETURNS int AS $$
  SELECT product_row.start_price + product_row.price
$$ LANGUAGE sql STABLE;

Now, I have a one-to-many relationship from product to product_deal and I would like to integrate a product_deal.price into my formula. When I do a normal query on product I can access the field product.deal (due to the relationship). I am imagining something like this for the computed value:

CREATE FUNCTION avg_price(product_row product)
RETURNS int AS $$
  SELECT product_row.start_price + (product_row.price || product_row.deal.price)
$$ LANGUAGE sql STABLE;

I am not a database expert so I guess my conditional clause is faulty, but hopefully you get the general idea. The problem is that I don't think that I can access this relational value. Is it even doable at this point? If so how can it be done?

[tirumaraiselvan]

Hi @oskery

You can get the relationship row in your function. E.g

CREATE FUNCTION avg_price(product_row product)
RETURNS int AS $$
DECLARE deal_row deal;
BEGIN
  -- get the related row into a variable
  SELECT * INTO deal_row FROM deal where deal.product_id = product_row.id; 
  SELECT product_row.start_price + (product_row.price || deal_row.price)
END;
$$ LANGUAGE plpgsql STABLE;

Note that you will have to convert this into a plpgsql function as it is declaring a variable. (Also, please check for any syntactic errors)

[oskery]

Thank you @tirumaraiselvan, this seems to be what I am looking for! I simplified the function in the question and now I have issues with my more complicated version. I would really appreciate your help or input! This is my complete function, trying to do what you suggested:

CREATE FUNCTION avg_price(product_row product)
RETURNS real AS $$
DECLARE deal_row product_deal;
BEGIN
  SELECT * INTO deal_row FROM product_deal WHERE product_deal.product_id = product_row.id AND product_deal.price IS NOT NULL;
  IF deal_row IS NOT NULL THEN
    SELECT product_row.start_price + (product_row.price * (12-deal_row.duration) + deal_row.price * deal_row.duration)/12.0;
  ELSE 
    SELECT product_row.start_price + product_row.price;
  END IF;
END;
$$ LANGUAGE plpgsql STABLE;

So in my database there exists products without deals, therefore I used the IF statement. There are also deals that are not price-deals, hence the additional WHERE condition. I think I am doing something wrong though, I can CREATE the above function but get an error when querying it:

"error": {
  "exec_status": "FatalError",
  "hint": "If you want to discard the results of a SELECT, use PERFORM instead.",
  "message": "query has no destination for result data",
  "status_code": "42601",
  "description": null
},

If I use PERFORM, as suggested, it gives me an error about the function being VOLATILE but I think the hasura functions for computed fields can only be STABLE or IMMUTABLE?

[tirumaraiselvan]

Hi @oskery

Turns out in plpgsql, you need to RETURN a value : https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

Try:

CREATE FUNCTION avg_price(product_row product)
RETURNS real AS $$
DECLARE deal_row product_deal;
BEGIN
  SELECT * INTO deal_row FROM product_deal WHERE product_deal.product_id = product_row.id AND product_deal.price IS NOT NULL;
  IF deal_row IS NOT NULL THEN
    RETURN product_row.start_price + (product_row.price * (12-deal_row.duration) + deal_row.price * deal_row.duration)/12.0;
  ELSE 
    RETURN product_row.start_price + product_row.price;
  END IF;
END;
$$ LANGUAGE plpgsql STABLE;

[oskery]

Thanks alot @tirumaraiselvan!

It now executes but avg_price is always the normal price. It seems as though the variable deal_row is always NULL and the ELSE clause is always executed, even though the WHERE clause should be true?:

 "data": {
    "product": [
      {
        "price": 95,
        "avg_price": 95,
        "deal": [
          {
            "price": 47.5,
            "product_id": 5056
          }
        ],
        "id": 5056
      }
    ]
}

I modified the IF-statments part to debug and it never returns 1.0:

IF deal_row IS NOT NULL THEN
  RETURN 1.0;
ELSE 
  RETURN product_row.start_price + product_row.price;
END IF;

[tirumaraiselvan]

@oskery Looking at https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW ,I think you cannot check for NULL value because it is a record type. You are probably getting something like (null, null, null) (pls check this as even I am not sure).

Try to check the FOUND variable instead:

IF FOUND THEN
  RETURN 1.0;
ELSE 
  RETURN product_row.start_price + product_row.price;
END IF;

I would also encourage you to explore the docs above and ask PG related questions in our community channel.

[oskery]

Thanks @tirumaraiselvan,

I apologize for asking somewhat un-hasura-related questions, but I have been struggling with finding answers and you were very helpful. I will definitely use the discord channel for PG related questions in the future, didn't know about it!

I saw the "FOUND" keyword in a similar question but I assumed it was a variable. Changing the "IS NOT NULL" to "FOUND" did indeed work! Thank you very much for your help. Closing the issue.

[tirumaraiselvan]

@oskery No problem. Happy to help.