CODEWITHSUNDEEP
graphqlhasura
Lostsoul
Lostsoul

Reputation: 26017

Is there a way to compare each item to a aggreated value?

I'm new to graphQL and Hasura. I'm trying(in Hasura) to let me users provide custom aggregation (ideally in the form of a normal graphQL query) and have then each item the results compared against the aggreation.

Here's a example. Assume I have this schema:

USERTABLE:
userID
Name
Age
City
Country
Gender
HairColor

INCOMETABLE:
userID
Income

I created a relationship in hasura and I can query the data but my users want to do custom scoring of users' income level. For example, one user may want to query the data broken down by country and gender.

For the first example the result maybe:

{Country : Canada
 { gender : female
  { userID: 1, 
    Name: Nancy Smith,..
    #data below is on aggregated results
    rank: 1
    %fromAverage: 35%
  }...

Where I'm struggling is the data showing the users info relative to the aggregated data.

for Rank, I get the order by sorting but I'm not sure how to display the relative ranking and for the %fromAverage, I'm not sure how to do it at all.

Is there a way to do this in Hasura? I suspected that actions might be able to do this but I'm not sure.

Upvotes: 0

Views: 502

Answers (1)

Abraham Labkovsky
Abraham Labkovsky

Reputation: 1956

You can use track a Postgres view. Your view would have as many fields as you'd like calculated in SQL and tracked as a separate "table" on your graphql api.

I am giving examples below based on a simplification where you have just table called contacts with just a single field called: id which is an auto-integer. I am just adding the id of the current contact to the avg(id) (a useless endeavor to be sure; just to illustrate...). Obviously you can customize the logic to your liking.

A simple implementation of a view would look like this (make sure to hit 'track this' in hasura:

CREATE OR REPLACE VIEW contact_with_custom AS
SELECT id, (SELECT AVG(ID) FROM contacts) + id as custom FROM contacts;

See Extend with views

Another option is to use a computed field. This is just a postgres function that takes a row as an argument and returns some data and it just adds a new field to your existing 'table' in the Graphql API that is the return value of said function. (you don't 'track this' function; once created in the SQL section of Hasura, you add it as a 'computed field' under 'Modify' for the relevant table) Important to note that this option does not allow you to filter by this computed function, whereas in a view, all fields are filterable.

In the same schema mentioned above, a function for a computed field would look like this:

CREATE OR REPLACE FUNCTION custom(contact contacts)
RETURNS Numeric AS $$
    SELECT (SELECT AVG(ID) from contacts ) + contact.id
$$ LANGUAGE sql STABLE;

Then you select this function for your computed field, naming it whatever you'd like...

See Computed fields

Upvotes: 2

Related Questions