search - Wordpress, fetching users with an exact match in a string of comma separated values in user_meta

admin管理员组

文章数量:1307109

I have data saved in user_meta -> areapref field. It is basically a string of comma delimited post IDs (e.g. 2, 37, 200, 300 etc.)

I need to check if the user has a particular ID in this field. If so... do stuff.

I can check if an ID is in areapref using LIKE. But I believe this has an issue. If one location is ID=10, and another is ID=100 - both will match with 1, or 10? Or am I misunderstanding LIKE. I fear CONTAIN would have the same issue.

I need to know how to get a list of users who have an 'exact match' within the areapref string? So search for ID 2, will return only 2, NOT 22, or 20, 200 etc. I've spent hours going through stackexchange/overflow/wp codex and cannot find an answer... though I am dense!

Here's enough code I hope to show what I'm currently doing... any ideas greatly appreciated.

function ds_notify_new_event( $post_ID ){
    $url = get_permalink( $post_ID );
    $event = get_the_title( $post_ID );
    $localeID = get_post_field( 'location_id', $post_ID);
    global $wpdb;
    $locale = $wpdb->get_var( $wpdb->prepare(
    " SELECT post_id FROM {$wpdb->prefix}em_locations WHERE ID = %d ",
    $localeID ) );
    
    $args = array(
            'role'    => 'subscriber',
            'meta_query' => array(
                    array( 'key' => 'areapref', 'value' => $locale, 'compare' => 'LIKE' ),
                    // just checks usr is ok with email
                    array( 'key' => 'notify', 'value' => 'yes', 'compare' => '=' ) 
                ),
            'fields' => array( 'display_name', 'user_email' )
        );
    // retrieve users to notify about the new post
    $users = get_users( $args );
// do stuff with users

I have data saved in user_meta -> areapref field. It is basically a string of comma delimited post IDs (e.g. 2, 37, 200, 300 etc.)

I need to check if the user has a particular ID in this field. If so... do stuff.

I can check if an ID is in areapref using LIKE. But I believe this has an issue. If one location is ID=10, and another is ID=100 - both will match with 1, or 10? Or am I misunderstanding LIKE. I fear CONTAIN would have the same issue.

I need to know how to get a list of users who have an 'exact match' within the areapref string? So search for ID 2, will return only 2, NOT 22, or 20, 200 etc. I've spent hours going through stackexchange/overflow/wp codex and cannot find an answer... though I am dense!

Here's enough code I hope to show what I'm currently doing... any ideas greatly appreciated.

function ds_notify_new_event( $post_ID ){
    $url = get_permalink( $post_ID );
    $event = get_the_title( $post_ID );
    $localeID = get_post_field( 'location_id', $post_ID);
    global $wpdb;
    $locale = $wpdb->get_var( $wpdb->prepare(
    " SELECT post_id FROM {$wpdb->prefix}em_locations WHERE ID = %d ",
    $localeID ) );
    
    $args = array(
            'role'    => 'subscriber',
            'meta_query' => array(
                    array( 'key' => 'areapref', 'value' => $locale, 'compare' => 'LIKE' ),
                    // just checks usr is ok with email
                    array( 'key' => 'notify', 'value' => 'yes', 'compare' => '=' ) 
                ),
            'fields' => array( 'display_name', 'user_email' )
        );
    // retrieve users to notify about the new post
    $users = get_users( $args );
// do stuff with users

• search
• meta-query
• user-meta

Share Improve this question edited Jan 16, 2021 at 19:03 Tom J Nowell♦ 61k77 gold badges7979 silver badges148148 bronze badges asked Jan 16, 2021 at 18:45 Mike SmithMike Smith 1322 bronze badges 2

• 1 You can have multiple user meta with the same key, you don't have to store all those values as a comma separated list, that's what the 3rd parameter of get_user_meta and all the other get_xxxx_meta functions is for. If you had done it that way then it would be a trivial WP_User_Query check. Instead you've got a can of worms trying to solve a problem that can never be fully solved – Tom J Nowell ♦ Commented Jan 16, 2021 at 18:53
• 1 Also keep in mind that meta_query is very slow/expensive, and taxonomies can be registered to users, just never use a taxonomy for both posts and users at the same time or the IDs will clash – Tom J Nowell ♦ Commented Jan 16, 2021 at 19:03

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

You can't do this in a query with that kind of data.

The only way to truly do it is to retrieve all users, and manually process them in PHP to find the ID. Otherwise, you will get false positives such as the example you gave.

The fundamental problem, is the way the data is stored. There are several other options:

• store each areapref as an individual key/value pair, remember meta keys are not unique, you can add multiple values with the same key. Just set the 3rd parameter of get_user_meta to false and it will return an array of them instead of a single value.
• a taxonomy, taxonomy terms are used for posts by tracking their post ID, but they can be used for any kind of ID. Comment Category, User Tags, are both valid taxonomies, and it's perfectly valid to pass user as the second parameter when calling register_taxonomy, just be warned some additional boilerplate is necessary to get a WP Admin UI ( see Justin Tadlocks article on user taxonomies for code )

If you use individual user meta, your get_users call ( actually a WP_User_Query wrapped in a middle man helper function ), will be a lot more straight forward.

If you use a user taxonomy, then you'll also see a significant performance and scalability improvement. Meta is optimised in the database for queries where you already know the post/user ID. The meta tables have awful performance when doing searches and filtering. You would use all the same methods to retrieve the users that you would when retrieving post terms aka get_terms to retrieve the terms that match that location, where the terms all have a slug that matches a column in the em_locations table, then get_objects_in_term to retrieve the user IDs.

I realise you may have coded yourself into a tight corner by relinquishing control to frameworks such as ACF for your core data storage. Those frameworks usually provide filters that can help change how data is stored and retrieved for fields, but you will need to ask in an ACF community for help with that.

Additionally, you will need to migrate your data. This should be relatively easy, just create a loop that fetches 50 users that have that user meta set, create the new data for those users, delete the old meta that you queried for, then reload the page until it says it can find none.

本文标签：