sql query get only 1 value of many in a joined table

1 Views

This is about a WordPress website.

There are 2 tables. 1 called wp_users and 1 called wp_usermeta.
The 2 tables are linked by wp_users.id = wp_usermeta.user_id.
Usermeta has a column named meta_key and meta_value. 1 user has multiple meta_key‘s, but each meta_key also has a meta_value.

Read More

What I would like to have is as following,

I’d like to select the ID, user_login & user_email from wp_users and meta_value from wp_usermeta where meta_key = ‘foobar’ . However, the query needs to be made in such a way that if someone uses the search, it will still find the same but only if user_login, user_email or meta_value contains the searched string.

Here is what I currently have. I have tried joining the tables, but I’m quite unsure of how to do this exactly. 

public function get_users($get_from, $per_page, $search = null) {
    global $wpdb;
    $sql =      "SELECT ID, user_login username, user_email FROM {$wpdb->prefix}users";

    if(isset($search)) {
        $sql .= " WHERE user_login LIKE '%" . esc_sql($wpdb->esc_like($search)) . "%'";
        $sql .= " OR user_email LIKE '%" . esc_sql($wpdb->esc_like($search)) . "%'";
    }

    if(!empty($_REQUEST['orderby'])){ 
        $sql .= " ORDER BY " . esc_sql($_REQUEST['orderby']);
        $sql .= !empty($_REQUEST['order']) ? ' ' . esc_sql($_REQUEST['order']) : ' ASC';
    }

    $sql .= " LIMIT $get_from , $per_page";
    echo $sql;

    $result = $wpdb-> get_results($sql, 'ARRAY_A');
    $this->total_users = $wpdb->num_rows;

    return $result;
}
Post Views: 1

Related posts

1 comment

  1. I would rather use union, it is faster in my experience than a series of or conditions.

    SELECT ID, user_login username, user_email
FROM users
WHERE user_login LIKE '%...%'
UNION DISTINCT
SELECT ID, user_login username, user_email
FROM users
WHERE user_email LIKE '%...%'
UNION DISTINCT
SELECT ID, user_login username, user_email
FROM users
INNER JOIN wp_usermeta ON users.id=wp_usermeta.user_id
WHERE wp_usermeta.meta_key='foobar' and wp_usermeta.meta_value LIKE '%...%'

    Alternatively, you can concatenate the 3 fields into one and to the like on the concatenated string:

    SELECT ID, user_login username, user_email
FROM users
LEFT JOIN wp_usermeta ON users.id=wp_usermeta.user_id
AND wp_usermeta.meta_key='foobar' 
WHERE concat_ws(',',user_login, user_email, coalesce(wp_usermeta.meta_value,'') LIKE '%...%'

Comments are closed.