過去に書いた記事のタイトル一覧を見ると、ニッチなものばかりですね。。
そうでもしないと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の順番で一番最後のテーブルの内容が反映されました。

本来であれば、全件取得するのならクエリビルダを使えば良いのですが、
あくまでも例文ということで。。