使用元数据表中的多个条目进行Compex MySQL左联接 [英] Compex MySQL Left Join using multiple entries from meta tables

问题描述

我正在尝试创建一个查询，以从主表(WordPress用户表)和用户元表中获取信息.

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

如果您不熟悉WP DB架构:wp_users保存基本的用户信息及其ID，wp_usermeta保存ID，meta_key和meta_value.

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.

比方说，我想让所有具有特定meta_keys和meta_values的用户.这是我当前拥有的查询(基于可用meta_keys通过PHP生成):

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

这做得很好，但是，如果某个用户缺少meta_key(例如'last_name')，则根本不会返回该用户行.因此，实际上我需要的是一种为缺少的元键返回Null值的方法.

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.

现在，我有一个非常棘手的功能，它会为所有没有给定meta_key的用户创建空白的meta_keys和meta_values，这样就可以返回它们.尽管当您有超过一千个用户并且需要添加一种新型的meta_key时，这是一种糟糕的做法.

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.

谢谢.

推荐答案

将usermeta联接条件放入联接中，而不是在子查询中苦苦挣扎:

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

这篇关于使用元数据表中的多个条目进行Compex MySQL左联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！