pre_get_posts filter meta_query without conflicting existing meta_query

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;

});