January 2018 - Freelance Full Stack Developer in London, UK

WordPress SQL snippets

November 4, 2021January 22, 2018 by louie171

A place to keep snippets of useful WordPress SQL. Do a select based on some meta field of a post ( i.e. search for a post that has a particular metafield )

SELECT  p.ID,p.post_title,
        MAX(CASE WHEN pm1.meta_key = 'id_vim_node' then pm1.meta_value ELSE NULL END) as field_i_want
 FROM    wp_posts p LEFT JOIN wp_postmeta pm1 ON ( pm1.post_id = p.ID)

where pm1.meta_key = 'id_vim_node'

GROUP BY
   p.ID, p.post_title

SELECT p.ID,p.post_title,

MAX(CASE WHEN pm1.meta_key = 'id_vim_node' then pm1.meta_value ELSE NULL END) as field_i_want

FROM wp_posts p LEFT JOIN wp_postmeta pm1 ON ( pm1.post_id = p.ID)

where pm1.meta_key = 'id_vim_node'

GROUP BY

p.ID, p.post_title

In the example above I’m searching for posts with a value for id_vim_node. This is based on this code from stackexchange Output WP_Query … Read more