2

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

5
  • 1
    Why are you not using WP_Query Nov 10 '15 at 18:30
  • Long story short: I'm developing a library, it needs to be a MySQL query Nov 10 '15 at 19:31
  • In my installation, default uncategorized category uses "uncategorised" slug, not "uncategorized". Can you check that? I've tested your code changing the slug value and it works perfectly.
    – cybmeta
    Nov 10 '15 at 20:34
  • I did not hardcode that slug, it is retrieved by getting the slug of the category term with id=1. But i tried anyway, didn't work. Nov 12 '15 at 14:18
  • This worked for me also. Thanks. It was a help.
    – Jake
    Mar 11 at 19:30
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.