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;
});