参考にさせて頂いたページ
取得データの結合(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メソッドの返り値として一つの配列が返ってくるらしい。
