SELECT custom post type and its meta in SQL

admin管理员组

文章数量:1290287

So I have the following SQL script where I can view all my post_meta with specific meta_keys:

SELECT * FROM `wp_postmeta` 
WHERE `meta_key` = 'honorific' 
OR `meta_key` = 'suffix'
OR `meta_key` = 'nickname' 

Is there anyway that I can make it where instead of giving me ALL the post_meta with those keys, it gives me just the post_metas that match a specific post type such as 'test'?

I tried:

SELECT * p.post_type FROM wp_posts p ` INNER JOIN `wp_postmeta`

----- Also:

SELECT p.ID, 
       p.post_title
FROM  wp_posts p 
      INNER JOIN wp_postmeta AS pm ON pm.post_id  = p.ID
WHERE
      pm.meta_key = 'honorific' AND p.post_type = 'employee'

That's where I'm kinda of stuck at - All help will be appreciated.

Basically:

• Give me post_meta that equals honorific, suffix and nickname with posts related to custom post type test.

So I have the following SQL script where I can view all my post_meta with specific meta_keys:

SELECT * FROM `wp_postmeta` 
WHERE `meta_key` = 'honorific' 
OR `meta_key` = 'suffix'
OR `meta_key` = 'nickname' 

Is there anyway that I can make it where instead of giving me ALL the post_meta with those keys, it gives me just the post_metas that match a specific post type such as 'test'?

I tried:

SELECT * p.post_type FROM wp_posts p ` INNER JOIN `wp_postmeta`

----- Also:

SELECT p.ID, 
       p.post_title
FROM  wp_posts p 
      INNER JOIN wp_postmeta AS pm ON pm.post_id  = p.ID
WHERE
      pm.meta_key = 'honorific' AND p.post_type = 'employee'

That's where I'm kinda of stuck at - All help will be appreciated.

Basically:

• Give me post_meta that equals honorific, suffix and nickname with posts related to custom post type test.

• custom-post-types
• posts
• post-meta
• sql

Share Improve this question asked Jun 17, 2021 at 16:29 Sem313Sem313 1311 silver badge33 bronze badges 1

• 1 Where and how are you running the query? The easiest method is to first query the posts with the desired post type for their ID, then use those IDs as part of the query, but there's a very very high chance that you don't need to do this at all and that there are easier ways to do it ( and pitfalls you aren't aware of ). E.g. if your goal is to display all employees by name on a page, there are trivial alternatives that may even be faster depending on how your server is setup. Likewise WP CLI can be used for similar things and generate output in other formats such as CSV – Tom J Nowell ♦ Commented Jun 17, 2021 at 16:55

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

Tom's comment on the question makes a very important point: unless you are just using this SQL for diagnostic or exploratory purposes, there is almost definitively an easier and more efficient means to access this data, either through the WordPress APIs or via tools like WP CLI.

There is virtually no reason to use this SQL for logic within a WordPress plugin, theme, or site.

The way to think of SELECT... JOINs is that you are SELECTing from the "virtual table" produced by the JOIN.

We can SELECT all of the rows from the wp_postmeta table which have one of the meta_keys "honorific", "suffix", or "nickname" and a post_id field corresponding to the ID field in wp_posts rows with the post_type "employee" as such:

SELECT wp_postmeta.*
  FROM wp_posts INNER JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
  WHERE wp_posts.post_type="employee"
    AND wp_postmeta.meta_key IN ("honorific", "suffix", "nickname");

Or more succinctly with table aliases,

SELECT m.*
  FROM wp_posts AS p INNER JOIN wp_postmeta AS m ON p.ID = m.post_id
  WHERE p.post_type="employee"
    AND m.meta_key IN ("honorific", "suffix", "nickname");

本文标签： SELECT custom post type and its meta in SQL