WordPressのデータベースから欲しい投稿情報を抽出した時のSQLのメモです。
WordPressを複雑には使っていないので、私の場合はこれでうまく抽出できました。もし、コピペして使う場合は重複行が出たら手直しください。
※あくまで自分用のメモです。
欲しい項目（というか見たい一覧情報）
- タイトル
- スラッグ
- 投稿日時
- 更新日時
- カテゴリー
- タグ
- タイトルの文字数
- 記事の文字数※
※記事の文字数は、HTMLタグの文字数も含む数になってしまうので参考程度の情報
抽出条件
- 投稿ステータスは、公開済み、下書き、予約投稿
- 固定ページは除外
並び順
- 投稿日時の古い順
SQL
SELECT a.ID
, a.post_title AS タイトル
, a.post_name AS スラッグ
, a.post_date AS 投稿日時
, a.post_modified AS 更新日時
, GROUP_CONCAT(b.name SEPARATOR ‘, ‘) AS カテゴリー
, GROUP_CONCAT(c.name SEPARATOR ‘, ‘) AS タグ
, CHAR_LENGTH(a.post_title) AS タイトルの文字数
, CHAR_LENGTH(a.post_content) AS 記事の文字数
FROM wp_posts a
LEFT JOIN (
SELECT sub_a.name
, sub_a.slug
, sub_c.object_id
FROM wp_terms sub_a
LEFT JOIN wp_term_taxonomy sub_b
ON sub_a.term_id = sub_b.term_id
LEFT JOIN wp_term_relationships sub_c
ON sub_b.term_taxonomy_id = sub_c.term_taxonomy_id
WHERE sub_b.taxonomy = ‘category’
) b
ON a.ID = b.object_id
LEFT JOIN (
SELECT sub_a.name
, sub_a.slug
, sub_c.object_id
FROM wp_terms sub_a
LEFT JOIN wp_term_taxonomy sub_b
ON sub_a.term_id = sub_b.term_id
LEFT JOIN wp_term_relationships sub_c
ON sub_b.term_taxonomy_id = sub_c.term_taxonomy_id
WHERE sub_b.taxonomy = ‘post_tag’
) c
ON a.ID = c.object_id
WHERE a.post_status IN (‘publish’, ‘future’, ‘draft’)
AND a.post_type = ‘post’
GROUP BY a.ID
ORDER BY a.post_date ASC;
アイキャッチ画像の情報も欲しい時（本当に参考レベル）
※注意 アイキャッチ画像を変更している場合、カテゴリー名とタグ名の項目が重複して取れてしまうようです。（GROUP BY すれば解消されるかも。試してないけど）
SELECT a.ID
, a.post_title AS タイトル
, a.post_name AS スラッグ
, a.post_date AS 投稿日時
, a.post_modified AS 更新日時
, GROUP_CONCAT(b.name SEPARATOR ‘, ‘) AS カテゴリー
, GROUP_CONCAT(c.name SEPARATOR ‘, ‘) AS タグ
, CHAR_LENGTH(a.post_title) AS タイトルの文字数
, CHAR_LENGTH(a.post_content) AS 記事の文字数
, d.post_title AS アイキャッチ画像のファイル名
, d.guid AS アイキャッチ画像の保存先パス名
FROM wp_posts a
LEFT JOIN (
SELECT sub_a.name
, sub_a.slug
, sub_c.object_id
FROM wp_terms sub_a
LEFT JOIN wp_term_taxonomy sub_b
ON sub_a.term_id = sub_b.term_id
LEFT JOIN wp_term_relationships sub_c
ON sub_b.term_taxonomy_id = sub_c.term_taxonomy_id
WHERE sub_b.taxonomy = ‘category’
) b
ON a.ID = b.object_id
LEFT JOIN (
SELECT sub_a.name
, sub_a.slug
, sub_c.object_id
FROM wp_terms sub_a
LEFT JOIN wp_term_taxonomy sub_b
ON sub_a.term_id = sub_b.term_id
LEFT JOIN wp_term_relationships sub_c
ON sub_b.term_taxonomy_id = sub_c.term_taxonomy_id
WHERE sub_b.taxonomy = ‘post_tag’
) c
ON a.ID = c.object_id
LEFT JOIN (
SELECT sub_a.post_title
, sub_a.guid
, sub_a.post_parent
FROM wp_posts sub_a
WHERE sub_a.post_type = ‘attachment’
) d
ON a.ID = d.post_parent
WHERE a.post_status IN (‘publish’, ‘future’, ‘draft’)
AND a.post_type = ‘post’
GROUP BY a.ID
ORDER BY a.post_date ASC;
あとがき
長めのSELECT文を書いたのは数年ぶりだったので、もっと上手い書き方はあるかもしれません。（重複して取れちゃったらごめんなさい）
