1

trying to retrieve all user_id's where those user_id's meet a 3 criteria.

the code I have is 

$sql = "
SELECT user_id
FROM {$wpdb->usermeta} 
WHERE (({$wpdb->usermeta}.meta_key = 'job_title' AND {$wpdb-
>usermeta}.meta_value = '".$job_title."') OR
({$wpdb->usermeta}.meta_key = 'pmpro_bstate' AND {$wpdb-
>usermeta}.meta_value = '".$state."') OR
({$wpdb->usermeta}.meta_key = 'pmpro_baddress2' AND {$wpdb-
>usermeta}.meta_value = '".$suburb."'))
GROUP BY
   user_id
HAVING 
   COUNT(DISTINCT meta_key)=3";

Is Lower() needed on the value and variables to compare them accurately ?

And is there a more efficient way to perform this query ?

Cheers

David
  • 31
  • 6

1 Answers1

1

It is much better to do this using INNER JOIN:

$sql = "SELECT u.ID
FROM wp_users u
INNER JOIN wp_usermeta m1 ON u.ID = m1.user_id AND m1.meta_key = 'job_title'
INNER JOIN wp_usermeta m2 ON u.ID = m2.user_id AND m2.meta_key = 'pmpro_bstate'
INNER JOIN wp_usermeta m3 ON u.ID = m3.user_id AND m3.meta_key = 'pmpro_baddress2'
WHERE m1.meta_value = '".$job_title."'
AND m2.meta_value = '".$state."'
AND m3.meta_value = '".$suburb."'"
Milan Petrovic
  • 199
  • 6
  • 15
  • What makes the INNER JOIN better to use? And also thank you – David Aug 28 '17 at 23:30
  • Each join creates a smaller set of data that are faster to process (, and MySQL server is optimized to handle joins better than other methods dealing with multiple tables, so it has a performance advantage over other methods. There are cases where joins can be slower (especially if mixing LEFT and INNER), but in this case, it is cleaner and easier to read the query and based on the data processing much faster. – Milan Petrovic Aug 29 '17 at 07:13