Wordpress : Sql select posts with all related meta

Question

I need to get an array of posts with all related meta. So it should be looks like that:

post
  postfields from post table : [
                               'title' =>...
                               'content'=>...
                               etc....
                               ]
  array of values from meta : [
                               [],
                               []
                              ],
 post
   postfields from post table : [
                               'title' =>...
                               'content'=>...
                               etc....
                               ]
   array of values from meta : [
                               [],
                               []
                              ]

What I'm trying to do:

SELECT
    *
FROM wp_posts
    INNER JOIN wp_postmeta
    ON wp_posts.ID = wp_postmeta.post_id
    WHERE wp_posts.post_type = 'page'
    GROUP BY wp_posts.ID

But it fails.

#1055 - Expression #24 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'somedbname.wp_postmeta.meta_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

How can I get all posts with all related meta in one query?

Answer 1

There are a couple of things to be aware of with these two tables.

1. Posts and Post Meta have a one to many relationship. So by using GROUP BY wp_posts.ID you'll limit your results to a single post with the first relatable post meta record (and any other related post meta won't show up).
2. By using INNER JOIN wp_postmeta the query will not return any posts that do not have post meta.

One design pattern you can use is to perform the query without GROUP BY wp_posts.ID (which will result in multiple rows for a single post because each row will contain the same post id but different post meta) and restructure your array in PHP. This sort of loop in PHP is fairly performant compared to doing multiple queries. And you can also cache your results in a transient if needed.

To clarify with an example, you should be able to perform your query like so:

global $wpdb;
$results = $wpdb->get_results( "SELECT * FROM wp_posts LEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id WHERE wp_posts.post_type = 'page' ORDER BY wp_posts.ID" );

if ( ! empty( $results ) ) {
    $previous_id = 0;
    $structured_results = array();
    foreach ( $results as $post ) {
        if ( $previous_id !== $post->ID ) {
            $structured_results[ $post->ID ] = array(
                'ID' => $post->ID,
                'post_author' => $post->post_author,
                'post_date' => $post->post_date,
                'post_title' => $post->post_title,
                // etc for all other columns in wp_posts
                'post_meta' => array(
                    $post->meta_id => array(
                        'meta_key' => $post->meta_key,
                        'meta_value' => $post->meta_value,
                    ),
                ),
            );
        } else {
            $structured_results[ $post->ID ]['post_meta'][ $post->meta_id ] = array(
                'meta_key' => $post->meta_key,
                'meta_value' => $post->meta_value,
            );

        }
        $previous_id = $post->ID;
    }
}

This will give you a single array of results named structured_results. Each element of the array will be a single post (with it's key being the post id). Each element will contain all of the columns from wp_posts. In addition, each element will have a key named post_meta which is an array of all post meta records.

A couple of notes:

• This is a LEFT JOIN so it's going to include posts that have no post meta (use INNER JOIN if you only want posts with meta).
• I didn't fill out all of the columns in the code above from wp_posts, so you can add additional columns (such as post_content, etc).