WP Meta Query: Query to Strictly Get Not Existing Meta Key and Value
This is the second time I am working on a plugin project like this, first time I don’t remember the project so I couldn’t get back the code, had to rewrite it. This time I am making a post so I could come back to it in the future.
So in this case, lets say we have WP users and they have list of foods they like, of course this data will be saved as WP User Meta and I am going to name the meta name as best_foods. Now we are going to save this data, not as unique value but multiple value. Below is a function for adding best food
<?php
function add_best_food( $user_id, $food )
{
if( !in_array( $food, get_user_meta( $user_id, 'best_foods', false ) ) ) {
add_user_meta( $user_id, 'best_foods', $food, false );
}
}
Now we can see the function checks if the food already exists in the user meta since we are not using a unique value. If not, then proceed to add user meta and set it false to unique value on the last parameter as on the documentation. means the meta key can be added multiple times and when using get_user_meta it returns an array of the values.
You can ask why not just use array as meta value and PHP serialize is done on it, I want to be able to manipulate data on database level and MySQL cannot read PHP serialized data, which is why I’m using multiple meta key value.
The Problem
This is the main reason I am making this post. Lets say I want to get list of user that doesn’t like rice only. How are we going to do that?
The Default WP Method
Using meta_query parameter in WP_Query let us try
<?php
get_users( [
'meta_query' => [
[
'key' => 'best_foods',
'value' => 'rice',
'compare' => '!='
]
]
]);
This should be the right way to query it, but what happens here? This is similar to this
SELECT * FROM <code>wp_users</code> INNER JOIN <code>wp_usermeta</code> ON <code>wp_users</code>.<code>ID</code> = <code>wp_usermeta</code>.<code>user_id</code> WHERE <code>meta_key</code> = 'best_foods' AND <code>meta_value</code> != 'rice' GROUP BY <code>ID</code>
What happens? The users that love rice will still be returned if they love other foods because the meta_key is not unique, there are many of them for a specific user ID. This is a bit confusing, it was for me at first 😉
The Right Way
I figured out a custom query to come about this using sub query with EXIST and NOT EXIST
<?php
function fetch_users( $likes )
{
global $wpdb;
$users = $wpdb->get_results( $wpdb->prepare( "
SELECT * FROM <code>{$wpdb->users}</code>
LEFT JOIN <code>{$wpdb->usermeta}</code>
ON <code>{$wpdb->users}</code>.<code>ID</code> = <code>{$wpdb->usermeta}</code>.<code>user_id</code>
WHERE EXISTS (
SELECT 1 FROM <code>{$wpdb->usermeta}</code>
WHERE <code>user_id</code> = <code>ID</code> AND <code>meta_key</code> = 'best_foods' AND <code>meta_value</code> = %s
)
GROUP BY <code>ID</code>
", $likes ) );
}
function fetch_users_notlike( $likes )
{
global $wpdb;
$users = $wpdb->get_results( $wpdb->prepare( "
SELECT * FROM <code>{$wpdb->users}</code>
LEFT JOIN <code>{$wpdb->usermeta}</code>
ON <code>{$wpdb->users}</code>.<code>ID</code> = <code>{$wpdb->usermeta}</code>.<code>user_id</code>
WHERE NOT EXISTS (
SELECT 1 FROM <code>{$wpdb->usermeta}</code>
WHERE <code>user_id</code> = <code>ID</code> AND <code>meta_key</code> = 'best_foods' AND <code>meta_value</code> = %s
)
GROUP BY <code>ID</code>
", $likes ) );
}
The above functions will get strictly on users that like or does not like a food even if they have other likes.
Below is a video that explains better what I’m doing here if you don’t get it, it’s somehow hard to understand.
I’m not sure there is a right way of doing this with WordPress inbuilt features, please let me know if there is in the comment section.
James John
Software Engineer
