I'm attempting to run a search that sorts through a user profile with Advanced Custom Fields attached. I'm using the WP user query. One of my fields I'm searching against is a repeater field (business_information) and its subfields (business_name).
I'm using the information provided on this ACF documentation page, #4 Sub Custom Field Values
So, with my variables, it looks like this:
function my_posts_where( $where ) {
$where = str_replace("meta_key = 'business_information_%", "meta_key LIKE 'business_information_%", $where);
return $where;
}
add_filter('posts_where', 'my_posts_where');
if( !empty( $_GET['usersearch'] ) ){
$usersearch = stripslashes( trim($_GET['usersearch']) );
// WP_User_Query arguments
$args = array(
'role' => 'Subscriber',
'meta_query' => array(
array(
'key' => 'membership_class',
'value' => 'Full',
'compare' => '=',
'type' => 'CHAR',
),
array(
'relation' => 'OR',
array(
'key' => 'first_name',
'value' => $usersearch,
'compare' => 'LIKE'
),
array(
'key' => 'last_name',
'value' => $usersearch,
'compare' => 'LIKE'
),
array(
'key' => 'personal_city',
'value' => $usersearch,
'compare' => 'LIKE',
'type' => 'CHAR',
),
array(
'key' => 'personal_province',
'value' => $usersearch,
'compare' => 'LIKE',
'type' => 'CHAR',
),
array(
'key' => 'treatments',
'value' => $usersearch,
'compare' => 'LIKE',
),
array(
'key' => 'business_information_%_business_name',
'value' => $usersearch,
'compare' => 'LIKE',
),
),
),
);
The request output:
SELECT DISTINCT SQL_CALC_FOUND_ROWS wp_users.* FROM wp_users INNER JOIN wp_usermeta ON ( wp_users.ID = wp_usermeta.user_id ) INNER JOIN wp_usermeta AS mt1 ON ( wp_users.ID = mt1.user_id ) INNER JOIN wp_usermeta AS mt2 ON ( wp_users.ID = mt2.user_id ) WHERE 1=1 AND (
(
(
( wp_usermeta.meta_key = 'membership_class' AND wp_usermeta.meta_value = 'Full' )
AND
(
( mt1.meta_key = 'first_name' AND mt1.meta_value LIKE '%name%' )
OR
( mt1.meta_key = 'last_name' AND mt1.meta_value LIKE '%name%' )
OR
( mt1.meta_key = 'personal_city' AND mt1.meta_value LIKE '%name%' )
OR
( mt1.meta_key = 'personal_province' AND mt1.meta_value LIKE '%name%' )
OR
( mt1.meta_key = 'treatments' AND mt1.meta_value LIKE '%name%' )
OR
( mt1.meta_key = 'business_information_%_business_name' AND mt1.meta_value LIKE '%name%' )
)
)
AND
(
(
( mt2.meta_key = 'wp_capabilities' AND mt2.meta_value LIKE '%\"Subscriber\"%' )
)
)
)
) ORDER BY user_login ASC
Now, I'll be the first to admit I don't know what the hell I'm doing, but the rest of the searches are working exactly as I want. It's just the business repeater field that's not getting into the search query properly. From what I understand, the above function is supposed to be replacing that piece of the db request, but it's not doing that, and I've got NO idea why or how to make it work, or what it's supposed to actually look like in the end. I've been up and down google on this and just can't find an answer. Everything seems related to posts, not users, and I'm wondering if that's my hangup? Does anyone know anything that can help?
I'm attempting to run a search that sorts through a user profile with Advanced Custom Fields attached. I'm using the WP user query. One of my fields I'm searching against is a repeater field (business_information) and its subfields (business_name).
I'm using the information provided on this ACF documentation page, #4 Sub Custom Field Values
So, with my variables, it looks like this:
function my_posts_where( $where ) {
$where = str_replace("meta_key = 'business_information_%", "meta_key LIKE 'business_information_%", $where);
return $where;
}
add_filter('posts_where', 'my_posts_where');
if( !empty( $_GET['usersearch'] ) ){
$usersearch = stripslashes( trim($_GET['usersearch']) );
// WP_User_Query arguments
$args = array(
'role' => 'Subscriber',
'meta_query' => array(
array(
'key' => 'membership_class',
'value' => 'Full',
'compare' => '=',
'type' => 'CHAR',
),
array(
'relation' => 'OR',
array(
'key' => 'first_name',
'value' => $usersearch,
'compare' => 'LIKE'
),
array(
'key' => 'last_name',
'value' => $usersearch,
'compare' => 'LIKE'
),
array(
'key' => 'personal_city',
'value' => $usersearch,
'compare' => 'LIKE',
'type' => 'CHAR',
),
array(
'key' => 'personal_province',
'value' => $usersearch,
'compare' => 'LIKE',
'type' => 'CHAR',
),
array(
'key' => 'treatments',
'value' => $usersearch,
'compare' => 'LIKE',
),
array(
'key' => 'business_information_%_business_name',
'value' => $usersearch,
'compare' => 'LIKE',
),
),
),
);
The request output:
SELECT DISTINCT SQL_CALC_FOUND_ROWS wp_users.* FROM wp_users INNER JOIN wp_usermeta ON ( wp_users.ID = wp_usermeta.user_id ) INNER JOIN wp_usermeta AS mt1 ON ( wp_users.ID = mt1.user_id ) INNER JOIN wp_usermeta AS mt2 ON ( wp_users.ID = mt2.user_id ) WHERE 1=1 AND (
(
(
( wp_usermeta.meta_key = 'membership_class' AND wp_usermeta.meta_value = 'Full' )
AND
(
( mt1.meta_key = 'first_name' AND mt1.meta_value LIKE '%name%' )
OR
( mt1.meta_key = 'last_name' AND mt1.meta_value LIKE '%name%' )
OR
( mt1.meta_key = 'personal_city' AND mt1.meta_value LIKE '%name%' )
OR
( mt1.meta_key = 'personal_province' AND mt1.meta_value LIKE '%name%' )
OR
( mt1.meta_key = 'treatments' AND mt1.meta_value LIKE '%name%' )
OR
( mt1.meta_key = 'business_information_%_business_name' AND mt1.meta_value LIKE '%name%' )
)
)
AND
(
(
( mt2.meta_key = 'wp_capabilities' AND mt2.meta_value LIKE '%\"Subscriber\"%' )
)
)
)
) ORDER BY user_login ASC
Now, I'll be the first to admit I don't know what the hell I'm doing, but the rest of the searches are working exactly as I want. It's just the business repeater field that's not getting into the search query properly. From what I understand, the above function is supposed to be replacing that piece of the db request, but it's not doing that, and I've got NO idea why or how to make it work, or what it's supposed to actually look like in the end. I've been up and down google on this and just can't find an answer. Everything seems related to posts, not users, and I'm wondering if that's my hangup? Does anyone know anything that can help?
Share Improve this question asked Apr 18, 2017 at 16:41 FayeFaye 6815 silver badges16 bronze badges 3 |1 Answer
Reset to default 2Not sure if you ever found the answer, but just in case, I just did :)
There's a hook I (finally!) found that lets you do this and works in a similar way to posts_where. The hook is pre_user_query. Here's an example on the Codex: https://developer.wordpress/reference/hooks/pre_user_query/#user-contributed-notes
With the above, your function could now look like this:
function user_posts_where( $user_query ) {
$user_query->query_where = str_replace("meta_key = 'business_information_$", "meta_key LIKE 'business_information_%", $user_query->query_where);
return $user_query;
}
add_filter('pre_user_query', 'user_posts_where');
Please note that ACF now recommends using the dollar sign as the wildcard, hence business_information_$.
You'd also need to change your meta_query to the below, using business_information_$_business_name instead:
array(
'key' => 'business_information_$_business_name',
'value' => $usersearch,
'compare' => 'LIKE'
)
my_posts_where()function is adding a filter toposts_wherewhich looks to like it may be a hook forWP_Queryand not forWP_Users_Query– Stephen S. Commented Apr 18, 2017 at 17:35