Select rows as columns for wordpress post meta

Question

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.

This is my query that I am running

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; 

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

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

So I need something like this

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

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).

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 is related to post_id on wp_postmeta table

Result wanted

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

Answer 1

What about 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'

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

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';

Answer 2

Try that:

select post_title , 
 MAX(CASE WHEN `meta_key`='total_related' THEN meta_value END)as 'total_related',
 MAX(CASE WHEN `meta_key` = 'updated' THEN meta_value END) as 'updated' ,
 MAX(CASE WHEN `meta_key` = 'cricket' THEN meta_value END) as 'cricket' 
FROM   wp_posts p 
JOIN wp_postmeta m ON p.id = m.post_id 
GROUP BY p.id

Answer 3

There are several approaches.

Here's an example of one way to get the specified result, using correlated subqueries in the SELECT list:

SELECT p.post_title
     , ( SELECT m1.meta_value
           FROM wp_post_metadata m1
          WHERE m1.meta_key = 'total_related'
            AND m1.post_id = p.id
          ORDER BY m1.meta_key LIMIT 1
       ) AS `total_related`
     , ( SELECT m2.meta_value
           FROM wp_post_metadata m2
          WHERE m2.meta_key = 'updated'
            AND m2.post_id = p.id
          ORDER BY m2.meta_key LIMIT 1
       ) AS `updated`
     , ( SELECT m3.meta_value
           FROM wp_post_metadata m3
          WHERE m3.meta_key = 'cricket'
            AND m3.post_id = p.id
          ORDER BY m3.meta_key LIMIT 1
       ) AS `cricket`
  FROM wp_posts p
 WHERE p.id IN (5,8)

There are several other approaches, each with its own advantages and drawbacks.

There's a somewhat related question I referenced in a comment on the question. That question illustrates several approaches, but omits a correlated subquery approach.)