2

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.

2
  • I assume you want to do an AND relation with the existing meta query, but that means you can't also do an OR between your two restricted_visibility cases. the only way to handle that is to directly modify the SQL via filters.
    – Milo
    Commented Nov 19, 2014 at 18:25
  • @Milo Thanks, as I suspected. Frustrating as I did try writing queries to achieve this using the posts_where filter but it was causing problems. I think I will expand my answer with these queries I have used.
    – Chris
    Commented Nov 19, 2014 at 20:36

1 Answer 1

0

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;

});

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.