Compex MySQL Left Join using multiple entries from meta tables

Question

I am trying to create a single query for getting information from the main table (WordPress user table), and the user meta table.

If you're not familiar with WP DB architecture: wp_users holds basic user information and their IDs, wp_usermeta holds the ID, meta_key, and meta_value.

Let's say I want to get all users that have certain meta_keys and meta_values. This is the query I currently have (generated via PHP based on available meta_keys):

SELECT ID, 
user_email, 
user_login, 
first_name.meta_value as first_name,
last_name.meta_value as last_name,
phone_number.meta_value as phone_number,
wp_capabilities.meta_value as wp_capabilities 
FROM wp_users, 
(select * from wp_usermeta where meta_key = 'first_name') as first_name,
(select * from wp_usermeta where meta_key = 'last_name') as last_name,
(select * from wp_usermeta where meta_key = 'phone_number') as phone_number,
(select * from wp_usermeta where meta_key = 'wp_capabilities') as wp_capabilities 
WHERE  
ID = first_name.user_id AND 
ID = last_name.user_id AND 
ID = phone_number.user_id AND 
ID = wp_capabilities.user_id  AND 
wp_wpi_capabilities.meta_value LIKE '%administrator%'   
ORDER BY first_name

This does a good job, however if a certain user is missing a meta_key, 'last_name' for instance, that user row does not get returned at all. So really all I need is a way of returning a Null value for missing meta keys.

Right now I have a very hackish function that goes through and creates blank meta_keys and meta_values for all the users that don't have a given meta_key, that way they are returned. This is a terrible way of doing it though when you have over a thousand users, and you need to add a new type of meta_key.

Let me know if anybody has done this, or if I need to explain better.

Thank you.

Answer 1

Put the usermeta joining criteria in the join rather than struggling with subqueries:

SELECT
    ID, user_email, user_login, 
    first_name.meta_value as first_name,
    last_name.meta_value as last_name,
    phone_number.meta_value as phone_number,
    wp_capabilities.meta_value as wp_capabilities 
FROM wp_users
    JOIN wp_usermeta AS wp_capabilities ON wp_capabilities.user_id=ID
        AND wp_capabilities.meta_key='wp_capabilities'
    LEFT JOIN wp_usermeta AS first_name ON first_name.user_id=ID
        AND first_name.meta_key='first_name'
    LEFT JOIN wp_usermeta AS last_name ON last_name.user_id=ID
        AND last_name.meta_key='last_name'
    LEFT JOIN wp_usermeta AS phone_number ON phone_number.user_id=ID
        AND phone_number.meta_key='phone_number'
WHERE
    wp_capabilities.meta_value LIKE '%administrator%'
ORDER BY
    first_name