[s2Member-List /] Search by Custom Profile Field

[jaswrks]

@clavaque writes...

Will [s2Member-List /] support search by profile field?

No, only by columns in the wp_users table at this time, since that's what is supported by WP_User_Query. However, we could alter this to improve things in a future release.

[jaswrks]

@raamdev If you feel like taking a look at this, please let me know. I'd love it if you could help me to make this possible. Here is the routine that we'll need to improve to get this working.

What we need to do is find a way to get this method to search additional user fields; including custom field values stored by s2Member. I see this being quite challenging, but if you have some thoughts on the topic I'd love to hear them.

See also: http://www.primothemes.com/forums/viewtopic.php?t=15658#p48878

[raamdev]

Yes, I'll take a look at this and see if I can't get some workable code pushed up to a branch.

By the looks of it, this will require running custom MySQL queries, is that correct? And if so, it probably makes sense to implement caching with WordPress transients to improve performance, right?

Assigning this to myself. I'll plan to take a look at this later this week or early next week.

[raamdev]

Bump @jaswsinc (see questions above).

[jaswrks]

By the looks of it, this will require running custom MySQL queries, is that correct? And if so, it probably makes sense to implement caching with WordPress transients to improve performance, right?

Yes, that sounds like a plan to me. Honestly though, I have not thought this through completely. That is, I don't have anything specific in mind for this yet. Thus, whatever you can come up with that is a workable solution would be OK with me, since I don't really know what exactly needs to happen here just yet.

If you start work on this I'd encourage you to just start pushing some ideas/concepts that you have in mind and maybe we can hash things out together. Whatever you think is fine :-) The outline you gave sounds like a great place to start.

[clavaque]

Another one requesting this: https://websharks.zendesk.com/agent/#/tickets/3896

[raamdev]

I'll take a closer look at this today and see if I can come up with a workable solution.

[raamdev]

I'm not sure there's going to be any optimized way of handling this. If we assume that we'll need this to work on a site with thousands (perhaps tens of thousands) of users, then we're likely going to run into some serious performance concerns.

---

The only way I can see this working is to run a routine prior to calling WP_User_Query($args) that runs an SQL query to search all users for an s2Member Custom Field with a value of the search term (@jaswsinc does my regex syntax look right here?):

SELECT `user_id` as `ID` FROM `wp_usermeta` WHERE `meta_key` = 'wp_s2member_custom_fields' AND `meta_value` REGEXP '.*;s:[0-9]+:".*SEARCHTERM.*".*'

That would give us all users who have an s2Member Custom Field with SEARCHTERM anywhere in any Custom Field value.

Then, with that list of User IDs, we could use the WP_User_Query include parameter to include that list of User IDs in the query that gets run, so that those users would be included (in addition to any other users that were found by the query parameters):

// .....
if($args['number'] < 1) $args['number'] = 1; // Make sure this is always >= 1.
$args['offset'] = ($page - 1) * $args['number']; // Calculate dynamically.

// --------------------------------------------------------

/* Search database for any users with s2Member Custom Fields that contain SEARCHTERM in value */
global $wpdb;
$users = $wpdb->get_results ("SELECT `user_id` as `ID` FROM `" . $wpdb->usermeta . "` WHERE `meta_key` = '" . $wpdb->prefix . "s2member_custom_fields' AND `meta_value` REGEXP '.*;s:[0-9]+:\".*SEARCHTERM.*\".*'");

/* Build an array of all User IDs whose s2Member Custom Fields contain SEARCHTERM in their value */
$include_user_ids = array();
if (is_array ($users) && count ($users) > 0)
    foreach ($users as $user)
        $include_user_ids[] = $user->ID;

/* Add User IDs to a new include argument for WP_User_Query */
if(!empty($include_user_ids))
    $args[] = array('include' => $include_user_ids);

// --------------------------------------------------------

$query = new WP_User_Query($args);
return array('query' => $query, 'pagination' => self::paginate($page, (integer)$query->get_total(), $args['number']));

This is untested code. I just wrote this off the top of my head as an idea for how this might work.

Thoughts @jaswsinc? Do you want me to try implementing this, or are there bigger issues (perhaps related to performance) that you foresee?

[jaswrks]

The only way I can see this working is to run a routine prior to calling WP_User_Query($args) that runs an SQL query to search all users for an s2Member Custom Field with a value of the search term (@jaswsinc does my regex syntax look right here?):

I agree. I think that's the best we can do. It sounds like a great plan to me!

For the regex, you'll want something like this...

(^|;)s\\:[0-9]+\\:\"[KEY]\";s\\:[0-9]+\\:\"[VALUE]\"

In PHP, that would be done like this...

$search_col = 'country'; // Search for the `country` field.
$search_val = 'US'; // Search for this value in the `country` field.

$regex = '(^|;)s\:[0-9]+\:"'.preg_quote($search_col).'";s\:[0-9]+\:"'.preg_quote($search_val).'"';
$sql_regex = "REGEXP '".esc_sql($regex)."'";

// e.g. REGEXP '(^|;)s\\:[0-9]+\\:\"country\";s\\:[0-9]+\\:\"US\"'

---

It might be possible to optimize this a little bit further by limiting the number of rows that need to be tested via regex, to those which match a simpler (slightly faster) LIKE match first.

WHERE `col` LIKE '%[VALUE]%' AND `col` REGEXP ...

[raamdev]

$search_col = 'country'; // Search for thecountryfield.

If I read the code in member-list.inc.php correctly, the search automatically applies to all the fields (name, email, etc.), is that correct? That's why I was assuming with my regex that we didn't care what s2Member Custom Field we searched, but rather just the values inside all s2Member Custom Fields.

Am I misunderstanding something about how the shortcode works?

[jaswrks]

WordPress itself takes a search_columns parameter also, and this is supported in member-list.inc.php via the search_columns attribute in the [s2Member-List /] shortcode.

By default, we search all columns. However, it's possible to provide a comma-delimited list of columns to search in the wp_users table if you wish to be more specific. What I'd like to add is the ability to search through custom fields generated with s2Member too.

So for instance, something like this could search all fields, including s2 custom fields (all of them)...

[s2Member-List search="[KEYWORDS]" enable_list_search="yes" /]

Something like this would search only specific fields...

• user_login, in the wp_users table (already supported)
• country in the s2member custom fields for each user.

[s2Member-List search="[KEYWORDS]" search_columns="user_login, s2member_custom_field_country" enable_list_search="yes" /]

[raamdev]

@jaswsinc

By default, we search all columns. However, it's possible to provide a comma-delimited list of columns to search in the wp_users table if you wish to be more specific.

I'm looking over the code for this in member-list.inc.php and I see that default search_columns are set. So when you say "by default, we search all columns", do you mean that by default we search those default columns ('ID', 'user_login', 'user_email', 'user_url', 'user_nicename')?

I'm trying to figure out what the conditional would be to check if we're searching "all columns" (i.e., the search_columns attribute was not supplied to the shortcode).

Since we're setting default values for that argument, I can't just search if the search_columns argument is empty. But if I check if the search_columns argument contains only the defaults ('ID', 'user_login', 'user_email', 'user_url', 'user_nicename'), how would I know if the user supplied those exact columns with the intention of excluding the s2Member Custom Fields from their search?

Am I missing something here?