时间戳

首页 > 解决方案 > Wordpress Memberships - 获取计划中对特定元键具有非空值的所有活动成员。还检索其他元数据

php - Wordpress Memberships - 获取计划中对特定元键具有非空值的所有活动成员。还检索其他元数据

问题描述

我正在为我所有活跃的 WP Memberships 成员创建一个带有标记的谷歌地图。首先，我需要查询数据库以获取特定会员计划（传递给函数）的所有 ACTIVE（和/或 COMPLIMENTARY）成员，但它们还必须具有自定义 meta_key 的非空值affiliate_location。我的成员可以使用一个前端表单，他们可以通过在表单中​​输入他们的位置（和其他详细信息）来选择加入地图，并将信息保存为用户元数据。如果他们没有输入他们的位置，他们的 meta_key 将有一个空的 meta_value affiliate_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元键/值。这只是我想要的一部分。如果我um1.meta_key, um1.meta_value从第一个 SQL 表达式中删除 ，那么我会得到位置元键/值。

基本上，对于所有匹配的用户（拥有商店affiliate_location数据的“活跃”成员），我还需要在同一个查询中返回他们的姓名、电子邮件、网站和电话号码。否则，我将不得不运行一个查询来获取所有匹配的用户，然后使用他们的 User_ID 值来运行数百个单独的查询，以收集他们所有的附属数据。有没有办法在一个查询中获取所有数据？

非常感谢您的帮助！

更新：

我已经实施了@Edward 的解决方案。他提出了 2 个解决方案，一个带有标准的 JOIN 子查询，另一个带有更高级的 OUTER APPLY，但是我发现我的 MySQL 数据库不接受 OUTER APPLY 语法，所以我选择实现第一个解决方案，并进行了一些更正（与websitevs有一个错字web_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 非常感谢！我学到了很多。干杯!

标签： phpmysqlsqlwordpresswoocommerce

解决方案

我想我找到了你的问题。um1.meta_value正在返回，NULL因为您没有像 for 那样基于元值规则um1.的非空性进行过滤。 affiliate_locationum.

具体来说，子句中的这些行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不会对.usermeta AS um1返回的列产生影响SELECT ... um1.meta_key , um1.meta_value ...。

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

为了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 <> ''

推荐阅读