I have a custom post type called "resource" where files are uploaded by admins and made visible to selected subscribers. I'm using Advanced Custom Fields (ACF) to create a field called "visible_to", and this field allows the admin to indicate which subscribers get to see the post. The user name is displayed to admin and you can select 1 subscriber, many, or none for all subscribers to see the post. The selection gets stored as an array of user_id in wp_postmeta table.
In WP-Admin for the custom post type "resource", I'd like to use a similar dropdown list filter for admin to select the user name, click filter, and return a list of posts that are "visible_to" the selection or visible to all. So far I have successfully added a custom column, a dropdown list, and I have a query to return results. But I have 2 problems with my query.
Screenshot of the custom post type, dropdown list filter (All Visible To), and custom column (Visible To):
My code is based on the closest solution I could find here: Filter by custom field in custom post type on admin page
Problem: My problem revolves around my lack of understanding $query and parse_query filter. It is two-fold:
- The $query in the code I'm using is pulling in the meta_value for "visible_to", which is an array of ID values. This is not user friendly. I want a list that shows the user name for each subscriber role, just like the first screenshot I posted above.
- I've tried changing $query to return user_login from wp_users but I cannot join wp_users to wp_posts. Neither of my $query attempts have an effect on the results. All posts are returned, regardless of the list item selected.
Any help is much appreciated.
My code below.
add_action('restrict_manage_posts', 'myehs_visible_to_dropdown');
function myehs_visible_to_dropdown($post_type){
global $wpdb;
//Only add dropdown to the appropriate Custom Post Types
if( $post_type !== 'resource' && $post_type !== 'manual' && $post_type !== 'news')
return;
//Grab data for the dropdown from the DB
$query = $wpdb->prepare('
SELECT DISTINCT pm.meta_value FROM %1$s pm
LEFT JOIN %2$s p ON p.ID = pm.post_id
WHERE pm.meta_key = "%3$s"
AND p.post_status = "%4$s"
AND p.post_type = "%5$s"
ORDER BY "%3$s"',
$wpdb->postmeta,
$wpdb->posts,
'visible_to', // Meta key
'publish',
$post_type,
'visible_to'
);
$results = $wpdb->get_col($query);
//Ensure there are options to show
if(empty($results))
return;
//Get selected option if there is one selected
if (isset( $_GET['visible'] ) && $_GET['visible'] != '') {
$selectedName = $_GET['visible'];
} else {
$selectedName = -1;
}
//Grab all of the options that should be shown
$options[] = sprintf('<option value="-1">%1$s</option>', __('All Visible To'));
foreach($results as $result) :
if ($result == $selectedName) {
$options[] = sprintf('<option value="%1$s" selected>%2$s</option>', esc_attr($result), $result);
} else {
$options[] = sprintf('<option value="%1$s">%2$s</option>', esc_attr($result), $result);
}
endforeach;
//Output the dropdown menu
echo '<select class="" id="visible" name="visible">';
echo join("\n", $options);
echo '</select>';
}
// Make selected dropdown return results
add_filter( 'parse_query', 'myehs_filter_by_visible_to' );
function myehs_filter_by_visible_to($query) {
global $pagenow;
$current_page = isset( $_GET['post_type'] ) ? $_GET['post_type'] : '';
// If we are in Admin and on the right post type and dropdown selection is made
if ( is_admin() &&
$pagenow == 'edit.php' &&
$current_page == array('resource','manual','news') &&
isset( $_GET['visible'] ) && $_GET['visible'] !='' &&
$query->is_main_query())
// Return only posts where visible to matches the selection
{
$query->query_vars['meta_key'] = 'visible_to';
$query->query_vars['meta_value'] = $_GET['visible'];
$query->query_vars['meta_compare'] = '=';
}
}
