CakePHPで独自SQLを使うには幾つかCakePHPの作法を覚える必要があります。CakePHPではmysqlのデータベース名は「users」と小文字の複数形、モデルのデータは「User」と頭が大文字の単数形になります。
独自SQL文ではAS句を使い「users AS User」という形にするとCakePHPのモデルの出力の形式になります。バーチャルフィールドを使うときは、特にCakePHPの独特な書き方があり、それを使わずMySQLを使う感覚でSQL文を書くとエラーになります。
Contents
モデルのデータを抽出
ユーザーデータテーブル（モデル）：User
都道府県データテーブル（モデル）：Prefecture
を作成します。
データの確認（User)
|
0 1 2 3 4 5 6 |
$query = 'SELECT * FROM users AS User WHERE 1=1 '; $params = array(); $data = $this->User->query($query, $params); pr($data); |
出力結果
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 |
Array ( [0] => Array ( [User] => Array ( [id] => 1 [name] => 佐藤 [prefecture_id] => 1 [age] => 22 ) ) [1] => Array ( [User] => Array ( [id] => 2 [name] => 鈴木 [prefecture_id] => 2 [age] => 33 ) ) [2] => Array ( [User] => Array ( [id] => 3 [name] => 高橋 [prefecture_id] => 3 [age] => 29 ) ) [3] => Array ( [User] => Array ( [id] => 4 [name] => 田中 [prefecture_id] => 4 [age] => 45 ) ) [4] => Array ( [User] => Array ( [id] => 5 [name] => 伊藤 [prefecture_id] => 5 [age] => 37 ) ) [5] => Array ( [User] => Array ( [id] => 6 [name] => 渡辺 [prefecture_id] => 6 [age] => 47 ) ) ) |
データの確認（Prefecture)
|
0 1 2 3 4 5 6 |
$query = 'SELECT * FROM prefectures AS Prefecture WHERE 1=1 '; $params = array(); $data = $this->Prefecture->query($query, $params); pr($data); |
出力結果
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 |
Array ( [0] => Array ( [Prefecture] => Array ( [id] => 1 [name] => 北海道 ) ) [1] => Array ( [Prefecture] => Array ( [id] => 2 [name] => 青森 ) ) [2] => Array ( [Prefecture] => Array ( [id] => 3 [name] => 岩手 ) ) [3] => Array ( [Prefecture] => Array ( [id] => 4 [name] => 宮城 ) ) [4] => Array ( [Prefecture] => Array ( [id] => 5 [name] => 秋田 ) ) [5] => Array ( [Prefecture] => Array ( [id] => 6 [name] => 山形 ) ) ) |
AS句とWHEREの使い方
UserとPrefectureを LEFT JOINしてデータを取得。
Where句で年齢を30歳を超えるに指定
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
$query = 'SELECT * FROM users AS User LEFT JOIN prefectures as Prefecture ON User.prefecture_id = Prefecture.id WHERE 1=1 AND User.age >30'; $params = array(); $data = $this->User->query($query, $params); pr($data); |
出力結果
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 |
Array ( [0] => Array ( [User] => Array ( [id] => 2 [name] => 鈴木 [prefecture_id] => 2 [age] => 33 ) [Prefecture] => Array ( [id] => 2 [name] => 青森 ) ) [1] => Array ( [User] => Array ( [id] => 4 [name] => 田中 [prefecture_id] => 4 [age] => 45 ) [Prefecture] => Array ( [id] => 4 [name] => 宮城 ) ) [2] => Array ( [User] => Array ( [id] => 5 [name] => 伊藤 [prefecture_id] => 5 [age] => 37 ) [Prefecture] => Array ( [id] => 5 [name] => 秋田 ) ) [3] => Array ( [User] => Array ( [id] => 6 [name] => 渡辺 [prefecture_id] => 6 [age] => 47 ) [Prefecture] => Array ( [id] => 6 [name] => 山形 ) ) ) |
AS句で新しいフィールドを作って計算
AS句で年齢の半分を計算してみます。試しにAS句のあとに「HalfAge」という新しいフィールド名を指定します。
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
$query = 'SELECT User.id , User.name , User.prefecture_id , User.age , Prefecture.id , Prefecture.name , User.age/2 AS HalfAge FROM users AS User LEFT JOIN prefectures AS Prefecture ON User.prefecture_id = Prefecture.id WHERE 1=1 AND User.age >40'; $params = array(); $data = $this->User->query($query, $params); pr($data); |
出力結果
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
Array ( [0] => Array ( [User] => Array ( [id] => 4 [name] => 田中 [prefecture_id] => 4 [age] => 45 ) [Prefecture] => Array ( [id] => 4 [name] => 宮城 ) [0] => Array ( [HalfAge] => 22.5000 ) ) [1] => Array ( [User] => Array ( [id] => 6 [name] => 渡辺 [prefecture_id] => 6 [age] => 47 ) [Prefecture] => Array ( [id] => 6 [name] => 山形 ) [0] => Array ( [HalfAge] => 23.5000 ) ) ) |
[HalfAge] の項目は[User]の中に入るようにしたいのですが、[0]という新しいキーが作られてしまいます。
「 User.age/2 AS User.HalfAge」のようにHalfAgeの前にUserをつけるとどうなるか？
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
$query = 'SELECT User.id , User.name , User.prefecture_id , User.age , Prefecture.id , Prefecture.name , User.age/2 AS User.HalfAge FROM users AS User LEFT JOIN prefectures as Prefecture ON User.prefecture_id = Prefecture.id WHERE 1=1 AND User.age >40'; |
これはエラーになってしまいます。
|
0 1 2 |
Error: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.HalfAge FROM users AS User LEFT JOIN prefectures' at line 3 |
AS句で新規フィールドを作り、CakePHPの出力形式でデータを出力する
CakePHPで新しいフィールドをAS句を使って作る場合は、以下のようにします。
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
$this->User->virtualFields['HalfAge'] = 0; $query = 'SELECT User.id , User.name , User.prefecture_id , User.age , Prefecture.id , Prefecture.name , User.age/2 AS User__HalfAge FROM users AS User LEFT JOIN prefectures as Prefecture ON User.prefecture_id = Prefecture.id WHERE 1=1 AND User.age >40'; $params = array(); $data = $this->User->query($query, $params); pr($data); |
1行目:バーチャルフィールドを使う場合、以下のようにモデル名とバーチャルフィールド名を宣言します。
$this->User->virtualFields[‘HalfAge’] = 0;
書式は「$this->モデル名->virtualFields[‘フィールド名’] = 0;」となります。
4行目:バーチャルフィールドの記載
User.age/2 AS User__HalfAge
AS句のあとは「モデル名__バーチャルフィールド名」とします。モデル名とバーチャルフィールド名の間はアンダーバーが2つ連続します。
出力結果
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
Array ( [0] => Array ( [User] => Array ( [id] => 4 [name] => 田中 [prefecture_id] => 4 [age] => 45 [HalfAge] => 22.5000 ) [Prefecture] => Array ( [id] => 4 [name] => 宮城 ) ) [1] => Array ( [User] => Array ( [id] => 6 [name] => 渡辺 [prefecture_id] => 6 [age] => 47 [HalfAge] => 23.5000 ) [Prefecture] => Array ( [id] => 6 [name] => 山形 ) ) ) |
テスト用データ
User
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE TABLE IF NOT EXISTS `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, `prefecture_id` int(11) NOT NULL, `age` int(2) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ; INSERT INTO `users` (`id`, `name`, `prefecture_id`, `age`) VALUES (1, '佐藤', 1, 22), (2, '鈴木', 2, 33), (3, '高橋', 3, 29), (4, '田中', 4, 45), (5, '伊藤', 5, 37), (6, '渡辺', 6, 47); |
Prefecture
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE IF NOT EXISTS `prefectures` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ; INSERT INTO `prefectures` (`id`, `name`) VALUES (1, '北海道'), (2, '青森'), (3, '岩手'), (4, '宮城'), (5, '秋田'), (6, '山形'); |
