ORM many to many relationship 的 join 問題

問題：雖然 schema 定義是 many-to-many relationship，但是實際上只有 one-to-many (inverse) 的 relationship。我要怎麼樣寫好漂亮的 ORM 可以直接拿到 one-to-many (inverse) 的單欄結果，而非一個 many-to-many 的 result？

答案：我現在還不知道，直接 raw SQL 無雙

會探討的東西有

Laravel Eloquent ORM relationship
Database SQL
Laravel Query builder

先談談 Entrust 用的 user_role many-to-many relationship

Entrust 是一個基於身分 (role) 這個概念的套件。role 跟 user 是 many-to-many relationship，一個 user 可以有多種身分 (role)，一種身分 (role) 可能有不只一個 user 是。

Many-to-many relationship 在 DB 常常用三張表來表示，這裡以 user 跟 role 來舉例，foreign key 等設定就不多做贅述：

user
role
user_role

user 跟 role 都是原本的 table 不多新增任何欄位，而 user_role 這張表只有兩個 column：user_id 和 role_id²

   [User]

  id  |  Name
----------------
   1  |  Alice
   2  |  Bob
   3  |  Cindy
   4  |  David

   [Role]

  id  |  Name
------------------
   1  |  Sysadmin
   2  |  Board master
   3  |  Normal user

  [User_Role]

 user_id | role_id
--------------------
    1    |   3
    2    |   2
    2    |   3
    3    |   3
    3    |   1
    4    |   3

上面的例子表示 Alice 跟 David 都只是 Normal user；而 Bob 除了是 Normal user 以外也是 Board master；然後 Cindy 是 Sysadmin，也有另外兩種身分。

Query 的時候很簡單，只要用 User::with('roles') 就可以得到這樣的表

[
 ...
 {
    "id":2,
    "name":"Bob",
    "roles":[
        {
            "id":3,
            "name":"Normal user",
            "pivot":{"user_id":2,"role_id":3}
        },
        {
            "id":3,
            "name":"Board master",
            "pivot":{"user_id":2,"role_id":2}
        },
    ]
 },
 ...
]

雖然我現在只讓一個 user 只有一個 role，但我還是用了 entrust

為啥要這樣做也是因為難保未來不會想要改變心意換種 design，保持一種擴充性之類的吧。而且人家都寫好了剩下的有的沒的功能實在不用白不用，那就這樣了。

然後就出了問題

我的前端需要拿 user 資料，想要一個含有 user 所有資訊包含他是什麼 role 的一張 table（注意因為我這裡的 user 必定只有一個 role）。但是我用 User::with('roles') 會回一張有 role 各種資訊的 table¹，我不需要知道 role_id，role 的其他各種資訊，該怎麼辦？我想要的只是像是把上面那張表換成像這樣子的形式：

[
 ...
 {
    "id":1,
    "name":"Alice",
    "role_name":"Normal user"
 },
 ...
]

Raw SQL 先硬爆一下

上面那種表用 raw SQL 絕對弄得出來

SELECT users.*, TMP.name AS role_name FROM users 
    JOIN 
        (SELECT role_user.user_id, roles.name FROM role_user
            JOIN roles ON role_user.role_id = roles.id) AS TMP
    ON TMP.user_id = users.id;

嘛，這樣不就成了嗎？但是 raw SQL 這樣好嗎？

補個 raw sql 相關的 laravel 操作

use Illuminate\Support\Facades\DB;

// NO SELECT in first
$sql = <<<EOT
        users.*, TMP.name AS role_name FROM users 
        JOIN (SELECT role_user.user_id, roles.name FROM role_user
                JOIN roles ON role_user.role_id = roles.id) AS TMP
        ON TMP.user_id = users.id;";
EOT;

// get result
DB::select(DB::raw($sql))->get();
// view sql
DB::select(DB::raw($sql))->toSql();

但是我前端有時候突然加一些條件或排序的要求耶

這我知道，SQL 很行阿，所以就在後面加個 ORDER BY 或著是 WHERE 條件這樣不就好了嗎。

但都用到 ORM 了還在用 string 來搞 SQL 不覺得蠢嗎？雖然這種設計 table 的方式還要來搞這些有的沒的就感覺有點蠢了。

而且 WHERE 搜尋條件是可以讓使用者自己輸入的，難道不怕 SQL injection 嗎？

這種時候就要用 query builder 了，如果使用者有別的要求的話用 query builder 可以很簡單的加上各種 constriant。³

// subquery SQL
// SELECT role_user.user_id, roles.name FROM role_user
//     JOIN roles
//     ON role_user.role = roles.id;
$sub = DB::table('role_user')
    ->join('roles', 'role_user.role_id', '=', 'roles.id')
    ->select('role_user.user_id', 'roles.name');

// query SQL
// SELECT users.*, SUB.name AS role_name FROM    users
//     JOIN ($sub) AS SUB
//    ON SUB.user_id = users.id;
$sql = DB::table('users')
    ->join(DB::raw("({$sub->toSql()}) as sub"), 'sub.user_id', '=', 'users.id')
    ->select('users.*', 'sub.name');

// add other constraints if needed
$sql->orderby('role_name', 'asc');

有沒有更好的做法？這感覺還是有點廢廢的

那就傳這種有 pivot 的 json 上去嘛，叫前端出來處理就好啊？
- ~~我不是很會寫 JS~~
- 前端應該專心負責在 render 資料到 view 上，處理資料的業務邏輯應該要放在後端才對

其他小魯才疏學淺我也不知道了，總之這個問題就先這樣吧Orz

Reference: stackoverflow programming

¹. 用 User::with('roles')->select('name') 雖然可以拿到比較少的 role table 相關資訊，但是那個 pivot 還是會在，我想要更簡潔我從 DB 拿出來的表 ↩

². laravel 在 many to many relationship 裡面直接這樣取三張表的 id column 名稱在寫扣的時候會省事很多 ↩

³. 據說有 mergeBind 的東西，但是我沒有很多研究，至少現在這樣會動 ↩

ORM many to many relationship 與 join 問題