Manually query posts by taxonomy with MySQL

Question

I'm trying to query posts from the WP database with the category uncategorised, while also joining the post meta table, but I can't get it to work. I get an empty result.

This is my query

SELECT * FROM wptests_posts wp
INNER JOIN wptests_postmeta wm ON (wm.`post_id` = wp.`ID`)
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`)
INNER JOIN wptests_terms wt ON (wt.`term_id` = wtt.`term_id`) AND
((wtt.`taxonomy` = 'category' AND  wt.`slug` = 'uncategorized'))

It seems to be the postmeta inner join on line 2. If i remove it, I get the expected results, except I don't then get the post meta.

Any help is appreciated

Answer 1

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