How to implement a "none" or "not any" filter?

Questions and Answers
1
System Information
  • Strapi Version: 4.1.8
  • Operating System: Strapi Docker on Windows WSL2
  • Database: MariaDB 10.3
  • Node Version: 14.16.0
  • NPM Version: 7.20.5
  • Yarn Version: 1.22.10

I’m trying to implement a filter on my UsersPermissionsUsers graphql endpoint, where any user who has any(at least one) item in the related content type matching a set of criteria will not be included in the result.

Example:

const result = await strapi.entityService.findMany('plugin::users-permissions.user', {
    filters: {
        $not: {
            user_relations_to: {
                from_user: parent.id,
                relation_status: { $in: ['denied', 'ignored', 'blocked'] }
            }
        }
    }
});

The way that I would expect this query to work is that if there is a single user_relations_to with from_user being the parent(authenticated user) AND relation_status being any of those values, that user will not be included in result. In practice this is not what I have experienced-- The users who have one or more user_relations_to matching those criteria are still included in the response, which is not what I want.

Some way I might imagine an alternative to look would be as follows:

//notice the "none" filter keyword
const result = await strapi.entityService.findMany('plugin::users-permissions.user', {
    filters: {
        $none: {
            user_relations_to: {
                from_user: parent.id,
                relation_status: { $in: ['denied', 'ignored', 'blocked'] }
            }
        }
    }
});

//notice the "not" and "any" filter keyword
const result = await strapi.entityService.findMany('plugin::users-permissions.user', {
    filters: {
        $not: {
            $any: {
                user_relations_to: {
                    from_user: parent.id,
                    relation_status: { $in: ['denied', 'ignored', 'blocked'] }
                }
            }
        }
    }
});

Strapi does not include any keywords of this variety. I’m wondering if there’s a way to achieve the query I’m looking for without having to write the SQL myself.

Cheers!

2

So if you are curious to see the knex query as a better way to explain what I’m going for with the graphQL query here’s what I came up with. I guess the correct keyword(s) I was looking for is havingNotExists. Anyway I’m still curious to see if there’s a way to do this with the graphQL query as that’ll be helpful in the future.

P.S. I wrote this in a resolver under the me query to have access to the parent.id, alternatively you could get the current authenticated user’s id from ctx.state.user.id

const result = await strapi.db.connection('up_users')
    .havingNotExists(function () {
        this
            .from('user_relations_from_user_links')
            .where('user_relations_from_user_links.user_id', parent.id)
            .join('user_relations', 'user_relations_from_user_links.user_relation_id', 'user_relations.id')
            .whereIn('user_relations.relation_status', ['requested', 'blocked', 'ignored'])
            .join('user_relations_to_user_links', 'user_relations.id', 'user_relations_to_user_links.user_relation_id')
            .whereRaw('up_users.id = user_relations_to_user_links.user_id')
    })