WCAデータベースで遊ぶ実践編「自己ベスト記録のリストを作成する」

昨年末の記事 「WCAデータベースたわむれ入門」 でWCAデータベースを用いる取っ掛かりを説明しました．
その記事はデータベースを扱うためのoverviewを重点的に書いたので具体的なWCAデータベースの操作方法までは触れていませんでした．
今回は実際に簡単なアプリケーションの開発をしながら，具体的なSQLの使い方を見ていきましょう．
ということで「自己ベスト記録のリスト」を作成します．

---

---

今回の目的

WCAの個人記録ページの最上部に出てくるような，種目ごとにSingleとAverageの記録を表示するリスト（表）を作成することを目的とします．
下の図のようなものが完成図です．

SQLクエリ

先日，この記事が話題に上がってました．非常にわかりやすくまとまっていて，有益な情報だと思います．

WCAデータベースを利用する場合，複雑なアプリケーションじゃない限り SELECT, INNER JOIN, OUTER JOIN ぐらいで十分かと思います．

SELECTは基本的に次のような構文です．

SELECT 取得したいカラム名
FROM テーブル名
WHERE 条件式

これで，テーブル名 から 条件式 にマッチした行のうち，取得したいカラム名 で指定したカラムを取得できます．
これだけ覚えておけば大丈夫です．他にも細かい記法はたくさんありますが，それはその都度その都度リファレンスを参照して（ググって）その場で解決していけば良いです．

INNER JOIN, OUTER JOIN は2つ以上のテーブルを結合するときに使用します．
INNER JOIN はIntersection（積集合）, OUTER JOIN はUnion（和集合）のようなイメージです．

さて，今回の目的を達成するためには，WCAデータベースのうち，“RanksSingle” と “RanksAverage” と “Events” テーブルを使用します． “RanksSingle” は下図のような構成になっています（phpMyAdminで表示したもの）．

personId を指定することで，その競技者の種目別Singleベスト記録を取得することができます．“RanksAverage” も同様です．

例えば，“RanksSingle” から WCA ID が “2010TERA01” のものを抽出して，そのうち personId, eventId, best を取り出したいときは，次のSQL文が設計されます．

SELECT personId, eventId, best AS single FROM RanksSingle WHERE personId = "2010TERA01"

まず，“RanksSingle” から得られるSingle記録と “RanksAverage” から得られるAverage記録を結合します．この場合は和集合なので（Singleのみしか記録がない種目があるため），OUTER JOIN を使用します．
上のSQL文を参考にすると，次のSQL文が設計されます．

SELECT * FROM
(SELECT personId, eventId, best AS single FROM RanksSingle WHERE personId = "2010TERA01") PersonSingle
LEFT OUTER JOIN
(SELECT personId, eventId, best AS average FROM RanksAverage WHERE personId = "2010TERA01") PersonAverage
ON PersonSingle.eventId = PersonAverage.eventId

実行すると，次のような結果を得られます．

これで競技者の WCA ID が与えられたとき，Single と Average の記録を得ることができました．
しかし，“RanksSingle” や “RanksAverage” の記録は整数値で入っているので，これを人間にわかる形式にフォーマットする必要があります．
競技のフォーマットの種類は下の図に示すように “Events” テーブルに記録されています．

よって，先ほど紹介した INNER JOIN を使用して，上の結果に競技のフォーマットを付加する必要があります．ここは積集合なので INNER JOIN を使用します．
次のSQL文が考えられます．

SELECT PersonSingle.eventId, single, average, format, name, cellName FROM
((SELECT personId, eventId, best AS single FROM RanksSingle WHERE personId = "2010TERA01") PersonSingle
LEFT OUTER JOIN
(SELECT personId, eventId, best AS average FROM RanksAverage WHERE personId = "2010TERA01") PersonAverage
ON PersonSingle.eventId = PersonAverage.eventId)
INNER JOIN Events
ON PersonSingle.eventId = Events.id

実行すると，次のような結果を得られます．

結果の整数値を人間が読み取れるフォーマットに変換する必要がありますが，値とフォーマットタイプのペアが揃ったのであとは簡単に処理することができます．
ひとまず，これでデータの取得は完了です！

PHPコーディング

MVC (Model / View / Controller) にゆるく従って，設計していきます．
Controller部は今回はあまり関係ありませんので省略します．
PHPコードはgistにアップしましたので参考にしてください．以下簡単な解説です．

kotarot/wca_my_record.php

Model

Model部は，DBコネクションの確立，SQL文を発行してPHPの連想配列にデータを格納する，というところまでを担当します．

PHPでMySQLを扱うとき，mysql系の関数は現在公式で非推奨になっていますので，mysqli系かPDOを使用しましょう．
今回はPDOを使用します．
参考: PHPでデータベースに接続するときのまとめ – Qiita

PDOが入っていない場合は，CentOSなら以下のコマンドで多分入ります．

# yum install php-pdo

そして，先ほど紹介したSQL文を投げ入れます．

なるべくならprepareでプリペアドステートメント使ってください．詳しくはググってください．
なおテーブル名ではプレースホルダは使用できないようです．
参考: Can PHP PDO Statements accept the table name as parameter? – Stack Overflow

View

View部は，Modelで作成したデータを参照してHTMLを生成します．
フォーマットに関してはPHPコードを見てください．
FMCのAverageはフォーマットがnumberですが，100で割らないといけないことに留意してください．

おわりに

WordPressでショートコード化してプロフィールページに貼り付けました．
こんな感じになります．

少しでも参考になれば嬉しいです！

---