WordPressの記事情報＋サムネイル情報を取得するためのMySQLクエリ

先日、知人のお仕事を手伝いしている際に、WordPressからShopifyに移管する作業を行いました。
デフォルトのエクスポート機能じゃ物足りなかったり、プラグインを入れようにもWordPressが古いバージョンだったのでプラグインを入れられなかったりで記事データのエクスポートに苦戦しました。

「まじかよ...」ってなったのがWordPressのデフォルトのエクスポート機能だと、記事情報しか取得できなくサムネイル情報が入っていないことです。

当然、記事移管なのでサムネ情報も記事と紐づいて取得したかったのでどうしようかなと思ったのですが、SQL書けばいけるのではと思いやってみたところうまくいったので、その際のクエリとクエリの解説について書いていきます。

最終的に作成したクエリ

SELECT 
  p.ID AS id,
  p.post_title AS title,
  p.post_date AS date,
  p.post_content AS content,
  CONCAT('https://example.com/wp-content/uploads/', pm2.meta_value) AS thumbnail_url
FROM wp_posts p
LEFT JOIN wp_postmeta pm1
  ON p.ID = pm1.post_id AND pm1.meta_key = '_thumbnail_id'
LEFT JOIN wp_postmeta pm2
  ON pm1.meta_value = pm2.post_id AND pm2.meta_key = '_wp_attached_file'
WHERE p.post_type = 'post'
  AND p.post_status = 'publish'

どのようにこのクエリを作ったのかを解説していきます。
まずは、今回登場するのは2つのテーブルです。

補足ですが、WordPressにはデータベースは12個あるようです。

（画像は https://codex.wordpress.org/Database_Description より引用）

今回の登場人物のwp_postsとwp_postametaについて詳しく見ていきます。

WordPressのテーブル情報

📄 wp_posts

🗂 wp_postmeta

投稿のメタ情報が入っています。_thumbnail_idがmeta_keyに入っています。
以下のように、投稿のメタ情報には_thumbnail_idが格納されているのですが、
これはアイキャッチ画像のID（別の投稿）を示しているだけです。

そのため、実際の画像のパス（URL）を取得するには、さらにそのIDを使って_wp_attached_fileを参照する必要があります。

SQLを考えてみる

まずは、最終的なアウトプットの形を決めます。
今回は下記のように抽出することにしました。

まずは、wp_postsテーブルから最終的に必要なカラムをselectします。
wp_posts pという書き方をすることでエイリアスをはることができるようです。

SELECT 
  p.ID AS id,
  p.post_title AS title,
  p.post_date AS date,
  p.post_content AS content
FROM wp_posts p

通常の記事投稿以外の投稿情報も出てきてしまっているので微妙なので、条件をつけます。

SELECT 
  p.ID AS id,
  p.post_title AS title,
  p.post_date AS date,
  p.post_content AS content
FROM wp_posts p
WHERE p.post_type = 'post'
  AND p.post_status = 'publish'

これで通常の投稿かつ公開されている記事だけを抽出することができました。
ちなみにカスタム投稿を絞り込むときは下記のようにするとよさそうです。

SELECT 
  p.ID AS id,
  p.post_title AS title,
  p.post_date AS date,
  p.post_content AS content
FROM wp_posts p
WHERE p.post_type = 'work' -- 投稿タイプのスラッグ
  AND p.post_status = 'publish'

現状だと、まだwp_postsテーブルの情報だけなのでここからサムネイル情報をカラムに追加していきます。

まずは、wp_posts.IDを起点に、wp_postmetaテーブルのmeta_key = '_thumbnail_id'で該当するサムネイル画像の投稿ID（= 添付画像のID）を取得します。

ここで使うのが以下の部分です：

LEFT JOIN wp_postmeta pm1
  ON p.ID = pm1.post_id AND pm1.meta_key = '_thumbnail_id'

このクエリでは、wp_postsの投稿ID（p.ID）とwp_postmetaのpost_idを紐付けて、
かつmeta_keyが_thumbnail_idであるデータのみを対象にします。
ここで得られるpm1.meta_valueが、実は「サムネイル画像として登録されている画像の投稿ID」になります。

1回目のLEFT JOINを行うと次のような結果になります。

SELECT 
  p.ID AS id,
  p.post_title AS title,
  pm1.meta_value AS thumbnail_id
FROM wp_posts p
LEFT JOIN wp_postmeta pm1
  ON p.ID = pm1.post_id AND pm1.meta_key = '_thumbnail_id'
WHERE p.post_type = 'post'
  AND p.post_status = 'publish'

(「しまじろうコンサートへ行ってきた」らしいです笑)

しかし、このmeta_valueはまだ画像のファイルパスではありません。
次に必要なのは、この画像投稿IDに紐づく画像ファイルのパス情報を取得することです。

そのため、さらにwp_postmetaをもう一度JOINして、
今度はmeta_key = '_wp_attached_file'の値を取得します。これが画像のパスです。

LEFT JOIN wp_postmeta pm2
  ON pm1.meta_value = pm2.post_id AND pm2.meta_key = '_wp_attached_file'

このようにして、wp_posts → pm1（_thumbnail_id）→ pm2（_wp_attached_file）という2段階のJOINで、
記事に紐づくアイキャッチ画像のURLを取得することができます。

最後に、このパス情報をwp-content/uploads/以下に結合して、完全なURLとして出力しています.

SELECT 
  p.ID AS id,
  p.post_title AS title,
  p.post_date AS date,
  p.post_content AS content,
  CONCAT('https://example.com/wp-content/uploads/', pm2.meta_value) AS thumbnail_url
FROM wp_posts p
LEFT JOIN wp_postmeta pm1
  ON p.ID = pm1.post_id AND pm1.meta_key = '_thumbnail_id'
LEFT JOIN wp_postmeta pm2
  ON pm1.meta_value = pm2.post_id AND pm2.meta_key = '_wp_attached_file'
WHERE p.post_type = 'post'
  AND p.post_status = 'publish'

これで完成です👏👏👏

まとめ

SQLのクエリ自体は簡単にAIが生成してくれるのですが、実際にどのようなことをやっているのかを理解しておくことは重要だと思ったのでまとめてみました。

業務ではあまり書く機会ないですが、ちょっとずつこのあたりの知識も身につけていけたらと思います！