WordPressのタクソノミ一覧を取得したりタクソノミをもとにpost_typeを変更するSQL

タクソノミ(カテゴリーやタグ、カスタムタクソノミ)の一覧を取得する

SELECT wp_terms.term_id,wp_terms.name,wp_terms.slug,wp_term_taxonomy.term_taxonomy_id,wp_term_taxonomy.taxonomy FROM wp_terms JOIN wp_term_taxonomy ON wp_terms.term_id =
wp_term_taxonomy.term_id;

タクソノミのnameがhogeであるpost_idの一覧を取得する

SELECT wp_terms.term_id,wp_terms.name,wp_terms.slug,wp_term_taxonomy.term_taxonomy_id,wp_term_taxonomy.taxonomy, wp_term_relationships.object_id AS post_id FROM wp_terms JOIN wp_term_taxonomy ON wp_terms.term_id = wp_term_taxonomy.term_id LEFT OUTER JOIN wp_term_relationships ON wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id WHERE wp_terms.name = 'hoge'; 

タクソノミのnameがhogeである記事のpost_idとpost_typeを一覧で取得する

SELECT wp_terms.term_id,wp_terms.name,wp_terms.slug,wp_term_taxonomy.term_taxonomy_id,wp_term_taxonomy.taxonomy, wp_term_relationships.object_id AS post_id, wp_posts.post_type FROM wp_terms JOIN wp_term_taxonomy ON wp_terms.term_id =
wp_term_taxonomy.term_id LEFT OUTER JOIN wp_term_relationships ON wp_term_taxonomy.term_taxonomy_id =
wp_term_relationships.term_taxonomy_id JOIN wp_posts ON
wp_term_relationships.object_id = wp_posts.ID WHERE wp_terms.name = 'hoge';

タクソノミのnameがhoge、かつpost_typeがpostである記事のpost_idとpost_typeを一覧で取得する

SELECT wp_terms.term_id,wp_terms.name,wp_terms.slug,wp_term_taxonomy.term_taxonomy_id,wp_term_taxonomy.taxonomy, wp_term_relationships.object_id AS post_id, wp_posts.post_type FROM wp_terms JOIN wp_term_taxonomy ON wp_terms.term_id =
wp_term_taxonomy.term_id LEFT OUTER JOIN wp_term_relationships ON wp_term_taxonomy.term_taxonomy_id =
wp_term_relationships.term_taxonomy_id JOIN wp_posts ON
wp_term_relationships.object_id = wp_posts.ID WHERE wp_terms.name = 'hoge' AND wp_posts.post_type = 'post';

UPDATEの確認1(じっくり確認)

SELECT
wp_terms.term_id
,wp_terms.name
,wp_terms.slug
,wp_term_taxonomy.term_taxonomy_id
,wp_term_taxonomy.taxonomy
,wp_term_relationships.object_id AS post_id
,wp_posts.post_type
,'page' AS new_post_type
FROM wp_terms
JOIN wp_term_taxonomy ON wp_terms.term_id = wp_term_taxonomy.term_id LEFT OUTER JOIN wp_term_relationships ON
wp_term_taxonomy.term_taxonomy_id =
wp_term_relationships.term_taxonomy_id
JOIN wp_posts ON wp_term_relationships.object_id = wp_posts.ID WHERE
wp_terms.name = 'hoge'
AND wp_posts.post_type = 'post'
;

UPDATEの確認2(UPDATE用確認)

SELECT
wp_term_relationships.object_id AS post_id
,wp_posts.post_type
,'page' AS new_post_type
FROM wp_terms
JOIN wp_term_taxonomy ON wp_terms.term_id = wp_term_taxonomy.term_id LEFT OUTER JOIN wp_term_relationships ON
wp_term_taxonomy.term_taxonomy_id =
wp_term_relationships.term_taxonomy_id
JOIN wp_posts ON wp_term_relationships.object_id = wp_posts.ID WHERE
wp_terms.name = 'hoge'
AND wp_posts.post_type = 'post'
;

タクソノミのnameがhogeであるpost_typeがpostの記事のpost_typeをpageに変更する

 UPDATE wp_posts SET
wp_posts.post_type = 'page'
WHERE ID IN(
SELECT
wp_term_relationships.object_id AS post_id
FROM wp_terms
JOIN wp_term_taxonomy ON wp_terms.term_id =
wp_term_taxonomy.term_id
LEFT OUTER JOIN wp_term_relationships ON
wp_term_taxonomy.term_taxonomy_id =
wp_term_relationships.term_taxonomy_id