Wordpress 会员资格 - 获取计划中具有特定元键非空值的所有活动成员.同时检索其他元数据

Question

我正在创建一个谷歌地图，其中包含我所有活跃的WP会员的标记。首先，我需要查询数据库以获取特定成员资格计划(传递给函数)的所有活动(和/或免费)成员，但它们还必须具有自定义meta_keyaffiliate_location的非空值。我的会员可以使用一个前端表单，他们可以通过在表单中输入他们的位置(和其他详细信息)来选择加入地图，将信息保存为用户元。如果他们没有输入自己的位置，他们将有一个空meta_value用于meta_keyaffiliate_location。

我已经完成了上述工作。我的函数可以检查用户是否是活跃/免费会员，然后检查他们是否有非空affiliate_locationmeta_value，并返回这些用户的列表，其中包括他们的user_id、display_name以及affiliate_locationmeta_key和meta_value(meta_value是我将用来在谷歌地图上放置他们的标记)。

我现在要完成的是，如果他们通过上述测试，则返回其他用户元数据。如果他们是活动成员并输入了一个位置，那么我还想返回(在同一查询中)以下元键的元值：affiliate_name、affiliate_email、affiliate_website、affiliate_phone_number。

在@LoicTheAztec对这个问题的回答的帮助下，我尝试了下面的SQL查询，调整了SQL以检查affiliate_location元值。

我尝试为需要返回的其他元键/值对的用户元表设置别名。您可以在最后LEFT JOIN ... usermeta AS um1...行中看到这一点，然后在第一个表达式中列出um1.meta_key，最后包括条件AND um1.meta_key = 'affiliate_name'，但结果只包含一个meta_key和一个meta_value属性。我不知道如何为每个匹配的用户返回所需的其他元数据。任何帮助都非常感谢！

// List of Active Users for a Membership Plan
function get_active_members_for_membership( $membership_slug )
{
global $wpdb;
// Getting all User IDs and data for a membership plan
return $wpdb->get_results( "
SELECT DISTINCT um.user_id, u.display_name, um.meta_key, um.meta_value, um1.meta_key, um1.meta_value
FROM {$wpdb->prefix}posts AS p
LEFT JOIN {$wpdb->prefix}posts AS p2 ON p2.ID = p.post_parent
LEFT JOIN {$wpdb->prefix}users AS u ON u.id = p.post_author
LEFT JOIN {$wpdb->prefix}usermeta AS um ON u.id = um.user_id
LEFT JOIN {$wpdb->prefix}usermeta AS um1 ON u.id = um1.user_id
WHERE p.post_type = 'wc_user_membership'
AND p.post_status IN ('wcm-active', 'wcm-complimentary')
AND p2.post_type = 'wc_membership_plan'
AND p2.post_name LIKE '$membership_slug'
AND um.meta_key = 'affiliate_location'
AND um.meta_value <> ''
AND um1.meta_key = 'affiliate_name'
-- AND um.meta_key = 'affiliate_email'
-- AND um.meta_key = 'affiliate_website'
-- AND um.meta_key = 'affiliate_phone_number'
" );
}

以下是我从上述尝试中得到的结果：

Array
(
[0] => stdClass Object
(
[user_id] => 1
[display_name] => Colin
[meta_key] => affiliate_name
[meta_value] => fake affiliate name 1
)
[1] => stdClass Object
(
[user_id] => 925
[display_name] => Hello
[meta_key] => affiliate_name
[meta_value] => fake affiliate name 2
)
)

在这种情况下，如您所见，结果包括affiliate_name元键/值。这只是我想要的一部分。如果我从第一个 SQL 表达式中删除um1.meta_key, um1.meta_value，则我得到位置元键/值。

基本上，对于所有匹配的用户(具有存储affiliate_location数据的"活动"成员)，我还需要在同一查询中返回他们的姓名、电子邮件、网站和电话号码。否则，我将不得不运行一个查询来获取所有匹配的用户，然后使用他们的User_ID值运行数百个单独的查询，以收集他们的所有会员数据。有没有办法在一个查询中获取所有这些数据？

提前非常感谢您的帮助！

---

更新：

我已经实施了@Edward的解决方案。他提出了 2 个解决方案，一个使用标准的 JOIN 子查询，另一个使用更高级的 OUTER APPLY，但我发现我的 MySQL 数据库不接受 OUTER APPLY 语法，所以我选择实现第一个解决方案，并进行了一些更正(有一个错字与websitevsweb_site)和主 WHERE 子句末尾的附加条件。 这是我尝试过的：

SELECT DISTINCT user_meta.user_id, u.display_name, user_meta.loc_key, user_meta.loc_value, user_meta.name_key, user_meta.name_value, user_meta.email_key, user_meta.email_value, user_meta.website_key, user_meta.website_value, user_meta.phone_key, user_meta.phone_value
FROM {$wpdb->prefix}posts AS p
LEFT JOIN {$wpdb->prefix}posts AS p2 ON p2.ID = p.post_parent
LEFT JOIN {$wpdb->prefix}users AS u ON u.id = p.post_author
LEFT JOIN
(
SELECT
loc.user_id,
loc.meta_key AS loc_key,
loc.meta_value AS loc_value,
name.meta_key AS name_key,
name.meta_value AS name_value,
email.meta_key AS email_key,
email.meta_value AS email_value,
website.meta_key AS website_key,
website.meta_value AS website_value,
phone.meta_key  AS phone_key,
phone.meta_value  AS phone_value
FROM {$wpdb->prefix}usermeta AS loc
LEFT JOIN {$wpdb->prefix}usermeta AS name
ON loc.user_id = name.user_id
AND name.meta_key = 'affiliate_name'
LEFT JOIN {$wpdb->prefix}usermeta AS email
ON loc.user_id = email.user_id
AND email.meta_key = 'affiliate_email'
LEFT JOIN {$wpdb->prefix}usermeta AS website
ON loc.user_id = website.user_id
AND website.meta_key = 'affiliate_website'
LEFT JOIN {$wpdb->prefix}usermeta AS phone
ON loc.user_id = phone.user_id
AND phone.meta_key = 'affiliate_phone_number'
WHERE loc.meta_key = 'affiliate_location'
AND loc.meta_value <> ''
)  AS user_meta ON user_meta.user_id = u.id
WHERE p.post_type = 'wc_user_membership'
AND p.post_status IN ('wcm-active', 'wcm-complimentary')
AND p2.post_type = 'wc_membership_plan'
AND p2.post_name LIKE '$membership_slug'
AND user_meta.loc_value <> ''

这个解决方案完全实现了我想要的！如果没有最终的AND user_meta.loc_value <> ''条件，返回的数组将包括所有"活动"用户，即使他们有空affiliate_location值。通过添加最终条件，这些用户被过滤掉。

非常感谢您的帮助@Edward非常感谢！我学到了很多东西。干杯！

Answer 1

我想我发现了你的问题。um1.meta_value返回NULL，因为您没有像um.那样根据affiliate_location元值规则的非空性过滤um1.。

具体来说，WHERE子句中的这些行没有执行您希望它们执行的操作。

WHERE ...
AND um.meta_key = 'affiliate_location'
AND um.meta_value <> ''
AND um1.meta_key = 'affiliate_name'

因为在FROM子句中，您使用的是两个LEFT OUTER JOIN。

我不知道您的数据结构，但是像这样向WHERE中添加一些东西可能会起作用：

AND um1.user_id = um.user_id -- this is what will force um1. records to match um. records
AND um1.meta_value <> '' -- this should be removed unless everyone with a location also has an affilicate_name 

这有帮助吗？

07/12

因此，为了回应您的评论，我将在这里扩展我的答案。您正在执行的LEFT JOINS确实是从usermeta中提取相同的列，但重要的是SQL将它们视为两个单独的对象。

这很重要，因为WHERE语句AND um.meta_key = affiliate_location不会影响SELECT ... um1.meta_key , um1.meta_value ...返回的usermeta AS um1列。

它没有做你期望它正在做的事情。

为了使um1仅返回您感兴趣的列，您需要像已经做的那样过滤um，然后显式强制um1.id匹配um.id。

您也可以尝试此操作。

FROM ..
LEFT JOIN (
SELECT um.user_id , um.meta_key, um1.meta_value
FROM usermeta as um 
INNER JOIN usermeta as um1 
ON um.user_id = um1.user_id 
WHERE 
um.meta_key = 'affiliate_location'
AND um.meta_value <> '' 
AND um1.meta_key = 'affiliate_name') AS usermeta 
ON usermeta.user_id = u.id 

我稍微重新制作了您的原始查询，这应该有望返回您所要求的内容。

SELECT DISTINCT 
user_meta.user_id
,   u.display_name
,   user_meta.loc_key
,   user_meta.loc_value
,   user_meta.name_key
,   user_meta.name_value
,   user_meta.email_key
,   user_meta.email_value
,   user_meta.website_key
,   user_meta.website_value
,   user_meta.phone_key
,   user_meta.phone_value
FROM {$wpdb->prefix}posts AS p
LEFT JOIN {$wpdb->prefix}posts AS p2 ON p2.ID = p.post_parent
LEFT JOIN {$wpdb->prefix}users AS u ON u.id = p.post_author
LEFT JOIN ( 
SELECT 
loc.user_id
,   loc.meta_key AS loc_key
,   loc.meta_value AS loc_value
,   name.meta_key AS name_key
,   name.meta_value AS name_value
,   email.meta_key AS email_key
,   email.meta_value AS email_value
,   website.meta_key AS website_key
,   website.meta_value AS website_value
,   phone.meta_key  AS phone_key
,   phone.meta_value  AS phone_value
FROM {$wpdb->prefix}usermeta AS loc 
LEFT JOIN {$wpdb->prefix}usermeta AS name 
ON loc.id = name.user_id
AND name.meta_key = 'affiliate_name'
LEFT JOIN {$wpdb->prefix}usermeta AS email 
ON loc.id = email.user_id
AND email.meta_key = 'affiliate_email'
LEFT JOIN {$wpdb->prefix}usermeta AS website
ON loc.id = website.user_id
AND web_site.meta_key = 'affiliate_website'
LEFT JOIN {$wpdb->prefix}usermeta AS phone
ON loc.id = phone.user_id
AND phone.meta_key = 'affiliate_phone_number'
WHERE loc.meta_key = 'affiliate_location'
AND loc.meta_value <> ''
)  AS user_meta
ON user_meta.user_id = u.id 
WHERE p.post_type = 'wc_user_membership'
AND p.post_status IN ('wcm-active', 'wcm-complimentary')
AND p2.post_type = 'wc_membership_plan'
AND p2.post_name LIKE '$membership_slug'
AND user_meta.loc_value <> ''

由于所有LEFT JOINS，这可能效率很低。

07/13 - 上述带有LEFT OUTER JOIN的解决方案适用于mySQL。如果有人在SQLServer上尝试类似的事情，那么他们可能会对OUTER APPLY方法有更多的运气，所以我把它留在这个答案中。

SELECT DISTINCT 
um.user_id
,   u.display_name
,   meta_data.meta_key
,   meta_data.meta_value
FROM {$wpdb->prefix}posts AS p
LEFT JOIN {$wpdb->prefix}posts AS p2 ON p2.ID = p.post_parent
LEFT JOIN {$wpdb->prefix}users AS u ON u.id = p.post_author
LEFT JOIN {$wpdb->prefix}usermeta as um ON um.user_id = u.id 
OUTER APPLY ( 
SELECT meta_key , meta_value
FROM usermeta as um1 
WHERE um1.meta_key IN ('affiliate_name', 'affiliate_phone_number' , 'affiliate_email' , 'affiliate_website')
AND um1.user_id = um.user_id
ORDER BY um1.meta_key ASC 
) as meta_data 
WHERE p.post_type = 'wc_user_membership'
AND p.post_status IN ('wcm-active', 'wcm-complimentary')
AND p2.post_type = 'wc_membership_plan'
AND p2.post_name LIKE '$membership_slug'
AND um.meta_key = 'affiliate_location'
AND um.meta_value <> ''