Wordpress users and usermeta - joining multiple rows in one table to one row in another table

join, mysql, sql-view, wordpress

Solution

I found that Toote's solution works but is a performance hog on a MySQL database taking about 17 seconds to execute a "select * from users_with_meta_view".

I got this down to 0.0054 sec with a view structured like this instead:

CREATE OR REPLACE VIEW users_with_meta_view AS
SELECT
    u.id,
    u.user_login AS login,
    u.user_pass AS password,
    u.user_email AS email,
    (select meta_value from wp_usermeta where user_id = u.id and meta_key = 'first_name' limit 1) as first_name,
    (select meta_value from wp_usermeta where user_id = u.id and meta_key = 'last_name' limit 1) as last_name,
    (select meta_value from wp_usermeta where user_id = u.id and meta_key = 'country' limit 1) as country
FROM wp_users u

Problem

I want to create a view from both the wp_users and wp_usermeta tables so that I can query rows in the view from an external application. Basic auth details are stored in wp_users (e.g. username, password, email, id) and other fields are stored in key/value pairs in the wp_usermeta table. the wp_users table is structured like this: ``` id | login | password | email ----------------------------- 1 | bsmith| abc123 | b@foo.com 2 | jjones| def456 | k@bah.com ``` the wp_usermeta table is structured like this: ``` id | user_id | meta_key | meta_value ------------------------------------ 1 | 1 | firstname| bob 2 | 1 | lastname | smith 3 | 1 | country | denmark 4 | 2 | firstname| jan 5 | 2 | lastname | jones 6 | 2 | country | germany ``` I want to end up with data like this as a MYSQL VIEW: ``` id | login | password | email | firstname | lastname | country ----------------------------------------------------------------- 1 | bsmith| abc123 | b@foo.com| bob | smith | denmark 2 | jjones| def456 | k@bah.com| jan | jones | germany ``` I know I need to do at least an inner join between wp_users and wp_usermeta, however I also need to perform a sub query inside wp_usermeta to join those rows together. The query I have for getting the user row is of course dead easy: ``` select u1.id, u1.login, u1.password, u1.email from wp_users u1 ``` The query I have for getting the meta as a row this is: ``` select m1.user_id, m1.meta_value as firstname, m2.meta_value as lastname, m3.meta_value as country from wp_usermeta m1 join wp_usermeta m2 on (m1.user_id = m2.user_id and m2.meta_key = 'last_name') join wp_usermeta m3 on (m2.user_id = m3.user_id and m3.meta_key = 'country') where m1.meta_key = 'first_name' ``` So how do I join these two queries together - u1's data and the row where u1.id = m1.userid?

Original source