WordPress用户和用户元-将一个表中的多行连接到另一表中的一行 [英] Wordpress users and usermeta - joining multiple rows in one table to one row in another table

问题描述

我想从wp_users和wp_usermeta表创建视图，以便可以从外部应用程序查询视图中的行.基本身份验证详细信息存储在wp_users中(例如，用户名，密码，电子邮件，ID)，其他字段存储在wp_usermeta表中的键/值对中.

wp_users表的结构如下:

id | login | password | email
-----------------------------
1  | bsmith| abc123   | [e-mail]
2  | jjones| def456   | [e-mail]

wp_usermeta表的结构如下:

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

我想将这样的数据作为MYSQL VIEW结束:

id | login | password | email    | firstname | lastname | country
-----------------------------------------------------------------
1  | bsmith| abc123   | [e-mail]| bob       | smith    | denmark
2  | jjones| def456   | [e-mail]| jan       | jones    | germany

我知道我至少需要在wp_users和wp_usermeta之间进行内部联接，但是我还需要在wp_usermeta内部执行子查询以将这些行联接在一起.

我要获取用户行的查询当然很简单:

select u1.id, u1.login, u1.password, u1.email from wp_users u1

我要连续获取元数据的查询是:

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'

那么我如何将这两个查询(u1的数据和u1.id = m1.userid所在的行)连接在一起?

我发现Toote的解决方案是可行的，但它是对MySQL数据库的性能要求，大约需要17秒才能执行"select from users_with_meta_view".

我将其降低为0.0054秒，而视图结构如下:

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

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   | [e-mail]
2  | jjones| def456   | [e-mail]

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   | [e-mail]| bob       | smith    | denmark
2  | jjones| def456   | [e-mail]| 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?

解决方案

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

这篇关于WordPress用户和用户元-将一个表中的多行连接到另一表中的一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！