Wordpress query for users who have 2 equal meta
How can I get all users who have 2 custom meta field
meta_1meta_2For example:
// This user will be in the result
User 1:
meta_key : meta_1, meta_value: 6
meta_key : meta_2, meta_value: 6
// This user will also be in the result
User 2:
meta_key : meta_1, meta_value: 9001
meta_key : meta_2, meta_value: 9001
// This user will NOT be in the result
User 3:
meta_key : meta_1, meta_value: 25
meta_key : meta_2, meta_value: 6
// This user will NOT be in the result
User 4:
meta_key : meta_1, meta_value: 9001
// (this user does not have meta_2)
The way I'd do this:
add an extra argument to WP_Query:
$args = array(
'equal_meta' => array('meta_1', 'meta_2')
);
$query = new WP_Query( $query_args );
and create join filter for it:
function my_posts_join( $join, $query ) {
if( _some_security_checks_if_needed ) {
$rule = $query->get('equal_meta', array());
if(!empty($rule) {
... write custom INNER JOIN using data from $rule
$join .= $customJoin
}
}
return $join;
}
I think that join is the best way, but there may be better... I'm not SQL guru ;)
And of course 'bind' that filter:
add_filter('posts_join', 'my_posts_join');
$query = new WP_Query( $query_args );
remove_filter('posts_join', 'my_posts_join');
If you want this filter to be applied to every WP_Query rather than specific one, then you should 'bind' it somewhere outside function, the best practice is to add it before/after function it calls:
add_filter('posts_join', 'my_posts_join');
function my_posts_join( $join, $query ) { ... }
Then you don't have to remove it.
[ADDED SQL]
Simpliest SQL query I could think of:
select wu.* from wp_users wu
-- two joins ensure we get only users with existing both meta values
inner join wp_usermeta wm1 on wm1.user_id = wu.ID and wm1.meta_key = 'meta_1'
inner join wp_usermeta wm2 on wm2.user_id = wu.ID and wm2.meta_key = 'meta_2'
-- now we filter only those with the same meta
where wm1.meta_value = wm2.meta_value;
This shows we will need two filters: posts_join, and posts_where. I hope you will manage to create both of them based on my answer.
$customJoin should look like this (pseudocode):
inner join wp_usermeta wm1 on wm1.user_id = wu.ID and wm1.meta_key = $rule[0]
inner join wp_usermeta wm2 on wm2.user_id = wu.ID and wm2.meta_key = $rule[1]
$customWhere:
AND wm1.meta_value = wm2.meta_value
Please note that in where there's AND, because $where (function's 1st argument) will actually include the beginning.
You should also add an empty space on the beginning of your custom query strings, otherwise they may produce invalid SQL.