Manually query posts by taxonomy with MySQL

Question

This worked fine for me. The postmeta inclusion might be a bit messy. Without it, I got about 150 results. With it, I got over 5000. I never go an empty result.

Since postmeta will have anywhere from zero to hundreds of items per post, it is going to make a new row for every postmeta item and keep repeating the post information for each row.

So I added GROUP BY wp.ID, and that broke it:

Query 1 ERROR: Expression #24 of SELECT list is not in GROUP BY clause
and contains nonaggregated column ‘local.wm.meta_id’ which is not
functionally dependent on columns in GROUP BY clause; this is
incompatible with sql_mode=only_full_group_by

That was mainly because the GROUP BY column (wp.ID) needs to be in the SELECT clause. This wouldn’t get your postmeta anyway. So without group by, it worked, you would just need to process it after getting the results and then combine them.

There isn’t a great way to get all the meta in a single query. It is better to specify what you need or use more than one query.

One option is the below, but it has the potential run out of memory on the CONCAT() clauses. I have had issues with posts with a lot of meta. It returns all the meta keys in a column and all the meta value in a column that could just be separated out in a loop later.

SELECT
    wp.ID,
    wp.*,
    GROUP_CONCAT(wm.meta_key ORDER BY wm.meta_key DESC SEPARATOR '||') as meta_keys,
    GROUP_CONCAT(wm.meta_value ORDER BY wm.meta_key DESC SEPARATOR '||') as meta_values
FROM
    wptests_posts wp
    INNER JOIN wptests_postmeta wm ON (wm.`post_id` = wp.`ID`) AND wm.meta_key NOT LIKE '\_%'
    INNER JOIN wptests_term_relationships wtr ON (wp.`ID` = wtr.`object_id`)
    INNER JOIN wptests_term_taxonomy wtt ON (wtr.`term_taxonomy_id` = wtt.`term_taxonomy_id`) AND (wtt.`taxonomy` = 'category')
    INNER JOIN wptests_terms wt ON (wt.`term_id` = wtt.`term_id`) AND  (wt.`slug` = 'uncategorised')
    GROUP BY wp.ID

Something like:

function custom_separate_postmeta( $a ) {
    $output = array_merge( $a, array_combine( explode('||', $a['meta_keys']), array_map('maybe_unserialize', explode('||', $a['meta_values'])) ) );
    unset($output['meta_keys']);
    unset($output['meta_values']);
    return $output;
}

And you can run it on your results:

$posts = array_map( 'custom_separate_postmeta', $posts );

Just some ideas on the issue..

Related Posts: