過去に書いた記事のタイトル一覧を見ると、ニッチなものばかりですね。。
そうでもしないとtipsとして残せないのが悪いクセ。
今回のお題
表題の通りです。
CakePHP3でクエリビルダを使わずにSQL直書きでデータを取得するやり方は
すでにたくさんのブログなどで共有されていますが改めて。
CakePHP3で直接SQLを実行する
sss.controller.php
<?php
namespace App\Controller;
use Cake\Datasource\ConnectionManager;
class UserController extends AppController
{
public function detail($id) {
$sql = "SELECT * FROM users WHERE id = '${id}'";
$connection = ConnectionManager::get('default');
// 複数行を取得する場合はfetch → fetchall('assoc')にします
$user = $connection->execute($sql)->fetch('assoc');
}
}
ここまでは問題なくできました。
ハマったのはここから。
結合した複数テーブルからデータを取得しようとした際に起きました。
CakePHP3で複数テーブルを結合したSQLを直接実行する
取得したい内容は以下のSQLを実行したものです。
console
mysql>
SELECT *
FROM users
LEFT JOIN companies ON companies.id = users.company_id
LEFT JOIN payments ON users.id = payments.user_id
WHERE users.id = 1;
+----+-------------+-----------+------------+---------------------+---------------------+----+-------------+---------------------+---------------------+----+---------+---------+---------------------+---------------------+
| id | family_name | last_name | company_id | created | modified | id | name | created | modified | id | user_id | payment | created | modified |
+----+-------------+-----------+------------+---------------------+---------------------+----+-------------+---------------------+---------------------+----+---------+---------+---------------------+---------------------+
| 1 | ○田 | ○郎 | 2 | 2019-02-01 12:00:00 | 2019-03-01 10:00:00 | 2 | ほんにゃら商事 | 2019-02-01 13:00:00 | 2019-03-01 11:00:00 | 3 | 1 | 10800 | 2019-02-01 14:00:00 | 2019-02-01 14:00:00 |
これをCakePHP内で実行します。。。
user.controller.php
<?php
namespace App\Controller;
use Cake\Datasource\ConnectionManager;
class UserController extends AppController
{
public function detail($id) {
$sql = "SELECT * FROM users "
. "LEFT JOIN companies ON companies.id = users.company_id "
. "LEFT JOIN payments ON users.id = payments.user_id "
. "WHERE users.id = '${id}'";
$connection = ConnectionManager::get('default');
// 複数行を取得する場合はfetch → fetchall('assoc')にします
$user = $connection->execute($sql)->fetch('assoc');
}
}
実行結果をvar_dumpした結果がこちら。
var_dump
array(1) {
[0]=> array(8) {
["id"]=> string(1) "3"
["family_name"]=> string(6) "○田"
["last_name"]=> string(6) "○郎"
["created"]=> string(19) "2019-02-01 14:00:00"
["modified"]=> string(19) "2019-02-01 14:00:00"
["name"]=> string(21) "ほんにゃら商事"
["user_id"]=> string(1) "1"
["payment"]=> string(5) "10800"
}
}
あれ、テーブルで共通したカラムがpaymentテーブルの内容になっている。。
ちなみに、joinの順番を入れ替えるとcompaniesのidなどが取得されました。
どうやら一番最後のテーブルの内容が反映される模様。
じゃあ、usersテーブルのidを取るには。。？
SELECT companies,*, payments.*, users.* FROM users
このクエリにするとjoinではなく、selectの順番で一番最後のテーブルの内容が反映されました。
本来であれば、全件取得するのならクエリビルダを使えば良いのですが、
あくまでも例文ということで。。