Question

我要从 wp_ usermeta 表格和 wp_usermeta 表格中创建视图, 这样我就可以从外部应用程序中查询视图中的行。基本的 auth 细节存储在 wp_ user( 如用户名、密码、电子邮件、 id) 中, 其它字段则存储在 wp_ usermeta 表格中的密钥/ 价值对。

wp_user 表格的结构如下:

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

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 VEW:

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

我知道我至少需要做一个内部连接 wp_usermeta 和 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 s 数据,和 u1.id = m1.userid 所在的行?

Answer 1

我发现 Toote 的解决方案有效, 但是一个在 MySQL 数据库上的表演猪,

我得到了这个下降至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

Answer 2

据我所知,你做的是对的,你只需要把它们拼凑在一起:

SELECT
    u1.id,
    u1.login,
    u1.password,
    u1.email,
    m1.meta_value AS firstname,
    m2.meta_value AS lastname,
    m3.meta_value AS country
FROM wp_users u1
JOIN wp_usermeta m1 ON (m1.user_id = u1.id AND m1.meta_key =  first_name )
JOIN wp_usermeta m2 ON (m2.user_id = u1.id AND m2.meta_key =  last_name )
JOIN wp_usermeta m3 ON (m3.user_id = u1.id AND m3.meta_key =  country )
WHERE
    -- CONDITIONS ON the user you want to select based any field

Answer 3

$sql= "SELECT
       u1.parent_id,
       m1.meta_value AS headline,
       m2.meta_value AS profilephoto
       FROM wp_vandor u1
       JOIN wp_usermeta m1 ON (m1.user_id = u1.child_id AND m1.meta_key =  headline )
       JOIN wp_usermeta m2 ON (m2.user_id = u1.child_id AND m2.meta_key =  profilephoto )
       WHERE m1.user_id != $currentuser_id";

$classifieds = $wpdb->get_results($sql);

foreach ($classifieds as $classified) { ?>
     <p><?php echo $classified->profilephoto; ?></p>
     <h3><?php echo $classified->headline; ?></h3>
<?php } ?>

友情链接