SQL Query in WordPress - Getting Impossible WHERE error

Question

I don't get any result in the SQL query below. I am trying to find user_id of users with custom usermeta fields.

SELECT
    DISTINCT user_id
    FROM wp_usermeta
    WHERE (
        (
            (meta_key='user_school_subjects' AND meta_value LIKE '%history%')
            OR (meta_key='user_professional_courses' AND meta_value LIKE '%history%')
            OR (meta_key='user_language_tutoring' AND meta_value LIKE '%history%')
            OR (meta_key='user_music_tutoring' AND meta_value LIKE '%history%')
        )
        AND (meta_key='user_gender' AND meta_value='male')
    )

When I run the query in SQL PHPMYADMIN, I get an error -> Impossible WHERE Query

What's wrong with the query? Please help me fix the error in the above query.

UPDATE: Here is the full search query being built using a form.

global $wpdb;

    $user_ids = array();
    $user_query = null;

    if (isset($_REQUEST['search']) && !empty($_REQUEST['search']) ) {
        $s = $_REQUEST['search'];
        $user_query .= "((meta_key='first_name' AND meta_value LIKE '%".$s."%') OR (meta_key='last_name' AND meta_value LIKE '%".$s."%') OR (meta_key='display_name' AND meta_value LIKE '%".$s."%') OR (meta_key='user_nicename' AND meta_value LIKE '%".$s."%') OR (meta_key='user_login' AND meta_value LIKE '%".$s."%'))";
    }

    if (isset($_REQUEST['email']) && !empty($_REQUEST['email']) ) {
        $s = $_REQUEST['email'];
        if ($user_query) {
        $user_query .= " AND (meta_key='user_email' AND meta_value='".$s."')";
        } else {
        $user_query .= "(meta_key='user_email' AND meta_value='".$s."')";
        }
    }

    if (isset($_REQUEST['subject']) && $_REQUEST['subject'] !='' ) {
        $s = $_REQUEST['subject'];
        if ($user_query) {
        $user_query .= " AND ((meta_key='user_school_subjects' AND meta_value LIKE '%".$s."%')";
        $user_query .= " OR (meta_key='user_professional_courses' AND meta_value LIKE '%".$s."%')";
        $user_query .= " OR (meta_key='user_language_tutoring' AND meta_value LIKE '%".$s."%')";
        $user_query .= " OR (meta_key='user_music_tutoring' AND meta_value LIKE '%".$s."%'))";
        } else {
        $user_query .= "((meta_key='user_school_subjects' AND meta_value LIKE '%".$s."%')";
        $user_query .= " OR (meta_key='user_professional_courses' AND meta_value LIKE '%".$s."%')";
        $user_query .= " OR (meta_key='user_language_tutoring' AND meta_value LIKE '%".$s."%')";
        $user_query .= " OR (meta_key='user_music_tutoring' AND meta_value LIKE '%".$s."%'))";
        }
    }

    if (isset($_REQUEST['location']) && $_REQUEST['location'] !='' ) {
        $s = $_REQUEST['location'];
        if ($user_query) {
        $user_query .= " AND ((meta_key='user_address' AND meta_value LIKE '%".$s."%')";
        $user_query .= " OR (meta_key='user_zipcode' AND meta_value LIKE '%".$s."%'))";
        } else {
        $user_query .= "((meta_key='user_address' AND meta_value LIKE '%".$s."%')";
        $user_query .= " OR (meta_key='user_zipcode' AND meta_value LIKE '%".$s."%'))";
        }

    }

    if (isset($_REQUEST['gender']) && !empty($_REQUEST['gender']) ) {
        if ( $_REQUEST['gender'] != '') {
            $s = $_REQUEST['gender'];
            if ($user_query) {
            $user_query .= " AND (meta_key='user_gender' AND meta_value='".$s."')";
            } else {
            $user_query .= "(meta_key='user_gender' AND meta_value='".$s."')";
            }
        }
    }

    if (isset($_REQUEST['status']) && !empty($_REQUEST['status']) ) {
        if ( $_REQUEST['status'] == 'active') {
            $s = $_REQUEST['status'];
            if ($user_query) {
            $user_query .= " AND ( (meta_key='user_membership' AND meta_value='active')";
            $user_query .= " OR (meta_key='user_availability' AND meta_value='available'))";
            } else {
            $user_query .= "((meta_key='user_membership' AND meta_value='active')";
            $user_query .= " OR (meta_key='user_availability' AND meta_value='available'))";
            }
        }
        elseif ( $_REQUEST['status'] == 'inactive') {
            $s = $_REQUEST['status'];
            if ($user_query) {
            $user_query .= " AND ((meta_key='user_membership' AND meta_value='".$s."')";
            $user_query .= " OR (meta_key='user_availability' AND meta_value='unavailable'))";
            } else {
            $user_query .= "((meta_key='user_membership' AND meta_value='".$s."')";
            $user_query .= " OR (meta_key='user_availability' AND meta_value='unavailable'))";
            }
        }
    }

    if (isset($_REQUEST['nationality']) && $_REQUEST['nationality']!='any' ) {
        $s = $_REQUEST['nationality'];
        if ($user_query) {
        $user_query .= "AND (meta_key='user_nationality' AND meta_value='".$s."')";
        } else {
        $user_query .= "(meta_key='user_nationality' AND meta_value='".$s."')";
        }
    }


    if (!empty($user_query)) {
        $wp_users = $wpdb->get_results("SELECT DISTINCT user_id FROM $wpdb->usermeta WHERE ($user_query)"); }

EDIT: You can find the answer here: https://stackoverflow.com/questions/3735176/compex-mysql-left-join-using-multiple-entries-from-meta-tables

Answer 1

Look at your conditions:

WHERE (
  ( 
    (meta_key='user_school_subjects'
      AND meta_value LIKE '%history%')
    OR (meta_key='user_professional_courses'
        AND meta_value LIKE '%history%')
    OR (meta_key='user_language_tutoring'
        AND meta_value LIKE '%history%')
    OR (meta_key='user_music_tutoring'
        AND meta_value LIKE '%history%')
  )
  AND (meta_key='user_gender'
        AND meta_value='male'))

There is no way that you can have a meta key matching one of these--

• user_school_subjects,
• user_professional_courses,
• user_language_tutoring,
• user_music_tutoring

... that also matches this--

• user_gender

If the key matches any of the first four, and it has to match one for that block to be true, then it automatically does not match the fifth. You can't ever get a true out of that. This clause is exactly what the server says-- impossible.

I would suggest WP_User_Query but I don't think it is capable of the logic you need.

You need to JOIN on the table, something like this:

SELECT DISTINCT wpmu.user_id
FROM wp_usermeta AS wpmu
LEFT JOIN wp_usermeta AS gender ON wpmu.user_id = gender.user_id
WHERE (
  (
    (wpmu.meta_key='user_school_subjects'
        AND wpmu.meta_value LIKE '%history%')
      OR (wpmu.meta_key='user_professional_courses'
          AND wpmu.meta_value LIKE '%history%')
      OR (wpmu.meta_key='user_language_tutoring'
          AND wpmu.meta_value LIKE '%history%')
      OR (wpmu.meta_key='user_music_tutoring'
          AND wpmu.meta_value LIKE '%history%')
    )
    AND (gender.meta_key='user_gender'
      AND gender.meta_value='male')
  )

I can't swear to that 100% since I can't test it without your data, but that is the idea.