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!
