Custom posts_where query to restrict post visibility WordPress

• SOLVED

I have a meta_key on all posts that is set to restrict certain posts to certain user roles and only return the post if the user is logged in and has an acceptable user role. I have written a custom query to do this with a posts_where filter but it doesn't seem to be playing ball, the query is returning results but are having issues such as slow load time and other unexpected problems.

I think I am almost there just can't quite perfect it! I'm also not sure if joining the 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;
}

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

Thanks!

Answers (1)

2014-11-25

Hariprasad Vijayan answers:

Hello,

Try the following,

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);*/
$join .= sprintf(' LEFT JOIN %1$s ON (%2$s.ID = %1$s.post_id AND %1$s.meta_key = "restricted_visibility") ', $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]) . '\''
);*/
$where .= sprintf(
' AND (( %1$s.meta_key = \'restricted_visibility\' AND %1$s.meta_value NOT IN (%2$s)) OR %1$s.meta_id IS NULL ) ',
$wpdb->postmeta,
'\'' . implode('\', \'', $not_in[$posts_visibility]) . '\''
);
}
// return our query
return $where;
});


Added two changes in the code (on $join and $where). Code not tested, please check it an let me know.

Regards,
Hariprasad

---

Josh Cranwell comments:

Thanks, seems to be working! We will test it for a day or so and mark your answer as correct if we don't run into any issues!

---

Hariprasad Vijayan comments:

Okay.

Thanks,

---

Josh Cranwell comments:

Thanks Hariprasad - it worked nicely. The only thing we did have to do was take the 'posts_join' filter out of the 'posts_where' action because it was causing the join to be created twice when running it multiple times on a page.

---

Josh Cranwell comments:

Thanks Hariprasad - it worked nicely. The only thing we did have to do was take the 'posts_join' filter out of the 'posts_where' action because it was causing the join to be created twice when running it multiple times on a page.

---

Josh Cranwell comments:

@Hariprasad - we have run into an issue with this code when we are running a meta_query in conjunction and the restricted_visibility key doesn't exist. Is there any way I can put conditional logic on the JOIN to say if the meta_key = 'restricted_visibility' or this meta key doesn't exist?