WordPressで記事一覧を抽出するSQLクエリ

内容

久しぶりの投稿ですね。
今日仕事でWordPressのデータから記事と投稿日付、そしてカテゴリー抽出する必要があり、ちょっといくつか勉強になったのでメモしておく。

SQLクエリ

そこで色々調べたのと、教えてもらったGROUP_CONCATを使って下記のようなクエリが出来上がった。

SELECT wp_posts.id, wp_posts.post_date,wp_posts.post_title,wp_posts.guid, GROUP_CONCAT(wp_terms.name) as category
FROM wp_posts, wp_terms, wp_term_relationships
WHERE wp_posts.post_status = 'publish'
AND wp_posts.ID = wp_term_relationships.object_id
AND wp_term_relationships.term_taxonomy_id = wp_terms.term_id
GROUP BY wp_posts.ID

カテゴリーがなければ、こんなクエリは必要ないのだけど、カテゴリはterm_taxonomyというカスタム分類と呼ばれるもの?らしい。
詳細は下記公式のWikiを見るとして、複数テーブルのIDで連結させる必要があるのが注意点。

関数リファレンス/get terms

その後、GROUP_CONCATとGROUP BYを使って、カテゴリーごとに記事がレコードとして表示されるのをまとめている。
これは会社の人に教えてもらったのだけど、とても便利だった。
区切り文字も指定できるようなので、カンマ以外もいけるようだ。
post_contentカラムのデータを含めば、記事の内容ももちろん出力可能。

CSVで出力するのにあたって

最初mysqldumpでCSVエクスポートやろうと思った。
普通にやると、下記のようなコマンドが使える。

mysqldump -u root -p -h HOSTNAME --single-transaction --tab=/tmp/ --fields-terminated-by=, wordpress wp_posts

–tabと–fields-terminated-byはセットで使う必要がある。
これを使うと–tabで指定したディレクトリに、–fields-terminated-byで指定した区切り文字で区切ることができる。

しかし、RDSだと SELECT INTO OUTFILEという権限が付与されていないのか、rootでもAccess deniedとなる。
sedとか使ってやる方法もあるが、複数のデータを抽出する必要があり、都度シェルでやるのも面倒だったのでSequel Proを使って抽出することにした。
なお、Sequel Proでそのまま抽出するとUTF-8で、Execl for Macでは日本語が全て文字化けする。
CSVでエクスポートするなら、SequelProの環境設定>デフォルトエンコーディングをsjisに変更すれば、日本語も問題なくExecl for Macで表示できるようになる。

ちょっとしたことだけど、覚えておけばまた使えそうなことを学べたのだった。