Motomichi Works Blog

その日学習したことについて書いている日記です。誰かの役に立ったらそれはそれで嬉しいです。

さくらvpsとcakephp2.6.7で開発日記 その0021 HABTMで実行されているSQLのSELECT文とJOINと結合規則について読んで学習してみる

参考にさせて頂いたページ

取得データの結合(UNION句) - データの取得 - MySQLの使い方

内部結合(INNER JOIN句) - データの取得 - MySQLの使い方

外部結合(LEFT JOIN句, RIGHT JOIN句) - データの取得 - MySQLの使い方

SQL講座 INNER JOINを使った表の結合

逆引きSQL構文集 - IN句を用いた副問合せ

簡単なSELECT文

例として以下のような感じ。

SELECT col_name1, col_name2, ... FROM db_name.tbl_name;

カラム名をカンマ区切りで書いてデータを取得する。

FROM句で対象のデータベース名とテーブル名を指定している。

今回のテーブル例

articles(記事テーブル)

ブログ記事のテーブルです。

id text
1 記事本文1
2 記事本文2
3 記事本文3
4 記事本文4

articletags(記事タグテーブル)

記事に付けるタグのテーブルです。

id text
1 html
2 css
3 js

articles_articletags(中間テーブル)

HABTMで使用する中間テーブルです。

id article_id articletag_id
1 4 1
2 4 2
3 4 3

自分のサイトの記事一覧ページで実行されたSELECT文

二つのSQL文がCakePHPデバッグ機能で出力されたので読んでみます。

記事一覧を取得するSQL

一つ目の例として以下の通り。
読むにあたって、全部取得すると多すぎるので、idが5未満の4件のデータだけ取得しました。

SELECT 
`Article`.`id`, 
`Article`.`text` 

FROM `practice`.`articles` AS `Article` 
WHERE `id` < 5

個人的にわかりやすく書き直すと以下のような感じ。

SELECT 
カラム名1, 
カラム名2 

FROM データベース名.テーブル名 AS テーブルの別名 
WHERE 条件;

取得対象カラムは、practiceデータベースのarticlesテーブルで、取得したデータはAS句でテーブル名をArticleテーブルのデータとして扱う。

WHERE句で条件を付けて、id < 5 としています。

これによって、idが5未満の記事が取得できました。

AS句については

カラムに別名を付ける(AS句) - データの取得 - MySQLの使い方

のページを参考にさせていただきました。

記事一覧のデータにJOINする

二つ目の例として以下の通り。

SELECT 
`Articletag`.`id`, 
`Articletag`.`text` 

FROM `practice`.`articletags` AS `Articletag` 
JOIN `practice`.`articles_articletags` AS `articles_articletags` ON (`articles_articletags`.`article_id` IN (1, 2, 3, 4) AND `articles_articletags`.`articletag_id` = `Articletag`.`id`)

個人的にわかりやすく書き直すと以下のような感じ。

SELECT 
カラム名1, 
カラム名2 
 
FROM データベース名.記事タグテーブル AS 記事タグテーブル別名 
JOIN データベース名.中間テーブル AS 中間テーブル別名 ON ( 結合規則 )

SELECT文で取得するフィールドを最初に列挙する。

FROM句に続く記述で、articletagsテーブルのデータを取得して、これをAS句で名前を付けて、Articletagテーブルとして扱う。

JOIN句に続く記述で、中間テーブルのデータを取得して、ON句で結合規則を設定する。

規則1 : 中間テーブルのデータのうち、article_idが1,2,3,4のいずれかにあてはまるものを結合する。(今回の例ではarticle_id4しか無くて3行のデータが取得できた。) 規則2 : 中間テーブルのarticletag_idがarticletagテーブルのidと等しいものを結合する。(今回の例では、上記で取得できた3行がそれぞれid1,id2,id3)

CakePHPのModelのquery()メソッドで二つを実行してみた結果

一つめのSQLの実行結果は以下の通り。

Array
(
  [0] => Array
    (
      [Article] => Array
        (
          [id] => 1
          [text] => 記事本文1
        )
    )

  [1] => Array
    (
      [Article] => Array
        (
          [id] => 2
          [text] => 記事本文2
        )
    )

  [2] => Array
    (
      [Article] => Array
        (
          [id] => 3
          [text] => 記事本文3
        )
    )

  [3] => Array
    (
      [Article] => Array
        (
          [id] => 4
          [text] => 記事本文4
        )
    )
)

二つめのSQLの実行結果は以下の通り。
articletagsテーブルとarticles_articletagsテーブルの二つのテーブルデータが結合規則に則って結合されている。

  • 「中間テーブルのデータのうち、article_idが1,2,3,4のいずれかにあてはまるもの」は '[article_id] => 4' が3行だけ。
  • 「Articletag.id = articles_articletags.articletag_id」にあてはまるものは上記の3行全てがこの条件も満たす。
Array
(
  [0] => Array
    (
      [Articletag] => Array
        (
          [id] => 1
          [text] => html
        )
      [articles_articletags] => Array
        (
          [id] => 1
          [article_id] => 4
          [articletag_id] => 1
        )
    )

  [1] => Array
    (
      [Articletag] => Array
        (
          [id] => 2
          [text] => css
        )
      [articles_articletags] => Array
        (
          [id] => 2
          [article_id] => 4
          [articletag_id] => 2
        )
    )

  [2] => Array
    (
      [Articletag] => Array
        (
          [id] => 3
          [text] => js
        )
      [articles_articletags] => Array
        (
          [id] => 3
          [article_id] => 4
          [articletag_id] => 3
        )
    )
)

上記の結合結果をテーブルとして表現すると、下記のような3行になる事がわかる。

Articletag.id Articletag.text articles_articletags.id articles_articletags.article_id articles_articletags.articletag_id
1 html 1 4 1
2 css 2 4 2
3 js 3 4 3

HABTMで取得される配列について

例として以下のメソッドを実行する。

$this->Article->find('all', array(
  'conditions' => array(
    'id <' => 5
  )
));

そうするとreturnされるのは下記のような配列。

Array
(
  [0] => Array
    (
      [Article] => Array
        (
          [id] => 1
          [text] => 記事本文1
        )
      [Articletag] => Array
        (
        )
    )

  [1] => Array
    (
      [Article] => Array
        (
          [id] => 2
          [text] => 記事本文2
        )
      [Articletag] => Array
        (
        )
    )

  [2] => Array
    (
      [Article] => Array
        (
          [id] => 3
          [text] => 記事本文3
        )
      [Articletag] => Array
        (
        )
    )

  [3] => Array
    (
      [Article] => Array
        (
          [id] => 4
          [text] => 記事本文4
        )
      [Articletag] => Array
        (
          [0] => Array
            (
              [id] => 1
              [text] => html
              [articles_articletags] => Array
                (
                  [id] => 1
                  [article_id] => 4
                  [articletag_id] => 1
                )
            )

          [1] => Array
            (
              [id] => 2
              [text] => css
              [articles_articletags] => Array
                (
                  [id] => 2
                  [article_id] => 4
                  [articletag_id] => 2
                )
            )

          [2] => Array
            (
              [id] => 3
              [text] => js
              [articles_articletags] => Array
                (
                  [id] => 3
                  [article_id] => 4
                  [articletag_id] => 3
                )
            )
        )
    )
)

$hasAndBelomgsToManyを設定しているModelのfind()メソッドでは、はじめに検証してきた二つのSQLが内部で実行されているみたい。

その二つのSQLの実行から得られる二つの配列を、整形しながら結合してくれてfindメソッドの返り値として一つの配列が返ってくるらしい。