WordPressでシステム開発をする時に必要なクエリ操作について

WordPressでガッツリしたシステム開発をする時によく使うクエリ操作について纏めてみた。
WordPress楽しいので他のフレームワークを使わないよ、でもゴリゴリSQL書かなきゃねっていう案件の時に。

前置き

使う前には

global $wpdb;

を書いてやる

PHPStormとかPHPDocが使える環境であれば

global $wpdb; /** @var wpdb $wpdb */

と書いておくとメソッドが補完できる。

テーブル一覧

テーブル一覧については把握が必要。
特にwp_postsとwp_postmeta、wp_optionsはよく使う。
あまり使わないけど混乱しがちなのがwp_terms, wp_term_taxonomies,wp_term_relationsps。

テーブル名	備考
wp_commentmeta	コメントメタ。あまり使われない。
wp_comments	コメント。コメントの本文やスパム判定などが入ってる。
wp_links	リンク。もう(ほとんど)使われてない。
wp_options	オプション。サイト本体のオプションはもちろん、プラグインの設定やウィジェットなども入っている。
wp_postmeta	ポストメタ。カスタムフィールドの内容が入っている。
wp_posts	ポスツ。記事自体が入っている。post_statusやpost_typeの取り扱いに注意。
wp_terms	タームス。タグやカテゴリの表示名が入っている。
wp_term_relationships	タームリレーションシップス。記事とタームの関連付けが入っている。
wp_term_taxonomy	タームタクソノミー。 タームとタクソノミーのリレーションテーブル。
wp_usermeta	ユーザーメタ。なかなか活躍しない。
wp_users	ユーザーズ。ユーザとかはここ。スーパーパスワードリセットもここ。

http://wpdocs.osdn.jp/%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9%E6%A6%82%E8%A6%81
https://codex.wordpress.org/Database_Description

テーブル名

$wpdb->postsのように
テーブルプリフィックス+固有テーブル名を
$wpdb->テーブル名
と書いてやる。

テーブル名	PHP表記
wp_commentmeta	$wpdb->commentmeta
wp_comments	$wpdb->comments
wp_links	$wpdb->links
wp_options	$wpdb->options
wp_postmeta	$wpdb->postmeta
wp_posts	$wpdb->posts
wp_terms	$wpdb->terms
wp_term_relationships	$wpdb->term_relationships
wp_term_taxonomy	$wpdb->term_taxonomy
wp_usermeta	$wpdb->usermeta
wp_users	$wpdb->users

SELECT

よくある複数個のSELECT

$query = "SELECT * FROM $wpdb->posts ORDER BY ID LIMIT 20;";
$rows = $wpdb->get_results($query);
foreach($rows as $row) {
   $id = $row->ID; // オブジェクトで返ってくる
}

$rows = $wpdb->get_results($query, OBJECT);と同義。

これとprepareを組み合わせたのが一番よく使う基本形。

連想配列で

global $wpdb;
$query = "SELECT * FROM $wpdb->posts ORDER BY ID LIMIT 20;";
$rows = $wpdb->get_results($query, ARRAY_A); // ココに指定
foreach($rows as $row) {
   $id = $row['ID']; // 連想配列で返る
}

数字添え字で

$query = "SELECT ID, post_title FROM $wpdb->posts ORDER BY ID LIMIT 20;";
$rows = $wpdb->get_results($query, ARRAY_N); // ココに指定
foreach($rows as $row) {
   $id = $row[0]; // 0: ID
   $id = $row[1]; // 1: post_title
}

値一つだけ

$query = "SELECT count(*) FROM $wpdb->posts ORDER BY ID LIMIT 20;";
$count = $wpdb->get_var($query);

値一つだけならget_var()でも可。

Prepare -> Excecute

クエリを使い回すという感じではなくて単なるエスケープ(適当)。

標準的な

$query = "SELECT count(*) FROM $wpdb->posts WHERE post_type = %s ORDER BY ID LIMIT 20;";
$prepared = $wpdb->prepare($query, 'product');
$count = $wpdb->get_var($prepared);

$count = $wpdb->get_var($wpdb->prepare($query, 'product'));と一行で書いてある事が多い。

値を取得しない場合

$query = "INSERT INTO $wpdb->posts (post_content) VALUES (%s);";
$wpdb->query( $wpdb->prepare($query, 'yaaaHoooo!') );

sqlのワイルドカード

タイトルに'今日の'が含まれる物を取得

$query = "SELECT * FROM $wpdb->posts 
  WHERE post_title LIKE %s
  ORDER BY ID LIMIT 20;";
$rows = $wpdb->get_results($wpdb->prepare($query, '%今日の%'));
foreach($rows as $row) {
   $id = $row->ID; // オブジェクトで返ってくる
}

%や_はprepareの後ろにそのまま入れると適当に展開してくれる。

updateとinsert

事例

こんな長いのでもOK

$query = "SELECT
    year(pm1.meta_value) as year,
    count(*) as count
    FROM
     $wpdb->posts
     INNER JOIN wp_otpostmeta pm1 ON (wp_otposts.ID = pm1.post_id)
     INNER JOIN wp_otpostmeta pm2 ON (wp_otposts.ID = pm2.post_id)
     INNER JOIN wp_otpostmeta pm3 ON (wp_otposts.ID = pm3.post_id)
    WHERE
     $wpdb->posts.post_type = 'member'
     AND $wpdb->posts.post_status = 'publish'
     AND pm1.meta_key = 'join_date'
     AND pm1.meta_value >= %s
     AND pm1.meta_value <= %s
     AND pm2.meta_key = %s
     AND pm2.meta_value NOT LIKE %s
     AND pm3.meta_key = %s
     AND pm3.meta_value NOT LIKE %s
    GROUP BY year(pm1.meta_value)
    ORDER BY pm1.meta_value ASC;";
    $years = $wpdb->get_results(
               $wpdb->prepare($query,
                   EPOCH_YEAR, get_year(),
                   get_year().'_status', '%quit%',
                   get_year().'_status', '%off%'), OBJECT);

混乱するけど。

カスタムフィールドに作った独自のIDで最大値取得

$query = "SELECT MAX(CAST(meta_value AS UNSIGNED)) FROM $wpdb->posts p
              LEFT OUTER JOIN $wpdb->postmeta pm on p.ID = pm.post_id
              WHERE meta_key = %s;";
$max_id = $wpdb->get_var($wpdb->prepare($query, 'my_id'));

WordPressで使う全てのテーブルでロック取得

トランザクション処理する場合にはテーブルロックする必要がある。
更新しようとしているテーブル(例えばwp_posts)とかだけをロックしようとしても失敗するので、関連テーブルを全てロックするのが無難。

$locking = array();
foreach ($wpdb->tables('blog') as $table) {
  $locking[]= $table . ' WRITE';
}
$query = 'LOCK TABLES '.implode(', ', $locking).';';
$wpdb->query($query);

ロックの時エイリアスを使うのであれば面倒っぽい

$locking = array();
foreach ($wpdb->tables('blog') as $table) {
  $locking[]= $table . ' WRITE';
}
// http://mysql.stu.edu.tw/doc/refman/5.1/en/lock-tables.html
$aliases = array(
   $wpdb->posts => 'p',
   $wpdb->postmeta => 'pm',
);
foreach ($aliases as $table => $alias) {
  $locking[]= $table . ' AS ' . $alias. ' WRITE';
}
$query = 'LOCK TABLES '.implode(', ', $locking).';';
$wpdb->query($query);

SELECT MAX(CAST(meta_value AS UNSIGNED)) FROM $wpdb->posts p
 LEFT OUTER JOIN $wpdb->postmeta pm on p.ID = pm.post_id

こういうクエリ用

まとめ

超小規模な案件ならまだしも、小規模なシステム開発だとデータモデリングからテーブル設計辺りで「こ、これをWordPressで実装するには無理があるんじゃ…」と気づくでしょう。無理ではないですが適してないです。
他のフレームワークも検討しましょう。
という自戒。

WordPress愛がある人は是非WordPressで！