CakePHPで独自SQLを使うには幾つかCakePHPの作法を覚える必要があります。CakePHPではmysqlのデータベース名は「users」と小文字の複数形、モデルのデータは「User」と頭が大文字の単数形になります。
独自SQL文ではAS句を使い「users AS User」という形にするとCakePHPのモデルの出力の形式になります。バーチャルフィールドを使うときは、特にCakePHPの独特な書き方があり、それを使わずMySQLを使う感覚でSQL文を書くとエラーになります。
モデルのデータを抽出
ユーザーデータテーブル（モデル）：User
都道府県データテーブル（モデル）：Prefecture
を作成します。
データの確認（User)
$query = 'SELECT * FROM users AS User WHERE 1=1 '; $params = array(); $data = $this->User->query($query, $params); pr($data);
出力結果
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)
$query = 'SELECT * FROM prefectures AS Prefecture WHERE 1=1 '; $params = array(); $data = $this->Prefecture->query($query, $params); pr($data);
出力結果
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歳を超えるに指定
$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);
出力結果
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」という新しいフィールド名を指定します。
$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);
出力結果
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をつけるとどうなるか？
$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';
これはエラーになってしまいます。
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句を使って作る場合は、以下のようにします。
$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つ連続します。
出力結果
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
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
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, '山形');
