How to set filtered array relationship in Hasura based on a different table

Question

I'm trying to filter results by referencing another table in Hasura. But I can't wrap my head around it. Can it be done with functions or views or it isn't possible?

Here are my tables;

Providers

id	name
1	Epic
2	EA
3	Valve

Games

id	name
10	Half Life
11	Counter Strike
12	Apex Legends
13	Fifa

Sites

id	name	providers	categories	users
20	Playnow.com	array relatioship	array relatioship	array relatioship
21	play.com

Users

id	username	site_id
30	tester 1	20
31	tester 2	21

Categories

id	title	site_id	games
40	FPS	20	array relatioship
41	Sport	21	array relatioship

Additional to these tables, I have relationship tables in order to create array relationships.

• provider_games - Relationship between providers and games.
• • provider_id,
• • game_id
• site_providers - Relationship between sites and providers. (Usable providers for the site)
• • site_id,
• • provider_id
• game_category - Which games belong to which categories.
• • game_id,
• • category_id
• user_banned_games - This table is a list of game ids a user does not allowed to play.
• • game_id,
• • user_id
• site_disabled_games - This table contains games that site owner doesn't want their users to play.
• • site_id,
• • game_id

I have a global providers list, some of these providers are accessible to some sites, once a provider is available to the site all games are accessible by its users. But at some point, the site owner might want to disable a specific game for its users or just for a specific user.

A user can browse games by providers or categories. I can make relationships with Hasura's default relationship system but I can't add banned or disabled games into the equation.

Side note: I have got the user's site id in its JWT token.

Below there is a sample query, but what I need is "_nin" value should be a collection of ids fetched from site_disabled_games and user_banned_games by the user's site_id. The client's side doesn't know which IDs are blocked. Even if they do know, that wouldn't be safe I think.

query getCategoriesWithGames {
  getCategories {
    id
    name
    shortname
    site_id
    isActive
    games(where: {game_id: {_nin: 10}}, limit: 10) {
      game {
        name
        provider {
          name
        }
      }
    }
  }
}

Hasura limits this query to the user's site_id through permissions as intended. But of course can be limited manually with getCategories(where: {site_id: {_eq: "UUID"}}

I've tried creating this function, but it says its VOLATILE.

CREATE OR REPLACE FUNCTION public.available_games(category_row gamecategories)
 RETURNS SETOF games
 LANGUAGE sql
AS $function$
SELECT * FROM games WHERE id NOT IN (SELECT id FROM site_bannedgames WHERE site_id=category_row.site_id)
$function$

The documentation mentions argument modes: only IN, does this mean I can't use "NOT IN" in my query?

Answer 1

You are thinking about this problem the wrong way around. You don't need to search for the game ids and supply them to your query. You just need to relate them.

You already have an array relationship user_banned_games which has both a user_id and a game_id. So presumably games can have an array relation of banned child user's in the same way that user's can have array relation of child banned games.

So you can easily filter in query for:

games (where: {_not: {banned_users: {user_id: {_eq: $user_id}}}})

or permissions:

{_not: {banned_users: {user_id: {_eq: x-hasura-user-id}}}}

It is late at night here for me and I think I got what you are trying to do.

Let me know if this set you on the right path or if I am missing something.

Best of luck!