I'm looking to query a bunch of user and usermeta data into a single table. I'm able to do so by creating multiple temporary tables and a pivot:

    create temporary table meta

    select u.ID, u.user_login, meta_key, meta_value from wp_users u
    inner join wp_usermeta m on m.user_id = u.ID;


    create temporary table users

    select ID, user_login,
        MAX(CASE WHEN meta_key = 'first_name' THEN meta_value END) as first_name,
        MAX(CASE WHEN meta_key = 'middle_name' THEN meta_value END) as middle_name,
        MAX(CASE WHEN meta_key = 'last_name' THEN meta_value END) as last_name,
        MAX(CASE WHEN meta_key = 'university_program' THEN meta_value END) as university_program, 
        MAX(CASE WHEN meta_key = 'wp_capabilities' THEN meta_value END) as wp_capabilities 
    from meta
    group by ID, user_login;

    drop temporary table meta; 

    select * from users 
    where wp_capabilities like '%um_student%';

    drop temporary table users;  

However, is there a better and shorter way to run this query without the need to create temporary tables or the pivot?

I'm looking to query a bunch of user and usermeta data into a single table. I'm able to do so by creating multiple temporary tables and a pivot:

    create temporary table meta

    select u.ID, u.user_login, meta_key, meta_value from wp_users u
    inner join wp_usermeta m on m.user_id = u.ID;


    create temporary table users

    select ID, user_login,
        MAX(CASE WHEN meta_key = 'first_name' THEN meta_value END) as first_name,
        MAX(CASE WHEN meta_key = 'middle_name' THEN meta_value END) as middle_name,
        MAX(CASE WHEN meta_key = 'last_name' THEN meta_value END) as last_name,
        MAX(CASE WHEN meta_key = 'university_program' THEN meta_value END) as university_program, 
        MAX(CASE WHEN meta_key = 'wp_capabilities' THEN meta_value END) as wp_capabilities 
    from meta
    group by ID, user_login;

    drop temporary table meta; 

    select * from users 
    where wp_capabilities like '%um_student%';

    drop temporary table users;  

However, is there a better and shorter way to run this query without the need to create temporary tables or the pivot?

• user-meta
• sql

Share Improve this question asked Jul 10, 2019 at 22:50 user1061001user1061001 111 bronze badge

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

I was just trying out the wp_get_users() function and I think that's a much cleaner way to get the user data in a table. I can simply do something like this:

$users = get_users('role=um_student');

        foreach ( $users as $user )
        {
            echo "<tr>";
                echo "<td>$user->ID</td>";
                echo "<td>$user->user_login</td>";
                echo "<td>$user->first_name</td>";
                echo "<td>$user->middle_name</td>";
                echo "<td>$user->last_name</td>";
                echo "<td>$user->university_program</td>";
                echo "</tr>";
        }