0

In a RDMS with the following query I try to get postmeta.meta_value of the post with custom post type points. To identify which key they belong to I made a case when. This query returns a greater number of records than the number of custom post types in most cases full of null. What I expect is a result that contains the same number of custom post type points. Could you please tell me the correct way to do this?

SELECT 
    case when wp_postmeta.meta_key = 'zone' then wp_postmeta.meta_value end 'zone',
    case when wp_postmeta.meta_key = 'category' then wp_postmeta.meta_value end 'category',
    case when wp_postmeta.meta_key = 'country' then wp_postmeta.meta_value end 'country'
FROM
    wp_posts
        INNER JOIN
    wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
WHERE
    wp_posts.post_type = 'points'

I hope a result that can look like this

+----------------------------------+---------+------------------------+
|               zone               |  cate   |          country       |
+----------------------------------+---------+------------------------+
| Zone 1                           | Cat 1   | Country 1              |
| Zone 2                           | Cat 2   | Country 2              |
| Zone 1                           | Cat 3   | Country 3              |
+----------------------------------+---------+------------------------+

I do not know if this is relevante but this postmeta key/values area created by custom fields created by Advanced custom fields plugin

  • OK... and what exactly are you trying to achieve? – Krzysiek Dróżdż Dec 5 '18 at 15:57
  • I updated my question with an example of an expected result – user615274 Dec 5 '18 at 16:13
  • I'm afraid you'll have to use 3 joins to achieve this... – Krzysiek Dróżdż Dec 5 '18 at 16:14
  • Could you comment what tables are required? If you have any examples, I would appreciate it – user615274 Dec 5 '18 at 16:25
  • post meta is a key/value pair attached to a post ID, I don't know how ACF stores this collection of 3 items though and you'll need to ask ACF developers in an ACF venue/forum or ACF dev support/docs. Otherwise you can fetch all post meta all at once via get_post_meta( $post_id ). I would advise against using raw SQL to query that table though, and that querying for posts via their post meta is super slow and not what that table was designed for ( it's designed for finding key/value pairs when you already know the post ID, taxonomies and terms are for filtering/searching ) – Tom J Nowell Dec 5 '18 at 16:44

Your Answer

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

Browse other questions tagged or ask your own question.