あかつきのお宿

MySQLのJOINに関する話です。タイトルの通り、JOIN先に存在しないデータを取って来たい場合にどうしようかっていう話です。

言葉だけだとピンと来ないかもですが、JOIN先に存在しないデータっていうのは、結合先のテーブルにデータがないレコードと言うか、紐づく対象が存在しないデータと言うか……まあ、具体例を示した方が早いっすね。

前置き的な

例えば今、ブログ記事が入っているpostsテーブルと、その記事を書いているユーザーデータが入っているusersという二つのテーブルがあるとします。

両者のテーブルの中身は以下のような感じ。

//postsテーブル
+----+-----------+--------+------+---------------------+
| id |  user_id  | title  | text |      created        |
+----+-----------+--------+------+---------------------+
|  1 |     1     | title1 | text | 2012-10-01 12:00:00 | 
|  2 |     2     | title2 | text | 2012-10-02 12:00:00 | 
|  3 |     1     | title3 | text | 2012-10-03 12:00:00 | 
|  4 |     3     | title4 | text | 2012-10-04 12:00:00 | 
|  5 |     3     | title5 | text | 2012-10-05 12:00:00 | 
+----+-----------+--------+------+---------------------+

//usersテーブル
+----+--------+
| id |  name  |
+----+--------+
|  1 |  name1 | 
|  2 |  name2 | 
+----+--------+

postsテーブルに入っているuser_idというのが、usersテーブルに入っているユーザーのidに相当します。

このとき、ブログ記事のデータと一緒に執筆者のデータも取って来たい場合は、LEFT JOINを使ったSQLを発行することで実現できます。

SELECT * FROM posts LEFT JOIN users ON(posts.user_id = users.id) WHERE 1

これで、各記事とその記事を書いた人が紐づいた状態で取得できますね。

JOIN先に存在しないデータを取って来る

ところで今回の場合、記事のidが4と5の記事は、ユーザーidが3の人が書いた記事になっていますが、でもusersテーブルを見ると、idが3というデータは入っていませんよね。

ってことは、上記のSQLによってデータを取って来た場合、取得結果はこうなるわけです。

+----+-----------+--------+------+---------------------+------+--------+
| id |  user_id  | title  | text |      created        |  id  |  name  |
+----+-----------+--------+------+---------------------+------+--------+
|  1 |     1     | title1 | text | 2012-10-01 12:00:00 |   1  | name1  |
|  2 |     2     | title2 | text | 2012-10-02 12:00:00 |   2  | name2  |
|  3 |     1     | title3 | text | 2012-10-03 12:00:00 |   1  | name1  |
|  4 |     3     | title4 | text | 2012-10-04 12:00:00 | NULL |  NULL  |
|  5 |     3     | title5 | text | 2012-10-05 12:00:00 | NULL |  NULL  |
+----+-----------+--------+------+---------------------+------+--------+

JOIN先に存在しないデータってのは、この記事idが4や5のようなデータのことです。ユーザーデータがNULLになるようなデータのことを指しています。

このような、JOIN先のデータがNULLになるようなデータだけを取って来たい。

そんなときはこうです。

SELECT * FROM posts LEFT JOIN users ON(posts.user_id = users.id) WHERE users.id IS NULL

これでidが4と5のデータだけを抽出できます。

上記の結果にもある通り、データが存在しない場合はJOIN先のidがNULLになるので、そのデータだけ取るようなSQLを発行すれば良いわけですね。

基本的には問題ないと思いますが、このやり方は、正確には「JOIN先のデータが存在しないレコードを抽出する」ではなく、「JOIN先のアソシエーションキーの値がNULLのデータを抽出する」みたいな処理なので、usersテーブルのidがNULLを許可するような構成になってる場合は、もしかしたら望まない挙動をすることがあるかもしれない。

+----+-----------+--------+------+---------------------+
| id |  user_id  | title  | text |      created        |
+----+-----------+--------+------+---------------------+
|  1 |     1     | title1 | text | 2012-10-01 12:00:00 | 
|  2 |     2     | title2 | text | 2012-10-02 12:00:00 | 
|  3 |     1     | title3 | text | 2012-10-03 12:00:00 | 
|  4 |     3     | title4 | text | 2012-10-04 12:00:00 | 
|  5 |    NULL   | title5 | text | 2012-10-05 12:00:00 | 
+----+-----------+--------+------+---------------------+

+------+--------+
|  id  |  name  |
+------+--------+
|   1  |  name1 | 
|   2  |  name2 | 
| NULL |  name3 | 
+------+--------+

もしこんな風にデータが入っていたら、記事idが5のデータとname3のユーザーデータが紐づいてしまいます。

CakePHPを使う場合

CakePHPを使って同じことをするなら、こんなような書き方になりますかね。

//Post.php
class Post extends AppModel {
  public $belongsTo = array(
    'User' => array(
      'className' => 'User',
      'foreignKey' => 'user_id',
    ),
  );
}

//PostsController.php
class PostsController extends AppController {
  public function index() {
    $params = array(
      'conditions' => array('User.id IS NULL'),
      'contain' => array('User'),
    );
    $results = $this->Post->find('all', $params);
  }
}

簡略的に書いちゃいましたが、Postモデルでアソシエーションに関する設定を書いて、コントローラーでJOIN先のデータがないレコードだけを取得しています。結果は一緒です。






こんなデータの取り方をする場面があんのかって言われるとアレですが、僕の場合は、不要なデータを別の場所に移すためにこの処理を使ってみました。

ほら、例えば論理削除してるデータとかって、何かあったときのために取ってはあるけど、基本的にはゴミじゃん。買ってから一回しか使ってない健康器具とか、もらったものだから何となく捨て辛いって理由で取ってあるプレゼントの空箱みたいに、場所だけ取るやつじゃん。だから余計なレコード数を減らすためにも、定期的に物理削除した方が良いよなって思って。

hasManyなんかでくっついてるデータは全部一気に消しちゃえば良いんだけど、でも仕様がふくらみすぎちゃって、アソシエーションも東京の地下鉄みたいに複雑に絡み合っちゃってるもんだから、どれが消して良いデータなのかもよく分からないんですよね。なので一気に消すのは避けて、とりあえずbelongsToしてるJOIN先のデータがないものは不要なデータだろうって判断することにして、一つずつ順番に消すようにしました。

「そもそも論理削除って意味あんの？」とか言われちゃうとまたアレだけど、その辺は触れないでください。

まあ、僕の話はどーでもいいやね。

どんな場面で使うかは人それぞれですが、もしこういうデータの取り方できないのかな〜って悩んでる人がいれば、この情報が参考になれば幸いです。