mysql - 每个俱乐部成员(member)的 WooCommerce 购买摘要 (mySQL)

Question

我正在为我们的俱乐部创建两个“用户管理”页面，以便他们可以查看每个成员(member)在 WooCommerce 中购买的商品。我已经完成了大部分 mySQL 查询，只需要完成一些小问题(有点超出我的理解范围)。

SELECT
    wp_users.ID,
    wp_users.display_name AS 'Name',
    wp_ihc_user_levels.level_id AS 'Roles',

    -- Check if User if full member or club visitor
    (CASE wp_usermeta.meta_key = 'club_member'
        WHEN wp_usermeta.meta_value LIKE 'Visitor' THEN 'Sponsored Visitor'
        WHEN wp_usermeta.meta_value LIKE 'Member' THEN 'Financial Member'
    END) AS 'Membership',

    -- Check if Member has purchased any items, by "category"
    (SELECT IF(COUNT(*) > 0, 'Yes', 'No') FROM wp_terms
            WHERE wp_users.ID AND name = 'Camping') AS 'Camping',
    (SELECT IF(COUNT(*) > 0, 'Yes', 'No') FROM wp_terms
            WHERE wp_users.ID AND name = 'Merchandise') AS 'Merchandise',
    (SELECT IF(COUNT(*) > 0, 'Yes', 'No') FROM wp_terms
            WHERE wp_users.ID AND name = 'Catering') AS 'Catering',
    (SELECT IF(COUNT(*) > 0, 'Yes', 'No') FROM wp_terms
            WHERE wp_users.ID AND name = 'Tickets') AS 'Tickets',

    -- Check if Member has booked any trips
    (SELECT IF(COUNT(*) > 0, 'Yes', 'No') FROM wp_em_bookings WHERE person_id = wp_users.ID) AS 'Trips'
FROM
    wp_users
    JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id
    JOIN wp_ihc_user_levels ON wp_users.ID = wp_ihc_user_levels.user_id
WHERE
    wp_usermeta.meta_value = (SELECT MAX(CASE WHEN meta_key = 'affiliated_club' THEN meta_value END) FROM wp_usermeta WHERE user_id = '17')

因此，SQL 查询将以调用该查询的 user_id 身份运行，然后它将在“affiliate_club”中查找所有其他俱乐部成员，并从顶部运行 SELECT 查询。

问题是:

1. 用户可以在“wp_ihc_user_levels.level_id”中拥有多个角色，但查询会返回“4”和“5”的 2 行，而不是 CONCAT，即同一行中的“4,5”。
2. CASE wp_usermeta.meta_key = 'club_member' 正在为所有条目返回“访客”，其中有一些正式成员(member)
3. 我不确定需要查询哪些 WooCommerce 表，以将类别链接到每个 wp_users.ID 购买进行计数。

第二个查询，我需要使用从查询一返回的“wp_users.ID”，以更详细地扩展每个 WooCommerce 购买，并按类别排序。

如果我进一步了解 WooCommerce 查询，我可能可以完成第二个查询的大部分内容。

提前致谢。

更新1:

好的，所以我能够解决第 1 点:

更改:

wp_ihc_user_levels.level_id AS 'Roles',

致:

(SELECT GROUP_CONCAT(level_id SEPARATOR ',')
    FROM wp_ihc_user_levels
    WHERE user_id = wp_users.ID) AS 'Roles',

现在“角色”显示值“2,3,5”等，而不是单个值

更新2:

好的，现在我已经解决了第 2 点:

更改:

(CASE wp_usermeta.meta_key = 'club_member'
    WHEN wp_usermeta.meta_value LIKE 'Visitor' THEN 'Sponsored Visitor'
    WHEN wp_usermeta.meta_value LIKE 'Member' THEN 'Financial Member'
END) AS 'Membership',

致:

(SELECT wp_usermeta.meta_value
    FROM wp_usermeta
    WHERE wp_usermeta.meta_key = 'club_member' AND wp_usermeta.user_id = wp_users.ID) AS 'Membership',

更新 3:SQL 查询已完成

好吧，这要么是一个极其复杂的 SQL 查询，要么是我的结构完全错误，但我的工作解决方案如下。

注意:%CURRENT_USER_ID% 占位符用于调用当前用户 ID，他们与其他成员(member)位于同一俱乐部。

SELECT DISTINCT
    wpdc_users.ID,
    wpdc_users.display_name AS 'Name',

    (SELECT wpdc_usermeta.meta_value
        FROM wpdc_usermeta
        WHERE wpdc_usermeta.meta_key = 'club_member' AND wpdc_usermeta.user_id = wpdc_users.ID) AS 'Membership',

    (SELECT GROUP_CONCAT(level_id SEPARATOR ',')
        FROM wpdc_ihc_user_levels
        WHERE user_id = wpdc_users.ID) AS 'Roles',

    (SELECT
        IF(SUM(wpdc_terms.name = 'Camping')     >0, 'Yes', 'No')

    FROM
        wpdc_postmeta
        JOIN wpdc_woocommerce_order_items ON wpdc_woocommerce_order_items.order_id = wpdc_postmeta.post_id
        JOIN wpdc_woocommerce_order_itemmeta ON wpdc_woocommerce_order_items.order_item_id = wpdc_woocommerce_order_itemmeta.order_item_id
        JOIN wpdc_term_relationships ON wpdc_term_relationships.object_id = wpdc_woocommerce_order_itemmeta.meta_value
        JOIN wpdc_terms ON wpdc_terms.term_id = wpdc_term_relationships.term_taxonomy_id

    WHERE
        wpdc_postmeta.meta_key = '_customer_user'
        AND wpdc_woocommerce_order_itemmeta.meta_key = '_product_id'
        AND wpdc_term_relationships.object_id = wpdc_woocommerce_order_itemmeta.meta_value
        AND wpdc_terms.term_id = wpdc_term_relationships.term_taxonomy_id
        AND wpdc_terms.term_id > 23
        AND wpdc_postmeta.meta_value =  wpdc_users.ID) AS 'Camping',

    (SELECT
        IF(SUM(wpdc_terms.name = 'Catering')    >0, 'Yes', 'No')

    FROM
        wpdc_postmeta
        JOIN wpdc_woocommerce_order_items ON wpdc_woocommerce_order_items.order_id = wpdc_postmeta.post_id
        JOIN wpdc_woocommerce_order_itemmeta ON wpdc_woocommerce_order_items.order_item_id = wpdc_woocommerce_order_itemmeta.order_item_id
        JOIN wpdc_term_relationships ON wpdc_term_relationships.object_id = wpdc_woocommerce_order_itemmeta.meta_value
        JOIN wpdc_terms ON wpdc_terms.term_id = wpdc_term_relationships.term_taxonomy_id

    WHERE
        wpdc_postmeta.meta_key = '_customer_user'
        AND wpdc_woocommerce_order_itemmeta.meta_key = '_product_id'
        AND wpdc_term_relationships.object_id = wpdc_woocommerce_order_itemmeta.meta_value
        AND wpdc_terms.term_id = wpdc_term_relationships.term_taxonomy_id
        AND wpdc_terms.term_id > 23
        AND wpdc_postmeta.meta_value =  wpdc_users.ID) AS 'Catering',

    (SELECT
        IF(SUM(wpdc_terms.name = 'Merchandise') >0, 'Yes', 'No')

    FROM
        wpdc_postmeta
        JOIN wpdc_woocommerce_order_items ON wpdc_woocommerce_order_items.order_id = wpdc_postmeta.post_id
        JOIN wpdc_woocommerce_order_itemmeta ON wpdc_woocommerce_order_items.order_item_id = wpdc_woocommerce_order_itemmeta.order_item_id
        JOIN wpdc_term_relationships ON wpdc_term_relationships.object_id = wpdc_woocommerce_order_itemmeta.meta_value
        JOIN wpdc_terms ON wpdc_terms.term_id = wpdc_term_relationships.term_taxonomy_id

    WHERE
        wpdc_postmeta.meta_key = '_customer_user'
        AND wpdc_woocommerce_order_itemmeta.meta_key = '_product_id'
        AND wpdc_term_relationships.object_id = wpdc_woocommerce_order_itemmeta.meta_value
        AND wpdc_terms.term_id = wpdc_term_relationships.term_taxonomy_id
        AND wpdc_terms.term_id > 23
        AND wpdc_postmeta.meta_value =  wpdc_users.ID) AS 'Merchandise',

    (SELECT
        IF(SUM(wpdc_terms.name = 'Tickets')     >0, 'Yes', 'No')

    FROM
        wpdc_postmeta
        JOIN wpdc_woocommerce_order_items ON wpdc_woocommerce_order_items.order_id = wpdc_postmeta.post_id
        JOIN wpdc_woocommerce_order_itemmeta ON wpdc_woocommerce_order_items.order_item_id = wpdc_woocommerce_order_itemmeta.order_item_id
        JOIN wpdc_term_relationships ON wpdc_term_relationships.object_id = wpdc_woocommerce_order_itemmeta.meta_value
        JOIN wpdc_terms ON wpdc_terms.term_id = wpdc_term_relationships.term_taxonomy_id

    WHERE
        wpdc_postmeta.meta_key = '_customer_user'
        AND wpdc_woocommerce_order_itemmeta.meta_key = '_product_id'
        AND wpdc_term_relationships.object_id = wpdc_woocommerce_order_itemmeta.meta_value
        AND wpdc_terms.term_id = wpdc_term_relationships.term_taxonomy_id
        AND wpdc_terms.term_id > 23
        AND wpdc_postmeta.meta_value =  wpdc_users.ID) AS 'Tickets',

    (SELECT IF(COUNT(*) > 0, 'Yes', 'No') FROM wpdc_em_bookings WHERE person_id = wpdc_users.ID) AS 'Trips / Events'
FROM
    wpdc_users
    JOIN wpdc_usermeta ON wpdc_usermeta.user_id = wpdc_users.ID
    JOIN wpdc_ihc_user_levels ON wpdc_users.ID = wpdc_ihc_user_levels.user_id

WHERE
    wpdc_usermeta.meta_value = (SELECT MAX(CASE WHEN meta_key = 'affiliated_club' THEN meta_value END) FROM wpdc_usermeta WHERE user_id = %CURRENT_USER_ID%)

我将标记此问题已解决，但如果有人能够建议是否可以压缩/最小化此问题，以便更有效地运行来检查每个类别购买，我将不胜感激。

提前致谢。

Answer 1

更新 3:SQL 查询已完成

好吧，这要么是一个极其复杂的 SQL 查询，要么是我的结构完全错误，但我的工作解决方案如下。

注意:%CURRENT_USER_ID% 占位符用于调用当前用户 ID，他们与其他成员(member)位于同一俱乐部。

SELECT DISTINCT
    wp_users.ID,
    wp_users.display_name AS 'Name',

    (SELECT wp_usermeta.meta_value
        FROM wp_usermeta
        WHERE wp_usermeta.meta_key = 'club_member' AND wp_usermeta.user_id = wp_users.ID) AS 'Membership',

    (SELECT GROUP_CONCAT(level_id SEPARATOR ',')
        FROM wp_ihc_user_levels
        WHERE user_id = wp_users.ID) AS 'Roles',

    (SELECT
        IF(SUM(wp_terms.name = 'Camping')     >0, 'Yes', 'No')

    FROM
        wp_postmeta
        JOIN wp_woocommerce_order_items ON wp_woocommerce_order_items.order_id = wp_postmeta.post_id
        JOIN wp_woocommerce_order_itemmeta ON wp_woocommerce_order_items.order_item_id = wp_woocommerce_order_itemmeta.order_item_id
        JOIN wp_term_relationships ON wp_term_relationships.object_id = wp_woocommerce_order_itemmeta.meta_value
        JOIN wp_terms ON wp_terms.term_id = wp_term_relationships.term_taxonomy_id

    WHERE
        wp_postmeta.meta_key = '_customer_user'
        AND wp_woocommerce_order_itemmeta.meta_key = '_product_id'
        AND wp_term_relationships.object_id = wp_woocommerce_order_itemmeta.meta_value
        AND wp_terms.term_id = wp_term_relationships.term_taxonomy_id
        AND wp_terms.term_id > 23
        AND wp_postmeta.meta_value =  wp_users.ID) AS 'Camping',

    (SELECT
        IF(SUM(wp_terms.name = 'Catering')    >0, 'Yes', 'No')

    FROM
        wp_postmeta
        JOIN wp_woocommerce_order_items ON wp_woocommerce_order_items.order_id = wp_postmeta.post_id
        JOIN wp_woocommerce_order_itemmeta ON wp_woocommerce_order_items.order_item_id = wp_woocommerce_order_itemmeta.order_item_id
        JOIN wp_term_relationships ON wp_term_relationships.object_id = wp_woocommerce_order_itemmeta.meta_value
        JOIN wp_terms ON wp_terms.term_id = wp_term_relationships.term_taxonomy_id

    WHERE
        wp_postmeta.meta_key = '_customer_user'
        AND wp_woocommerce_order_itemmeta.meta_key = '_product_id'
        AND wp_term_relationships.object_id = wp_woocommerce_order_itemmeta.meta_value
        AND wp_terms.term_id = wp_term_relationships.term_taxonomy_id
        AND wp_terms.term_id > 23
        AND wp_postmeta.meta_value =  wp_users.ID) AS 'Catering',

    (SELECT
        IF(SUM(wp_terms.name = 'Merchandise') >0, 'Yes', 'No')

    FROM
        wp_postmeta
        JOIN wp_woocommerce_order_items ON wp_woocommerce_order_items.order_id = wp_postmeta.post_id
        JOIN wp_woocommerce_order_itemmeta ON wp_woocommerce_order_items.order_item_id = wp_woocommerce_order_itemmeta.order_item_id
        JOIN wp_term_relationships ON wp_term_relationships.object_id = wp_woocommerce_order_itemmeta.meta_value
        JOIN wp_terms ON wp_terms.term_id = wp_term_relationships.term_taxonomy_id

    WHERE
        wp_postmeta.meta_key = '_customer_user'
        AND wp_woocommerce_order_itemmeta.meta_key = '_product_id'
        AND wp_term_relationships.object_id = wp_woocommerce_order_itemmeta.meta_value
        AND wp_terms.term_id = wp_term_relationships.term_taxonomy_id
        AND wp_terms.term_id > 23
        AND wp_postmeta.meta_value =  wp_users.ID) AS 'Merchandise',

    (SELECT
        IF(SUM(wp_terms.name = 'Tickets')     >0, 'Yes', 'No')

    FROM
        wp_postmeta
        JOIN wp_woocommerce_order_items ON wp_woocommerce_order_items.order_id = wp_postmeta.post_id
        JOIN wp_woocommerce_order_itemmeta ON wp_woocommerce_order_items.order_item_id = wp_woocommerce_order_itemmeta.order_item_id
        JOIN wp_term_relationships ON wp_term_relationships.object_id = wp_woocommerce_order_itemmeta.meta_value
        JOIN wp_terms ON wp_terms.term_id = wp_term_relationships.term_taxonomy_id

    WHERE
        wp_postmeta.meta_key = '_customer_user'
        AND wp_woocommerce_order_itemmeta.meta_key = '_product_id'
        AND wp_term_relationships.object_id = wp_woocommerce_order_itemmeta.meta_value
        AND wp_terms.term_id = wp_term_relationships.term_taxonomy_id
        AND wp_terms.term_id > 23
        AND wp_postmeta.meta_value =  wp_users.ID) AS 'Tickets',

    (SELECT IF(COUNT(*) > 0, 'Yes', 'No') FROM wp_em_bookings WHERE person_id = wp_users.ID) AS 'Trips / Events'
FROM
    wp_users
    JOIN wp_usermeta ON wp_usermeta.user_id = wp_users.ID
    JOIN wp_ihc_user_levels ON wp_users.ID = wp_ihc_user_levels.user_id

WHERE
    wp_usermeta.meta_value = (SELECT MAX(CASE WHEN meta_key = 'affiliated_club' THEN meta_value END) FROM wp_usermeta WHERE user_id = %CURRENT_USER_ID%)

我将标记此问题已解决，但如果有人能够建议是否可以压缩/最小化此问题，以便更有效地运行来检查每个类别购买，我将不胜感激。

提前致谢。