HOW to use aggregates results inside where clause Using Graphql.

[talivodworks]

I am trying to achieve a use case. I have 2 tables:

1. rooms
2. rooms_operating_hours

Here is the schema of both of the tables.

rooms

column	type
id	Int
name	text

rooms_operating_hours

column	type
id	Int
room_id	Int
day_of_week	text ( monday, tuesday, wednesday ...etc)
hour_start	timeonly
hour_end	timeonly

room hasMany rooms_operating_hours.

Sample data

room data

id	name
1	Room 1
2	Room 2

room_operating_hours data

id	room_id	day_of_week	hour_start	hour_end
1	1	monday	09:00:00+00:00	18:00:00+00:00
2	1	tuesday	09:00:00+00:00	18:00:00+00:00
3	1	wednesday	09:00:00+00:00	18:00:00+00:00
4	1	thursday	09:00:00+00:00	18:00:00+00:00
5	1	friday	09:00:00+00:00	18:00:00+00:00
6	2	monday	09:00:00+00:00	18:00:00+00:00
7	2	tuesday	09:00:00+00:00	18:00:00+00:00

Now I want to query the room which are available on monday, tuesday and wednesday through GraphQL. Postgres query something looks like this:

SELECT r.name, r.id FROM rooms AS r
WHERE r.id IN (
    SELECT roh.room_id
    FROM room_operating_hours AS roh
    WHERE (roh.day_of_week IN ('monday', 'tuesday', 'wednesday'))
    GROUP BY roh.room_id
    HAVING COUNT(roh.room_id) = 3
)

I have tried couple of graphql query but none of them work.

# This gets the Room 2 as well which i do not want.
query myRoomWithInOperatingHours1 {
  rooms {
    rooms_operating_hours(where: {
        day_of_week: { _in: [ 'monday', 'tuesday', 'wednesday']}
      })
  }
}

# I know this is silly but i tried that.
query myRoomWithInOperatingHours2 {
  rooms {
    rooms_operating_hours(where: {
        _and: [
        {
          day_of_week: { _eq: 'monday' }
        },
        {
          day_of_week: { _eq: 'tuesday' }
        },
        {
          day_of_week: { _eq: 'wednesday' }
        }
        ]
      })
  }
}

# this is same as in operator (myRoomWithInOperatingHours1)
query myRoomWithInOperatingHours3 {
  rooms {
    rooms_operating_hours(where: {
        _or: [
        {
          day_of_week: { _eq: 'monday' }
        },
        {
          day_of_week: { _eq: 'tuesday' }
        },
        {
          day_of_week: { _eq: 'wednesday' }
        }
        ]
      })
  }
}

I have been googling that and my findings are that:

1. Create Custom SQL function in hasura.

I am actually trying to find out what are the odds for not creating a custom SQL function as this use case is part of big complex query which is working fine with whatever hasura provides at this point. Doing Custom SQL would require me to do all the things that hasura is already doing.

Please help me with this. Thank You.

[tirumaraiselvan]

This is related: https://github.com/hasura/graphql-engine/issues/1498 but not complete for your use-case as it involves GROUP BY also.

One way is to create a view:

CREATE VIEW room_operating_hours_count AS
    SELECT roh.room_id, count(roh.room_id)
    FROM room_operating_hours AS roh
    WHERE (roh.day_of_week IN ('monday', 'tuesday', 'wednesday'))
    GROUP BY roh.room_id

Now you can filter on field like room_id and count.

[talivodworks]

@tirumaraiselvan I am sorry but I should have mentioned this in the Question that days are being decided on the basis of user selection. Like monday, tuesday. It could be all of the week days or just one week day, depending on the user selection. What do you suggest on that?

[tirumaraiselvan]

More discussion here: https://github.com/hasura/graphql-engine/issues/3478

I feel custom functions are the best bet here since group by is not explicitly supported in Hasura yet.

[talivodworks]

The way I get around this Use case is, I have created Materialized Views.

-- 
-- MATERIALIZED VIEW
-- 
CREATE MATERIALIZED VIEW monday_op_hrs_rooms as
SELECT * from public.room_operating_hours
WHERE day_of_week = 'monday';
CREATE UNIQUE INDEX ON monday_op_hrs_rooms (id);

CREATE MATERIALIZED VIEW tuesday_op_hrs_rooms as
SELECT * from public.room_operating_hours
WHERE day_of_week = 'tuesday' ;
CREATE UNIQUE INDEX ON tuesday_op_hrs_rooms (id);

CREATE MATERIALIZED VIEW wednesday_op_hrs_rooms as
SELECT * from public.room_operating_hours
WHERE day_of_week = 'wednesday';
CREATE UNIQUE INDEX ON wednesday_op_hrs_rooms (id);

CREATE MATERIALIZED VIEW thursday_op_hrs_rooms as
SELECT * from public.room_operating_hours
WHERE day_of_week = 'thursday';
CREATE UNIQUE INDEX ON thursday_op_hrs_rooms (id);

CREATE MATERIALIZED VIEW friday_op_hrs_rooms as
SELECT * from public.room_operating_hours
WHERE day_of_week = 'friday';
CREATE UNIQUE INDEX ON friday_op_hrs_rooms (id);

CREATE MATERIALIZED VIEW saturday_op_hrs_rooms as
SELECT * from public.room_operating_hours
WHERE day_of_week = 'saturday';
CREATE UNIQUE INDEX ON saturday_op_hrs_rooms (id);

CREATE MATERIALIZED VIEW sunday_op_hrs_rooms as
SELECT * from public.room_operating_hours
WHERE day_of_week = 'sunday';
CREATE UNIQUE INDEX ON sunday_op_hrs_rooms (id);

--
-- Trigger TO UPDATE THE MATERIALIZED VIEW
--

CREATE OR REPLACE FUNCTION refresh_operating_hours_materialized_views()
  RETURNS trigger AS $BODY$
  BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY monday_op_hrs_rooms;
    REFRESH MATERIALIZED VIEW CONCURRENTLY tuesday_op_hrs_rooms;
    REFRESH MATERIALIZED VIEW CONCURRENTLY wednesday_op_hrs_rooms;
    REFRESH MATERIALIZED VIEW CONCURRENTLY thursday_op_hrs_rooms;
    REFRESH MATERIALIZED VIEW CONCURRENTLY friday_op_hrs_rooms;
    REFRESH MATERIALIZED VIEW CONCURRENTLY saturday_op_hrs_rooms;
    REFRESH MATERIALIZED VIEW CONCURRENTLY sunday_op_hrs_rooms;
  RETURN NULL;
  END;
$BODY$ LANGUAGE plpgsql;

CREATE TRIGGER update_operating_hours_materialized_views 
  AFTER INSERT OR 
  UPDATE OR 
  DELETE 
  ON "room_operating_hours" 
  FOR EACH ROW EXECUTE PROCEDURE refresh_operating_hours_materialized_views();

Then I added the Object relationship between room and MATERIALIZED views. This way I can specify my where clause like this:

query roomOnMondayTuesdayWednesday {
  rooms(
    where: {
      # Coming Form Hasura relationship between room AND (monday_op_hrs_rooms, tuesday_op_hrs_rooms, wednesday_op_hrs_rooms)
      monday_hours: {
        hour_start: { _lte: "09:00:00+00" },
        hour_end: { _gte: "19:00:00+00" }
      }
      tuesday_hours: {
        hour_start: { _lte: "09:00:00+00" },
        hour_end: { _gte: "19:00:00+00" }
      }
      wednesday_hours: {
        hour_start: { _lte: "09:00:00+00" },
        hour_end: { _gte: "19:00:00+00" }
      }
    }
  ) {
    room_operating_hours{
      day_of_week
    }
  }
}

query roomOnSaturday {
  rooms(
    where: {
      # Coming Form Hasura relationship between room AND saturday_op_hrs_rooms
      saturday_hours: {
        hour_start: { _lte: "09:00:00+00" },
        hour_end: { _gte: "19:00:00+00" }
      }
    }
  ) {
    room_operating_hours{
      day_of_week
    }
  }
}

Now I can leverage the Hasura's out of the box provided features as well without creating a custom function.

Few things to note about MATERIALIZED VIEWS

• You have to create a trigger to refresh these views. As they are physically saved on the disk and not created on the fly.
• You may have noticed the keyword CONCURRENTLY in the trigger function up there. It requires Postgres version 9.4. It allows you to select the rows even database is running Update operation concurrently. More Details here.
• Materialized Views requires a Unique index. Do not forget to create one.

Hope this will help someone. Happy Coding. 🙂