I've got these tables:

Table blocked_users
id (pk)  | initiator_id (fk) | target_id (fk)
1        | a                 | b 


Table post
id (pk)  | author_profile (fk)
1        | a               
2        | b
3        | c
4        | d

I'm trying to get my head around the proper select permissions, I've tried many combinations and I cannot get the desired result -> Get the posts and exclude the users that either I have blocked or they have blocked me.

This is one of the many permissions I've tried:

blocked_by_me is blocked_users.initiator_id → author_profile.id

been_blocked_by is blocked_users.target_id → author_profile.id

{
  "_and": [
    { "deleted_at": { "_is_null": true } },
    { "author_profile": { "deleted_at": { "_is_null": true } } },
    {
      "_and": [
        {
          "author_profile": {
            "been_blocked_by": {
              "initiator_id": {
                "_eq": "X-Hasura-User-Id"
              }
            }
          }
        },
        {
          "author_profile": {
            "blocked_by_me": {
              "initiator_id": {
                "_eq": "X-Hasura-User-Id"
              }
            }
          }
        }
      ]
    }
  ]
}

And one of the many queries I tried: (with no permissions on post)

query GetPosts(
  $created_at: order_by = desc
  $limit: Int! = 12
  $offset: Int! = 0
) {
  post(
    limit: $limit
    offset: $offset
    order_by: { created_at: $created_at }
    where: {
      _not: {
        author_profile: {
          _or: [
            {
              been_blocked_by: { initiator_id: { _eq: "a" } }
              blocked_by_me: { target_id: { _neq: "b" } }
            }
          ]
        }
      }
      _and: {
        deleted_at: { _is_null: true }
        author_profile: { deleted_at: { _is_null: true } }
      }
    }
  ) {
    author_profile {
      id
      first_name
    }
  }
}

With the query above, if a user has made a post and that user's id does not exist on blocked_users then the post of that user won't be returned by the query, in other words, the query returns only the posts of the users that have at least one record on blocked_users (not blocked by me or that user have not blocked me).

CodePudding user response：

Get the posts and exclude the users that either I have blocked or they have blocked me.

You want your select permission exclude the posts from users who either blocked you or are blocked by you. Using the same array relationships that you have, we can write a permission for post like:

{
  "_not": {
    "_or": [
      {
        "author_profile": {
          "been_blocked_by": { "initiator_id": { "_eq": "X-Hasura-User-Id" } }
        }
      },
      {
        "author_profile": {
          "blocked_by_me": { "target_id": { "_eq": "X-Hasura-User-Id" } }
        }
      }
    ]
  }
}

We are basically telling Hasura to only show the posts that are _not by authors who have been_blocked_by us _or by authors who blocked us.