pre_get_posts filter meta_query without conflicting existing meta_query

I have a meta_key on all posts that is designed to check the current user role and only return the post if the user has an acceptable user role. The problem I am running into is that my meta_query declared in pre_get_posts is conflicting with existing meta queries I have setup throughout the site. Am I doing something wrong, is this not possible or there an alternative solution to my query?

add_action('pre_get_posts', function ($query) {

    // dont filter admin posts
    if(is_admin()) return $query;

    // get required globals
    global $current_user;

    // create our not in array
    $not_in = array (
        'public'    => array ('media', 'sponsor', 'super'),
        'media'     => array ('sponsor', 'super'),
        'sponsor'   => array ('super'),
        'super'     => array (),
    );

    // determine the users role
    $role = $current_user->roles;
    $role = array_key_exists(0, $role) ? $role[0] : 'public';
    if(current_user_can('edit_posts')) $role="super";

    // determine the users post visibility
    switch($role) {
        case 'media':
        case 'sponsor':
            $posts_visibility = $role;
            break;

        case 'super':
        case 'editor':
        case 'administrator':
            $posts_visibility = 'super';
            break;

        default:
            $posts_visibility = 'public';
            break;
    }

    // get existing meta query
    $meta_query = $query->get('meta_query');

    // add our new meta_query data
    $meta_query[] = array (
        'relation'  => 'OR',
        array (
            'key'       => 'restricted_visibility',
            'value'     => $not_in[$posts_visibility],
            'compare'   => 'NOT IN',
        ),
        array (
            'key'       => 'restricted_visibility',
            'compare'   => 'NOT EXISTS',
        ),
    );


    // update to our new meta query
    $query->set('meta_query', $meta_query);

    // return our query
    return $query;

});

Now I know that the meta query works on its own but it seems to conflict with other meta queries or not work at all when trying to combine existing and new queries together.

OK so having been told that I cannot mix AND/OR conditions, I changed to use the posts_where filter. The code is exactly the same as above right until the point I set the meta query. Then I use this –

add_filter('posts_where', function ($where) {

    // dont filter admin posts
        if(is_admin()) return $where;

    // get required globals
    global $current_user;

    // create our not in array
    $not_in = array (
        'public'    => array ('media', 'sponsor', 'super'),
        'media'     => array ('sponsor', 'super'),
        'sponsor'   => array ('super'),
        'super'     => array (),
    );

    // determine the users role
    $role = $current_user->roles;
    $role = array_key_exists(0, $role) ? $role[0] : 'public';
    if(current_user_can('edit_posts')) $role="super";

    // determine the users post visibility
    switch($role) {
        case 'media':
        case 'sponsor':
            $posts_visibility = $role;
            break;

        case 'super':
        case 'editor':
        case 'administrator':
            $posts_visibility = 'super';
            break;

        default:
            $posts_visibility = 'public';
            break;
    }

    if(count($not_in[$posts_visibility]) > 0) {
        // join postmeta so we can query it
        $this->filter('posts_join', function ($join) {

            // get our global
            global $wpdb;

            // create our join and return it
            $join .= sprintf(' LEFT JOIN %1$s ON %2$s.ID = %1$s.post_id ', $wpdb->postmeta, $wpdb->posts);

            return $join;
        });

        // build our query
        $where .= sprintf(
            ' AND (( NOT EXISTS (SELECT * FROM %1$s WHERE (%1$s.post_id = %2$s.ID) AND %1$s.meta_key = "restricted_visibility")' .
            ' OR ( %1$s.meta_key = "restricted_visibility" AND %1$s.meta_value NOT IN (%3$s) ))) GROUP BY %2$s.ID',
            $wpdb->postmeta,
            $wpdb->posts,
            '\'' . implode('\', \'', $not_in[$posts_visibility]) . '\''
        );
    }

    // return our query
    return $where;
});

The problem I am having with the above snippet is that it seems to cause a lot of unexpected issues. Queries taking a long time and many other unexpected issues. I have had to join the postmeta table so I can query it.

I look forward to your thoughts,
Chris.

OK,

After a lot of trial and error and asking around I managed to finally get a working solution. Thought I would post it here for those that are trying to fix a similar problem in the future.

Filter 1: posts_join creates a left join on all queries to link up the postmeta table. Its important to note that I have created the postmeta table with an alias of cpm1 as this is the whole bind that makes everything work further down the line. Otherwise our custom query will still conflict with other meta queries.

// create our posts join filter
add_filter('posts_join', function ($join) {

    // get our global
    global $wpdb;

    // create our join complete with alias and return it
    return $join . sprintf(" LEFT JOIN %1\$s AS cpm1 ON (%2\$s.ID = cpm1.post_id AND cpm1.meta_key = 'META_KEY_HERE') ", $wpdb->postmeta, $wpdb->posts);

});

Now we have the join, when querying for posts we can add the custom part of our query. Note that the query references our left joined table (cpm1) rather than directly referencing the postmeta table.

// create our posts_where filter to make use of our join and exclude certain posts
add_filter('posts_where', function ($where) {

    // dont filter admin posts
    if(is_admin()) return $where;

    // get required globals
    global
        $current_user;

    // create our not in array
    $not_in = array (
        'public'    => array ('media', 'sponsor', 'super'),
        'media'     => array ('sponsor', 'super'),
        'sponsor'   => array ('super'),
        'super'     => array (),
    );

    // determine the users role
    $role = $current_user->roles;
    $role = array_key_exists(0, $role) ? $role[0] : 'public';
    if(current_user_can('edit_posts')) $role="super";


    // determine the users post visibility
    switch($role) {
        case 'media':
        case 'sponsor':
            $posts_visibility = $role;
            break;

        case 'super':
        case 'editor':
        case 'administrator':
            $posts_visibility = 'super';
            break;

        default:
            $posts_visibility = 'public';
            break;
    }

    // only apply our custom query if we have legitimate conditions to be met
    if(count($not_in[$posts_visibility]) > 0) {

        // build our query
        $where .= sprintf(
            " AND (( cpm1.meta_key = 'META_KEY_HERE' AND cpm1.meta_value NOT IN (%1\$s)) OR cpm1.meta_id IS NULL ) ",
            "'" . implode("', '", $not_in[$posts_visibility]) . "'"
        );
    }

    // return our query
    return $where;

});