0

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?

siniradam
  • 2,727
  • 26
  • 37
  • Can you please elaborate the exact query you're trying to accomplish? Maybe show an example of some GQL queries that are close to what you want to achieve but aren't quite working out. There's lots of good info in your question but its not completely clear from reading it what exactly you're trying to do and what roadblocks you're running into – Jesse Carter Jun 10 '22 at 16:42
  • @JesseCarter I've added a sample query and a procedure, I hope this helps me to clarify my question. – siniradam Jun 10 '22 at 20:30

1 Answers1

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!

Abraham Labkovsky
  • 1,771
  • 6
  • 12
  • Seems like this covers it, I've used permissions because I don't want the client-side to handle this since the query can be modified. I've gotta make some tests, then I'll accept as an answer. I've upvoted for now. I can't thank you enough. One more question, is this efficient performance-wise? – siniradam Jun 15 '22 at 16:57
  • Sure thing. Hasura certainly has a specific way to get stuff like this done. But there is usually a way... You are correct that this should not be done client-side. If these records should not be visible, they should not be accessible. With respect to performance, you want to make sure all relationships are either set up with DB level foreign keys or else manually create indexes on the fields used to relate tables. While hasura allows you to define relationships manually, these will be costly if the DB is not indexing the keys – Abraham Labkovsky Jun 15 '22 at 18:35