WordPress WP_User_query generate SQL Problem

[hasanyuksektepe]

The code I use is as follows.

<?php

    $list_number = 20;
    $paged       = $_GET['paged'] ?? 1;
    if($paged == 1){
        $paged = 0;
    }
    else{
        $paged = ($paged - 1) * $list_number;
    }

    $user_arg = [
        'role__in' => ['customer'],
        'paged'    => ($paged + 1),
        'offset'   => ($paged + 1),
        'number'   => $list_number,
        'orderby'  => 'order',
        'order'    => 'DESC',
    ];

    $search_text = esc_attr(trim($_GET['s']));

    if(!empty($search_text)){
        $user_arg['search']         = '*'.$search_text.'*';
    }

    $user_query = new WP_User_Query($user_arg);

    print_r($user_query);

but it doesn't give me results.

This is the sql it created.

SELECT SQL_CALC_FOUND_ROWS wp_users.* FROM wp_users INNER JOIN wp_usermeta ON ( wp_users.ID = wp_usermeta.user_id ) WHERE 1=1 AND ( 
  ( 
    ( wp_usermeta.meta_key = 'wp_capabilities' AND wp_usermeta.meta_value LIKE '{931f21fb875f553b7863c5e753239abe423e792b6f5ad7e8c4386d251442470c}\"customer\"{931f21fb875f553b7863c5e753239abe423e792b6f5ad7e8c4386d251442470c}' )
  )
) AND (user_login LIKE '{931f21fb875f553b7863c5e753239abe423e792b6f5ad7e8c4386d251442470c}zua.apex{931f21fb875f553b7863c5e753239abe423e792b6f5ad7e8c4386d251442470c}' OR user_url LIKE '{931f21fb875f553b7863c5e753239abe423e792b6f5ad7e8c4386d251442470c}zua.apex{931f21fb875f553b7863c5e753239abe423e792b6f5ad7e8c4386d251442470c}' OR user_email LIKE '{931f21fb875f553b7863c5e753239abe423e792b6f5ad7e8c4386d251442470c}zua.apex{931f21fb875f553b7863c5e753239abe423e792b6f5ad7e8c4386d251442470c}' OR user_nicename LIKE '{931f21fb875f553b7863c5e753239abe423e792b6f5ad7e8c4386d251442470c}zua.apex{931f21fb875f553b7863c5e753239abe423e792b6f5ad7e8c4386d251442470c}' OR display_name LIKE '{931f21fb875f553b7863c5e753239abe423e792b6f5ad7e8c4386d251442470c}zua.apex{931f21fb875f553b7863c5e753239abe423e792b6f5ad7e8c4386d251442470c}' OR user_url LIKE '{931f21fb875f553b7863c5e753239abe423e792b6f5ad7e8c4386d251442470c}zua.apex{931f21fb875f553b7863c5e753239abe423e792b6f5ad7e8c4386d251442470c}' OR display_name LIKE '{931f21fb875f553b7863c5e753239abe423e792b6f5ad7e8c4386d251442470c}zua.apex{931f21fb875f553b7863c5e753239abe423e792b6f5ad7e8c4386d251442470c}') ORDER BY user_login DESC LIMIT 1, 20

{931f21fb875f553b7863c5e753239abe423e792b6f5ad7e8c4386d251442470c}

I have no idea why it's making them. When I change this expression to %, it finds results.

I tried changing the expressions but failed.

[craigfrancis]

Hi Hasan,

Just to confirm, are you looking at the SQL from $user_query->request?

At this point it still uses the "placeholder escape string", which is (usually) a random value surrounded by "{}".

It's used to "escape anything that resembles a printf() placeholder", where it's simply a search and replace for the "%" character.

It is a bit of a weird feature, where I believe it was originally designed to allow the SQL string to go though wpdb::prepare() multiple times.

Just before the SQL is actually sent to the database, all of these are converted back to a "%", via wpdb::remove_placeholder_escape(); and this is done via a 'query' filter.

It sounds like you're doing that last step yourself, so you can run the SQL manually.

To get the results normally, you can call $user_query->get_results().

[hasanyuksektepe]

Hello,

I understand what you are saying and thank you for your explanation.

So, when I search the users by email, why does not the result come up even though it is searched in the user_email column?

[craigfrancis]

Hi Hasan,

Maybe you only have 1 record that matches, and it's being excluded by:

<?php
    'paged'    => ($paged + 1),
    'offset'   => ($paged + 1),

Where your SQL contains LIMIT 1, 20.

Normally the offset starts at 0; and when it comes to this config, you would either use 'paged' or 'offset' (not both).

---

If that's not it, I've taken your output SQL, replaced the placeholder escape string, and re-formatted, so you can check if the query works:

SELECT
  SQL_CALC_FOUND_ROWS wp_users.*
FROM
  wp_users
INNER JOIN
  wp_usermeta ON ( wp_users.ID = wp_usermeta.user_id )
WHERE
  1=1 AND
  ( 
    ( 
      (
        wp_usermeta.meta_key = 'wp_capabilities' AND 
        wp_usermeta.meta_value LIKE '%\"customer\"%'
      )
    )
  ) AND (
    user_login LIKE '%zua.apex%' OR 
    user_url LIKE '%zua.apex%' OR 
    user_email LIKE '%zua.apex%' OR 
    user_nicename LIKE '%zua.apex%' OR 
    display_name LIKE '%zua.apex%' OR 
    user_url LIKE '%zua.apex%' OR 
    display_name LIKE '%zua.apex%'
  )
ORDER BY
  user_login DESC
LIMIT
  1, 20

[hasanyuksektepe]

Thanks. Problem Solved.

[craigfrancis]

np, hope the rest of your project goes well.