Wordpress Memberships - Get all active members of a plan who have a non empty value for a particular meta key. Also retrieve additional meta data

user2655393 from

I am creating a google map with markers for all my active WP Memberships members. First I need to query the database to get all ACTIVE (and/or COMPLIMENTARY) members of a particular Membership Plan (which is passed to the function), but they also must have a non-empty value for a custom meta_key affiliate_location. There is a frontend form available for my members where they can opt-in to the map by entering their location (and other details) into the form, saving the information as user meta. If they have not entered their location, they will have an empty meta_value for the meta_key affiliate_location.

I have already accomplished the above. My function can check to see whether the user is an active/complimentary Member, and then check to see if they have a non-empty affiliate_location meta_value, and return a list of those users, which includes their user_id, display_name, and the affiliate_location meta_key and meta_value (the meta_value is what I will use to place their marker on the google map).

What I am trying to accomplish now is to return additional user meta data if they pass the above tests. If they are an active member and have entered a location, then I also want to return (in the same query) the meta values for the following meta keys: affiliate_name, affiliate_email, affiliate_website, affiliate_phone_number.

I tried the below SQL query, with help from @LoicTheAztec's answer to this question, tweaking the SQL to also check for the affiliate_location meta value.

I tried aliasing the User Meta table for the other meta key/value pairs that I need to return. You can see this on the last LEFT JOIN ... usermeta AS um1... line, and then listing um1.meta_key in the first expression, and finally including the condition AND um1.meta_key = 'affiliate_name', but the results only ever include one meta_key and one meta_value property. I can't figure out how to return the additional meta data that I need for each matching user. Any help is MUCH APPRECIATED!

// 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'
    " );
}

Here are the results I'm getting from the above attempt:

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

In this case, as you can see, the results include the affiliate_name meta key/value. Which is only part of what I want. If I remove the um1.meta_key, um1.meta_value from the first SQL expression, then I get the location meta key/value.

Basically, for all matching users ('active' members, who have store affiliate_location data) I need to also return their Name, Email, Website, and Phone Number, in the same query. Otherwise, I'll have to run one query to get all matching users, and then use their User_ID values to run hundreds of separate queries in order to collect all their affiliate data. Is there a way to get all that data in one query?

Thank you so much in advance for your assistance!


UPDATE:

I have implemented @Edward's solution. He proposed 2 solutions, one with a standard JOIN sub query, and another with a more advanced OUTER APPLY, but I discovered that my MySQL database does not accept the OUTER APPLY syntax, so I chose to implement the first solution, with a couple corrections (there was a typo with website vs web_site) and an additional condition at the end of the main WHERE clause. Here is what I tried:

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

This solution accomplished exactly what I wanted! Without the final AND user_meta.loc_value <> '' condition, the returned array was including all "active" users even if they had empty affiliate_location values. By adding the final condition, those users were filtered out.

Thanks so much for your help @Edward It is very much appreciated! I learned a lot. Cheers!

php mysql sql wordpress woocommerce

Answers

answered 3 months ago Edward #1

I think I found your issue. The um1.meta_value is returning NULL because you're not filtering um1. based on non-emptiness of the affiliate_location meta-value rule like you are for um..

Specifically, these lines in the WHERE clause are not doing what you want them to do.

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

Since in your FROM clause, you are using two LEFT OUTER JOIN's.

I do not know your data structure, but adding something to the WHERE like this may work:

  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 

Does that help at all?

07/12

So in reponse to your comment, I will expand my answer here. The LEFT JOINS that you are doing are indeed pulling the same columns from usermeta, but the important thing is the SQL treats these as two separate objects.

This is imporant because the WHERE statement AND um.meta_key = affiliate_location will not have an affect on the usermeta AS um1 columns returned by SELECT ... um1.meta_key , um1.meta_value ....

It is not doing what you expect it to be doing.

In order for um1 to return only the columns you are interested in, you'll need to filter um as you have already done, and then explicitly force um1.id to match um.id.

You can try this as well.

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 

I remade your original query a bit, this should hopefully return what you are asking for.

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

This may be pretty inefficient due to all the LEFT JOINS.

07/13 - The above solution with LEFT OUTER JOIN works for mySQL. If someone is attempting something similar on SQLServer, then they may have more luck with the OUTER APPLY method so I am leaving it in this answer.

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