Wordpress query for users who have 2 equal meta

Question

How can I get all users who have 2 custom meta field

meta_1

and

meta_2

of equal value?

For 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)

Answer 1

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.

Wordpress query for users who have 2 equal meta

Sign up