参考にさせて頂いたページ
取得データの結合(UNION句) - データの取得 - MySQLの使い方
内部結合(INNER JOIN句) - データの取得 - MySQLの使い方
外部結合(LEFT JOIN句, RIGHT JOIN句) - データの取得 - MySQLの使い方
簡単な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メソッドの返り値として一つの配列が返ってくるらしい。