php - 将表相互连接以及相互连接

Question

我花了一天的大部分时间试图解决这个问题，所以我想是时候询问专家了:我正在尝试在 WordPress 之外访问我的 WordPress 数据库，因此我正在编写查询以从有些复杂的 WordPress 数据库中获取输出。
数据库中有多个表使用meta_values和meta_keys在多行中存储有关帖子或用户的数据。
例如，我需要从中提取四个表:

表:wp_postmeta

post_id  |    meta_key     |  meta_value
---------+-----------------+------------
2001     | _customer_user  | 3
2001     |order_description| smith
2001     | _order_total    | 300

表:wp_posts

ID  | post_type | post_status
----+-----------+------------
2001| shop_order| publish

表:wp_term_relationships

object_id | term_taxonomy_id
----------+-----------------
 2001     |  190

表:wp_usermeta

user_id  | meta_key        |meta_value
---------+-----------------+----------
  3      | first_name      | Andy
  3      | last_name       | Mccormick

此查询可以很好地在一行中显示订单号 (post_id) 客户 ID、订单说明和订单总额:

 SELECT wp_posts.ID, wp_posts.post_title,ot.meta_value as total,od.meta_value as order_desc, cu.meta_value as customer                
        FROM wp_posts
        LEFT JOIN wp_postmeta AS ot ON (wp_posts.ID = ot.post_id AND ot.meta_key='_order_total')
        LEFT JOIN wp_postmeta AS od ON (wp_posts.ID = od.post_id  AND od.meta_key='order_description')
        LEFT JOIN wp_postmeta AS cu ON (wp_posts.ID =cu.post_id  AND cu.meta_key='_customer_user')
        LEFT JOIN wp_term_relationships ON wp_posts.ID = wp_term_relationships.object_id
        WHERE wp_posts.post_type = 'shop_order'
        AND wp_posts.post_status = 'publish'
        AND wp_term_relationships.term_taxonomy_id = '190'
        GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC"

但是，在另一个区域，我想按姓名列出客户，而不仅仅是按 ID，因此我尝试从 wp_usermeta 表中获取他们的名字和姓氏，如下所示:

SELECT wp_posts.ID, cu.meta_value as customer, fn.meta_vale as fname, ln.meta_value as lname                
                FROM wp_posts
                LEFT JOIN wp_postmeta AS cu ON (wp_posts.ID = cu.post_id  AND cu.meta_key='_customer_user')
                LEFT JOIN wp_usermeta AS fn ON (cu.meta_key = fn.user_id AND fn.meta_key='first_name')
                LEFT JOIN wp_usermeta AS ln ON (cu.meta_key = ln.user_id AND ln.meta_key='last_name')
                LEFT JOIN wp_term_relationships ON wp_posts.ID = wp_term_relationships.object_id
                WHERE wp_posts.post_type = 'shop_order'
                AND wp_posts.post_status = 'publish'
                AND wp_term_relationships.term_taxonomy_id = '190'
                GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC;

我也尝试过: LEFT JOIN wp_usermeta AS fn ON ((cu.meta_key='_customer_user') = fn.user_id AND fn.meta_key='first_name') 以及许多其他子查询等。

显然，我现在在 SQL 查询方面的薪资水平已经超出了我的水平，所以任何帮助都会很棒。谢谢!

Answer 1

我不确定，但你可以尝试一下:

SELECT wp_posts.ID, cu.meta_value as customer, fn.meta_vale as fname, ln.meta_value as lname                
                FROM wp_posts
                LEFT JOIN wp_postmeta AS cu ON (wp_posts.ID = cu.post_id  AND cu.meta_key='_customer_user')
                LEFT JOIN wp_usermeta AS fn ON (cu.meta_value = fn.user_id AND fn.meta_key='first_name') // Note meta_value instead of meta_key
                LEFT JOIN wp_usermeta AS ln ON (cu.meta_value = ln.user_id AND ln.meta_key='last_name') // Note meta_value instead of meta_key
                LEFT JOIN wp_term_relationships ON wp_posts.ID = wp_term_relationships.object_id
                WHERE wp_posts.post_type = 'shop_order'
                AND wp_posts.post_status = 'publish'
                AND wp_term_relationships.term_taxonomy_id = '190'
                GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC;

据我所知，在您的示例中，cu.meta_value 为 3，因为它是用户的 ID，而 cu.meta_key (如您的查询中所示)是'_customer_id'，因此您宁愿匹配 cu.meta_value 而不是 cu.meta_key。但无法检查，因为我没有安装 WP。