AVAVT AVAVT - 1 year ago 58
MySQL Question

Wordpress query for users who have 2 equal meta

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 Source

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.