How do I query for posts by partial meta key?

Question

I have a function that stores the "like" status for a post as post meta. I want to associate that "like" with the user that liked it, so I setup a custom field called "like_status_{user_id}" (where {user_id} is the id of the currently logged in user) which I store as a 0 or 1. So for a post with several "likes" there would be several meta values in the db that are setup like this:

'meta_key' = 'like_status_0'
'meta_value' = 1
'meta_key' = 'like_status_2'
'meta_value' = 1
'meta_key' = 'like_status_34'
'meta_value' = 1

....and so on.

There are potentially thousands of likes on a specific post. How would I run a query that showed if someone else also liked that post?

I was thinking something like this:

$query = new WP_Query(array(
    'meta_key' => 'like_status_{user_id}',
    'meta_value' => 1,
));

I'm trying to push a notification to everyone who has liked a post when someone else likes that post...something like, "Hey, someone else liked that post you liked. You should go check it out!" But I need a way to find out if anyone else has liked that post and if so, who they would be so I could notify them.

If it's not possible, could you suggest a better way of storing this data as post_meta while still keeping the efficiency of quickly updating a single user's like status on a post?

Answer 1

Unfortunately you cannot perform a meta_query using a LIKE comparison on the meta_key value when using WP_Query. I've been down this road...

Instead you have a couple other options if you want to maintain like status relationships as post meta and not user meta and or meta in a custom table.

Option 1

• requires no modification of your meta schema
• uses wpdb class to perform a custom query

Example:

//when a user likes a post...
$current_user_id = get_current_user_id();
add_post_meta($current_user_id, "like_status_{$current_user_id}", 1, false);

//later in the request...
global $wpdb;

$results = $wpdb->get_results(
    "
    SELECT meta_key 
    FROM {$wpdb->prefix}postmeta 
    WHERE meta_key 
    LIKE 'like_status_%'
    ",
    ARRAY_N
);

$results = array_map(function($value){

    return (int) str_replace('like_status_', '', $value[0]);

}, $results);

array_walk($results, function($notify_user_id, $key){

    //apply to all users except the user who just liked the post
    if ( $notify_user_id !== $current_user_id ) {
        //notify logic here...           
    }

});

_{Note: logic could be simplified further if you wish.}

Option 2

• requires you change your meta schema
• requires you store the user id as the meta value
• allows you to use WP_Query along with meta_query

Option 2 requires that you change your meta key from like_status_{user_id} to something universal such as like_status or liked_by_user_id where in turn instead of storing the value of 1 against the key, you instead store the user's id as the value.

//when a user likes a post...
$current_user_id = get_current_user_id();
add_post_meta($current_user_id, "liked_by_user_id", $current_user_id, false);

//later in the request
$args = array(
    'post_type'  => 'post', //or a post type of your choosing
    'posts_per_page' => -1,
    'meta_query' => array(
        array(
            'key' => 'liked_by_user_id',
            'value' => 0,
            'type' => 'numeric'
            'compare' => '>'
        )
    )
);

$query = new WP_Query($args);   

array_walk($query->posts, function($post, $key){

    $user_ids = get_post_meta($post->ID, 'liked_by_user_id');

    array_walk($user_ids, function($notify_user_id, $key){
        
        //notify all users except the user who just like the post
        if ( $notify_user_id !== $current_user_id ) {
                
            //notify logic here...
            //get user e.g. $user = get_user_by('id', $notify_user_id);
            
        }

    });

});

Answer 2

It is quite difficult to concretely answer your question. The first part is easy though. I recently did something similar on stackoverflow

Meta keys are are compared and match exactly. WP_Query have no means to adjust this behavior with a simple parameter, but we can always introduce one ourselves and then adjust the posts_where clause to do a LIKE comparison on meta keys.

THE FILTER

This is just a basic filter, adjust it as needed.

add_filter( 'posts_where', function ( $where, \WP_Query $q )
{ 
    // Check for our custom query var
    if ( true !== $q->get( 'wildcard_on_key' ) )
        return $where;

    // Lets filter the clause
    $where = str_replace( 'meta_key =', 'meta_key LIKE', $where );

    return $where;
}, 10, 2 );

As you can see, the filter is only fired when we set our new custom parameter, wildcard_on_key to true. When this checks out, we simply change the = comparator to the LIKE comparator

Just a note on this, LIKE comparisons are inherently more expensive to run that other comparisons

THE QUERY

You can simply query your posts as follow to get all posts with meta keys like_status_{user_id}

$args = [
    'wildcard_on_key' => true,
    'meta_query'      => [
        [
            'key'   => 'like_status_',
            'value' => 1,
        ]
    ]
];
$query = new WP_Query( $args );

OTHER QUESTION

Custom fields does not have impact on performance, you can read my post on this subject here. I am however troubled by that you say each post can have hundreds or thousands of likes. This can hit you on performance getting and caching such a large amount of custom field data. It can also clog your db with a huge amount of unnecessary custom field data which makes it quite hard to maintain.

I am not a very big fan of storing serialized data in custom fields as one cannot search or order by serialized data. I would however suggest storing all the user ID's in an array under one custom field. You can simply just update the array with the user ID when a user like a post. Getting the custom field data and looping over the array of ID's and doing something with the ID's are easy. Just have a look at get_post_meta()

Updating a custom field is also easy. For that, you will need to look into update_post_meta(), I do not know how you create your custom fields, but update_post_meta() is definitely something you would want to use.

If you need to send emails or push notifications when a custom field is updated, you have the following hooks available to work with. (See update_metadata() for context)

• update_postmeta

• updated_{$meta_type}_meta

• updated_postmeta

• update_{$meta_type}_meta

• update_{$meta_type}_metadata

CONCLUSION

Just before I post this, again, before you go the serialized route, make sure that you would not need to sort by the sorted data or search for particular data inside the serialized data.

Answer 3

Since wordpress 5.1 it is possible now to use meta query like:

Answer 4

If later on you want to extend this, with more detailed statistics, features, etc, then yet another alternative could be: custom table(s)

• pros: Tailored to your needs and can be indexed for better performance.

• cons: More work

There might also be a workaround using a custom taxonomy, that could give better query performance than post meta queries, due to how the core tables are indexed.

I'm trying to push a notification to everyone who has liked a post when someone else likes that post...something like, "Hey, someone else liked that post you liked. You should go check it out!" But I need a way to find out if anyone else has liked that post and if so, who they would be so I could notify them.

Not sure what kind of notifications you mean here, but this can quickly get bulky.

Example: A user that likes ~ 1000 posts and each post gets ~ 1000 likes, then there are 1M notifications in the pipes, only for that user! If these are email notifications then the host provider might not be happy and the user would go crazy. That might also be expensive with a 3rd party email service.

Answer 5

Per the WP_Meta_Query documentation you can use the compare argument in the meta_query argument of WP_Query. However, you can only compare on the value and not the key so you may want to re-think how you structure this.

A like argument would look like this:

$arguments = array(
    'meta_query' => array(
        array(
            'key' => 'foo',
            'value' => 'ba',
            'compare' => 'LIKE'
        )
    )
);

$query = new WP_Query($arguments);

Given that you can't do a 'LIKE' search on the key I'd suggest you add the liked posts in the user meta and do a WP_User_Query search for users who have liked that post:

$arguments = array(
    'meta_query' => array(
        array(
            'key' => 'liked_post',
            'value' => '<post_id>'
        )
    )
);

$users = new WP_User_Query($arguments);