plugin development - Using AND and bracket grouping in SQL not working

admin管理员组

文章数量:1307584

I am trying to combine two conditions in a query but it isn't working. but running the conditions independently works.

LEt me explain. When I run this query:

SELECT DISTINCT
    SQL_CALC_FOUND_ROWS wp_users.*
FROM
    wp_users
    INNER JOIN wp_usermeta ON (wp_users.ID = wp_usermeta.user_id)
WHERE
    (wp_users.user_login LIKE '%little%'
        OR wp_users.user_email LIKE '%little%'
        OR wp_users.user_url LIKE '%little%'
        OR wp_users.display_name LIKE '%little%'
        OR (wp_usermeta.meta_key = 'first_name'
            AND wp_usermeta.meta_value LIKE '%little%')
        OR (wp_usermeta.meta_key = 'last_name'
            AND wp_usermeta.meta_value LIKE '%little%'))
ORDER BY
    user_registered DESC

I get this result

And when I run this

SELECT DISTINCT
    SQL_CALC_FOUND_ROWS wp_users.*
FROM
    wp_users
    INNER JOIN wp_usermeta ON (wp_users.ID = wp_usermeta.user_id)
WHERE ((wp_usermeta.meta_key = 'car_type'
        AND wp_usermeta.meta_value LIKE '%Camry%')
    OR(wp_usermeta.meta_key = 'car_type'
        AND wp_usermeta.meta_value LIKE '%Toyota%'))
ORDER BY
    user_registered DESC

I get this result

As you can see, the user with ID 46 is common in both query result.

I only want to show users that matches both query conditions. In this case, user ID 46 only.

When I then combined them like so, I get nothing:

SELECT DISTINCT
    SQL_CALC_FOUND_ROWS wp_users.*
FROM
    wp_users
    INNER JOIN wp_usermeta ON (wp_users.ID = wp_usermeta.user_id)
WHERE
    (wp_users.user_login LIKE '%little%'
        OR wp_users.user_email LIKE '%little%'
        OR wp_users.user_url LIKE '%little%'
        OR wp_users.display_name LIKE '%little%'
        OR (wp_usermeta.meta_key = 'first_name'
            AND wp_usermeta.meta_value LIKE '%little%')
        OR (wp_usermeta.meta_key = 'last_name'
            AND wp_usermeta.meta_value LIKE '%little%'))
            
AND((wp_usermeta.meta_key = 'car_type'
    AND wp_usermeta.meta_value LIKE '%Camry%')
    OR(wp_usermeta.meta_key = 'car_type'
        AND wp_usermeta.meta_value LIKE '%Toyota%'))
ORDER BY
    user_registered DESC

This has been driving me nuts for hours now. Please help me figure out what is wrong.

I am trying to combine two conditions in a query but it isn't working. but running the conditions independently works.

LEt me explain. When I run this query:

SELECT DISTINCT
    SQL_CALC_FOUND_ROWS wp_users.*
FROM
    wp_users
    INNER JOIN wp_usermeta ON (wp_users.ID = wp_usermeta.user_id)
WHERE
    (wp_users.user_login LIKE '%little%'
        OR wp_users.user_email LIKE '%little%'
        OR wp_users.user_url LIKE '%little%'
        OR wp_users.display_name LIKE '%little%'
        OR (wp_usermeta.meta_key = 'first_name'
            AND wp_usermeta.meta_value LIKE '%little%')
        OR (wp_usermeta.meta_key = 'last_name'
            AND wp_usermeta.meta_value LIKE '%little%'))
ORDER BY
    user_registered DESC

I get this result

And when I run this

SELECT DISTINCT
    SQL_CALC_FOUND_ROWS wp_users.*
FROM
    wp_users
    INNER JOIN wp_usermeta ON (wp_users.ID = wp_usermeta.user_id)
WHERE ((wp_usermeta.meta_key = 'car_type'
        AND wp_usermeta.meta_value LIKE '%Camry%')
    OR(wp_usermeta.meta_key = 'car_type'
        AND wp_usermeta.meta_value LIKE '%Toyota%'))
ORDER BY
    user_registered DESC

I get this result

As you can see, the user with ID 46 is common in both query result.

I only want to show users that matches both query conditions. In this case, user ID 46 only.

When I then combined them like so, I get nothing:

SELECT DISTINCT
    SQL_CALC_FOUND_ROWS wp_users.*
FROM
    wp_users
    INNER JOIN wp_usermeta ON (wp_users.ID = wp_usermeta.user_id)
WHERE
    (wp_users.user_login LIKE '%little%'
        OR wp_users.user_email LIKE '%little%'
        OR wp_users.user_url LIKE '%little%'
        OR wp_users.display_name LIKE '%little%'
        OR (wp_usermeta.meta_key = 'first_name'
            AND wp_usermeta.meta_value LIKE '%little%')
        OR (wp_usermeta.meta_key = 'last_name'
            AND wp_usermeta.meta_value LIKE '%little%'))
            
AND((wp_usermeta.meta_key = 'car_type'
    AND wp_usermeta.meta_value LIKE '%Camry%')
    OR(wp_usermeta.meta_key = 'car_type'
        AND wp_usermeta.meta_value LIKE '%Toyota%'))
ORDER BY
    user_registered DESC

This has been driving me nuts for hours now. Please help me figure out what is wrong.

• plugin-development
• database
• mysql
• sql

Share Improve this question asked Jan 10, 2021 at 11:17 W3GuyW3Guy 26411 silver badge1010 bronze badges 1

• Have you tried to achieve this using a WP_User_Query - developer.wordpress/reference/classes/wp_user_query – Q Studio Commented Jan 10, 2021 at 12:40

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

You need 2 table joins since there are effectively 2 meta keys in your WHERE clause:

SELECT DISTINCT
    SQL_CALC_FOUND_ROWS wp_users.*
FROM
    wp_users
        INNER JOIN wp_usermeta AS um1 ON (wp_users.ID = um1.user_id)
        INNER JOIN wp_usermeta AS um2 ON (wp_users.ID = um2.user_id)
WHERE
    (wp_users.user_login LIKE '%little%'
        OR wp_users.user_email LIKE '%little%'
        OR wp_users.user_url LIKE '%little%'
        OR wp_users.display_name LIKE '%little%'
        OR (um1.meta_key = 'first_name'
            AND um1.meta_value LIKE '%little%')
        OR (um1.meta_key = 'last_name'
            AND um1.meta_value LIKE '%little%'))

  AND((um2.meta_key = 'car_type'
    AND um2.meta_value LIKE '%Camry%')
    OR(um2.meta_key = 'car_type'
        AND um2.meta_value LIKE '%Toyota%'))
ORDER BY
    user_registered DESC

本文标签： plugin developmentUsing AND and bracket grouping in SQL not working