每个俱乐部成员购买WooCommerce的摘要(WooCommerce)

Question

我正在为我们的俱乐部创建两个“用户管理”页面，这样他们就可以看到他们每个成员在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行，而不是在同一行中返回"4,5”。
2. CASE wp_usermeta.meta_key = 'club_member‘为所有条目返回“访问者”，其中有一些正式成员
3. 我不确定需要查询哪些WooCommerce表，以便将类别链接到每个wp_users.ID采购以供计数。

第二个查询，我需要使用从查询1返回的"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查询，要么是我的结构完全错误，但我的工作解决方案如下。

注意：-- %CURRENT_USER_ID%占位符用于调用当前的用户ID，如果它们与其他成员在同一个俱乐部中存在。

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查询，要么是我的结构完全错误，但我的工作解决方案如下。

注意：-- %CURRENT_USER_ID%占位符用于调用当前的用户ID，如果它们与其他成员在同一个俱乐部中存在。

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%)

我将标记这一解决方案，但如果有人能够建议这是否可以压缩/最小化，以更有效地运行，以检查每个类别购买。

提前谢谢。