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

Here's how I did this dynamically - this procedure builds a SQL statement for every postmeta key for a given post type and then runs the "pivot" query for you:

This isn't the fastest query, and we use it only for migration and deep dives into data, but it does the job.

Note that this temporarily resets the max length of the concat function so you can build a large SQL statement:

CREATE PROCEDURE `wp_posts_pivot`(IN post_type_filter varchar(50))
BEGIN

/* allow longer concat */
declare max_len_original INT default 0;
set max_len_original = @@group_concat_max_len;
set @@group_concat_max_len=100000;

SET @sql = NULL;
SELECT 
    GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(pm.meta_key = ''',
                meta_key,
                ''', pm.meta_value, NULL)) AS `',
                meta_key,
                '`'))
INTO @sql FROM
    wp_posts p
        INNER JOIN
    wp_postmeta AS pm ON p.id = pm.post_id
WHERE
    p.post_type = post_type_filter;

SET @sql = CONCAT('SELECT p.id
                    , p.post_title
                    , ', @sql, ' 
                   FROM wp_posts p
                   LEFT JOIN wp_postmeta AS pm 
                    ON p.id = pm.post_id
                    where p.post_type=\'',post_type_filter,'\'
                   GROUP BY p.id, p.post_title');

/* reset the default concat */
set @@group_concat_max_len= max_len_original;

/*
select @sql;
*/

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


END

You can then call this with a simple call such as this one, which will select a single row for each 'page' post type along with all meta values:

call wp_posts_pivot('page');

Answer 2

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

Answer 4

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