选择行作为wordpress post meta的列 [英] Select rows as columns for wordpress post meta

问题描述

WordPress的wp_postmeta表具有帖子的所有其他字段，但它们都位于行中，因此很容易添加更多字段.

WordPress's wp_postmeta table has all the additional fields for a post but they are in rows so it's easy to add more.

但是，现在我要查询所有帖子的所有字段，可以这么说，我显然希望这些字段在列而不是行中.

However, now I want to query for all the fields of all the posts lets say, I obviously want those fields in a column and not a row.

这是我正在运行的查询

SELECT p.post_title, 
       m.meta_value, 
       m.meta_key 
FROM   wp_posts p 
       JOIN wp_postmeta m 
         ON p.id = m.post_id 
WHERE  p.id = 72697; 

这将为我提供所有meta_values及其各自的meta键作为列.但是我需要将元键值作为列，将元值作为行

This will give me all the meta_values and their respective meta keys as columns. But I need the meta keys values as columns and meta values as rows

例如，meta_key可以是additional_description，其值可以是What's up

For example a meta_key could be additional_description and it's value could be What's up

所以我需要这样的东西

SELECT p.post_title, additional_description
FROM   wp_posts p 
       JOIN wp_postmeta m 
         ON p.id = m.post_id 
WHERE  p.id = 72697; 

我需要它作为一栏.我还需要所有帖子，而不是特定的帖子，但是无论何时删除where，它都不会查询(我有很多帖子，这可能是个问题).

I need it as a column. I also need all of the posts and not a specific one, but whenever I remove the where it just doesn't query (I have lots of posts, that could be an issue).

以下是一些示例数据，以及我希望如何显示结果 wp_postmeta表

Here is some sample data and how I want the results to show up wp_postmeta table

meta_key    post_id     meta_key    meta_value
1       5       total_related   5
2       5       updated     0
3       5       cricket     1
4       8       total_related   8
5       8       updated     1
6       8       cricket     0



wp_post table

id  post_title      other things I dont care about
5   This is awesome
8   This is more awesome

wp_post id与wp_postmeta表上的post_id相关

wp_post id is related to post_id on wp_postmeta table

想要结果

post_title      total_related   updated     cricket
This is awesome     5       0       1
This is more awesome    8       1       0   

推荐答案

这样的事情怎么样?

SELECT p.post_title, m1.meta_value as 'total_related', m2.meta_value as 'updated', m3.meta_value as 'cricket'
FROM wp_posts p
LEFT JOIN wp_postmeta m1
    ON p.id = m1.post_id AND m1.meta_key = 'total_related'
LEFT JOIN wp_postmeta m2
    ON p.id = m2.post_id AND m2.meta_key = 'updated'
LEFT JOIN wp_postmeta m3
    ON p.id = m3.post_id AND m3.meta_key = 'cricket'

而且，由于您不需要查找特定的帖子，因此您应该能够做到这一点.

And since you aren't looking for a specific post you should be able to do this.

如果要查询特定的post_types，可以尝试类似的操作

If you want to query specific post_types you can try something like this

SELECT p.post_title, m1.meta_value as 'total_related', m2.meta_value as 'updated', m3.meta_value as 'cricket'
FROM wp_posts p
LEFT JOIN wp_postmeta m1
    ON p.id = m1.post_id AND m1.meta_key = 'total_related'
LEFT JOIN wp_postmeta m2
    ON p.id = m2.post_id AND m2.meta_key = 'updated'
LEFT JOIN wp_postmeta m3
    ON p.id = m3.post_id AND m3.meta_key = 'cricket'
WHERE p.post_type = 'my_custom_post_type';

这篇关于选择行作为wordpress post meta的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！